Date & Time & timestamptz

--取日期
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
----------
 PRC

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.conf
timezone='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分钟，单位interval
SELECT 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