postgresql 时区与时间函数

概述--把时间戳转成epoch值postgres=# select extract(epoch from now()); date_part ------------------ 1447898857.74524(1 row)--把epoch 值转换回时间戳postgres=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 144

--把时间戳转成epoch值postgres=# select extract(epoch from Now());    date_part     ------------------ 1447898857.74524(1 row)--把epoch 值转换回时间戳postgres=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' +  1447898857.74524 * INTERVAL '1 second';                      ?column?           ------------------------------ 2015-11-19 10:07:37.74524+08  postgres=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' +  1447898857.74524 * INTERVAL '1 second';                      ?column?           ------------------------------ 2015-11-19 10:07:37.74524+08 --查看当前的时间戳postgres=# select clock_timestamp(),current_timestamp,localtimestamp;        clock_timestamp        |              Now              |         timestamp          -------------------------------+-------------------------------+---------------------------- 2016-02-02 17:54:15.547194+08 | 2016-02-02 17:54:15.546956+08 | 2016-02-02 17:54:15.546956 --时间加减postgres=# select date '2016-02-02 10:00:00'+ interval '10 minutes';       ?column?       --------------------- 2016-02-02 00:10:00   --直接用sql生成随机日期时间select '2015-5-1'::date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval; --不同时区之间的转换postgres=# select '2016-02-03 09:07:30.816885+08' at time zone 'pst';          timezone          ---------------------------- 2016-02-02 17:07:30.816885(1 row)postgres=# select '2016-02-03 09:07:30.816885+08' at time zone 'cct';          timezone          ---------------------------- 2016-02-03 09:07:30.816885(1 row)  postgres=#  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'cct';      timezone       --------------------- 2001-02-17 09:38:40--查看系统支持的时区 select * from pg_timezone_names ;   --时区设置参数timezone = 'PRC'--修改时区的方法1. 全局参数postgresql.conftimezone='UTC'2. 数据库级配置alter database dbname set timezone='UTC';pipeline=# select * from pg_db_role_setting ; setdatabase | setrole |              setconfig               -------------+---------+--------------------------------------       14930 |       0 | {TimeZone=UTC}3. 用户级配置alter role rolname set timezone='UTC';或者alter role all set timezone='UTC';pipeline=# select * from pg_db_role_setting ; setdatabase | setrole |              setconfig               -------------+---------+--------------------------------------       14930 |       0 | {TimeZone=UTC}           0 |       0 | {TimeZone=UTC}   --创建随机日期时间函数          CREATE OR REPLACE FUNCTION rand_date_time(start_date date,end_date date) RETURNS TIMESTAMP AS  $BODY$   DECLARE      interval_days integer;      random_seconds integer;  random_dates integer;      random_date date;      random_time time;    BEGIN      interval_days := end_date - start_date;      random_dates:= trunc(random()*interval_days);    random_date := start_date + random_dates;     random_seconds:= trunc(random()*3600*24);     random_time:=' 00:00:00'::time+(random_seconds || ' second')::INTERVAL;    RETURN random_date +random_time;  END;   $BODY$  LANGUAGE plpgsql;  --生成指定时间内的随机时间SELECT rand_date_time('2000-01-01','2013-12-31');  --休眠1.5秒后执行，单位秒SELECT clock_timestamp(),pg_sleep(1.5),clock_timestamp();--休眠5分钟，单位intervalSELECT clock_timestamp(),pg_sleep_for('5 minutes'),clock_timestamp();--到指定时间执行，注意这些休眠时间不是完全精确的SELECT clock_timestamp(),pg_sleep_until('today 10:00'),clock_timestamp();

总结

以上是内存溢出为你收集整理的postgresql 时区与时间函数全部内容，希望文章能够帮你解决postgresql 时区与时间函数所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。