本文共 4207 字，大约阅读时间需要 14 分钟。
--取日期postgres=# select '2016-10-10 12:12:12' ::date; date ------------ 2016-10-10(1 row)--取时间postgres=# select '2016-10-10 12:12:12' :: time; time ---------- 12:12:12(1 row)Time: 6.083 ms--间隔1年postgres=# select '2016-10-10 12:12:12'::timestamp + interval '1 year'; ?column? --------------------- 2017-10-10 12:12:12(1 row)--间隔1月postgres=# select '2016-10-10 12:12:12'::timestamp + interval '1 month'; ?column? --------------------- 2016-11-10 12:12:12(1 row)--间隔1天postgres=# select '2016-10-10 12:12:12'::timestamp + interval '1 day'; ?column? --------------------- 2016-10-11 12:12:12(1 row)--间隔-1时postgres=# select '2016-10-10 12:12:12'::timestamp + interval '-1 hour'; ?column? --------------------- 2016-10-10 11:12:12--随机时间范围postgres=# select '2015-5-1'::date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval; ?column? --------------------- 2015-06-16 00:32:04(1 row)postgres=# select '2015-5-1'::date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval; ?column? --------------------- 2015-06-08 17:10:4--对于timestamp类型其范围是 4713 BC (公元前4713年) 到 294276 AD (公元后294276)postgres=# select '112045-10-1 12:12:12.1212+08'::timestamptz; timestamptz ------------------------------- 112045-10-01 12:12:12.1212+08 --不同时区之间的转换 postgres=# show timezone; TimeZone ---------- PRCpostgres=# 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';--休眠1.5秒后执行，单位秒SELECT clock_timestamp(),pg_sleep(1.5);--休眠5分钟，单位intervalSELECT clock_timestamp(),pg_sleep_for('5 minutes');--到指定时间执行，注意这些休眠时间不是完全精确的SELECT clock_timestamp(),pg_sleep_until('today 10:00');--事务开始时间与语句开始时间begin;--事务开始之后，now()值不变，clock_timestamp（）随系统而变postgres=# SELECT now(),transaction_timestamp(),clock_timestamp(); now | transaction_timestamp | clock_timestamp -------------------------------+-------------------------------+------------------------------- 2016-06-21 17:59:41.722658+08 | 2016-06-21 17:59:41.722658+08 | 2016-06-21 18:01:46.234223+08(1 row)postgres=# SELECT now(),transaction_timestamp(),clock_timestamp(); now | transaction_timestamp | clock_timestamp -------------------------------+-------------------------------+------------------------------- 2016-06-21 17:59:41.722658+08 | 2016-06-21 17:59:41.722658+08 | 2016-06-21 18:02:50.249675+08 --extract提取指定的日期值--dow,每周的星期号，星期天(0)到星期六(6)SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');--doy,一年的第几天(1-365/366)SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');postgres=# SELECT EXTRACT('year' FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 2001(1 row)postgres=# SELECT EXTRACT('hour' FROM TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 20--截取时间postgres=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); date_trunc --------------------- 2001-02-16 20:00:00(1 row)postgres=# SELECT date_trunc('month', TIMESTAMP '2001-02-16 20:38:40'); date_trunc --------------------- 2001-02-01 00:00:00(1 row)postgres=# SELECT date_trunc('day', TIMESTAMP '2001-02-16 20:38:40'); date_trunc --------------------- 2001-02-16 00:00:00 转载地址：http://svenx.baihongyu.com/