postgresql时间相关函数

• 时间与字符串转换
• to_char
• to_date, to_timestamp
• 时间的计算
• 日期
• 时间戳
• time
• interval
• 时间相关函数
• age
• current_xxx,localxxx
• isfinite
• justify_xxx
• extract,date_part
• date_trunc
• 跟事务相关的时间戳
• clock_timestamp, timeofday
• make_xxx
• overlaps
• 延迟执行sleep

时间与字符串转换

to_char

postgres=# select to_char(timestamp'now','yyyy-mm-dd hh24:mi:ss.ssssss'),to_char(timestamp'now','hh24:mi:ss');
 2015-02-28 23:59:15.8635515 | 23:59:15

按照给定的格式输出

to_date, to_timestamp

postgres=# select to_date('2013-11-11','yyyy-mm-dd'),to_timestamp('2013-11-11 11:12:13','yyyy-mm-dd hh24:mi:ss');
 2013-11-11 | 2013-11-11 11:12:13+08

格式支持参考:时间格式-官方

时间的计算

日期

日期加减整数,interval,time

postgres=# select date'2015-2-16' - 1,date'2015-2-16' - interval '1 second',date'2015-2-16' + time '12:00:00';
 2015-02-15 | 2015-02-15 23:59:59 | 2015-02-16 12:00:00

日期减日期

postgres=# select date'2015-2-28' - date'2015-2-26';
        2

时间戳

时间戳加减跟日期是一样的

时间戳加减 interval,time

postgres=# select timestamp'2015-2-28 12:12:50' - 1;
ERROR:  operator does not exist: timestamp without time zone - integer
LINE 1: select timestamp'2015-2-28 12:12:50' - 1;
                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
postgres=# select timestamp'2015-2-28 12:12:50' - interval '1 day',timestamp'2015-2-28 12:12:50' + time '00:00:10';
2015-02-27 12:12:50 | 2015-02-28 12:13:00

时间戳不支持- 整数

时间戳-时间戳

postgres=# select timestamp'2015-2-28 12:12:50' - timestamp'2015-2-26 1:16:30';
 P2DT10H56M20S

结果是interval

time

postgres=# select time '1:00:00' - time '00:00:30',time '1:00:00' + interval '1 second';
 PT59M30S | 01:00:01

time 不支持 time + time

interval

postgres=# select interval '1 hour' - interval '1 minute',interval '1 hour' + interval '1 minute';
 PT59M    | PT1H1M

postgres=# select interval '1 hour' * 3,interval '1 hour' / 3;
 PT3H     | PT20M

interval 支持乘除

时间相关函数

age

postgres=# select age(timestamp'2015-2-28 12:30:50',timestamp'2015-2-24 10:21:23');
 P4DT2H9M27S

postgres=# select age(date'now');
 PT0S

age函数相当于两个时间戳相减,第二个参数不填默认是当天0点

current_xxx,localxxx

postgres=# select current_date,current_timestamp,current_time,localtime,localtimestamp;
 2015-03-01 | 2015-03-01 00:49:31.359069+08 | 00:49:31.359069+08 | 00:49:31.359069 | 2015-03-01 00:49:31.359069

current_timestamp, current_time是带时区的

isfinite

postgres=# select isfinite(date'infinity'),isfinite(date'2013-1-1');
 f        | t

postgres=# select isfinite(timestamp'-infinity'),isfinite(timestamp'now'),isfinite(interval'1 hour');
 f        | t        | t

判断是否无穷大无穷小

justify_xxx

postgres=# select justify_days(interval '90 day'),justify_hours(interval '98 hour'),justify_interval(interval '31 day - 6 hour');
 P3M          | P4DT2H        | P1MT18H

调整计算,justify_days计算30天为一个月, justify_hours计算24小时一天, justify_interval为前两者结合

extract,date_part

postgres=# select extract(day from date'2015-2-28'),extract(minute from timestamp'2015-2-28 23:59:58');
        28 |        59

postgres=# select extract(month from interval '2 year 3 month 50 day');
         3

postgres=# select date_part('day',date'2015-2-28'),date_part('minute',timestamp'2015-2-28 23:59:58');
        28 |        59

postgres=# select date_part('month',interval '2 year 3 month 50 day');
         3

提取interval不自动计算,只是单纯提取,date_part 只是写法不一样

date_trunc

postgres=# select date_trunc('month',date'2015-2-28'),date_trunc('minute',timestamp'2015-2-28 23:59:58');
 2015-02-01 00:00:00+08 | 2015-02-28 23:59:00

postgres=# select date_trunc('month',interval '2 year 3 month 50 day');
 P2Y3M

截断到指定位置

跟事务相关的时间戳

postgres=# begin;
BEGIN
postgres=# select now(),transaction_timestamp(),current_timestamp,statement_timestamp();
 2015-03-01 01:18:37.661025+08 | 2015-03-01 01:18:37.661025+08 | 2015-03-01 01:18:37.661025+08 | 2015-03-01 01:18:40.400022+08

postgres=# select now(),transaction_timestamp(),current_timestamp,statement_timestamp();
 2015-03-01 01:18:37.661025+08 | 2015-03-01 01:18:37.661025+08 | 2015-03-01 01:18:37.661025+08 | 2015-03-01 01:19:02.023012+08

postgres=# end;
COMMIT

now(), transaction_timestamp(), current_timestamp 是事务开始的时间,在同一个事务中查询的结果是一样的. 
statement_timestamp() 是语句执行的时间,每次执行都不一样

clock_timestamp, timeofday

postgres=# select clock_timestamp(),clock_timestamp(),clock_timestamp();
 2015-03-01 01:27:17.056253+08 | 2015-03-01 01:27:17.056257+08 | 2015-03-01 01:27:17.056259+08

跟cpu时钟有关,所以即使在同一句语句中,也有差异 
timeofday跟clock_timestamp性质一样,但是返回的值类型是text

make_xxx

postgres=# select make_date(2013,12,11),make_time(12,12,23.33),make_timestamp(2015,2,28,17,27,30.666),make_timestamptz(2015,2,28,17,27,30.666);
 2013-12-11 | 12:12:23.33 | 2015-02-28 17:27:30.666 | 2015-02-28 17:27:30.666+08

postgres=# select make_interval(1,2,3,4,5,6,7),make_interval(hours:=5,secs:=7);
 P1Y2M25DT5H6M7S | PT5H7S

overlaps

postgres=# select (date'2010-1-1',date'2011-1-1') overlaps (date'2011-1-1',date'2012-1-1');
 f

postgres=# select (date'2010-1-1',timestamp'2011-1-1 00:00:01') overlaps (date'2011-1-1',date'2012-1-1');
 t

postgres=# select (date'2011-1-1',interval '1 month') overlaps (date'2011-2-1',interval '1 hour');
 f

判断时间区间是否重叠,时间区间是闭开区间

延迟执行sleep

pg_sleep

postgres=# select clock_timestamp(),pg_sleep(2),clock_timestamp();
 2015-03-01 14:13:34.100984+08 |          | 2015-03-01 14:13:36.103236+08

pg_sleep_for

postgres=# select clock_timestamp(),pg_sleep_for('2 second'),clock_timestamp();
 2015-03-01 14:14:27.4425+08 |              | 2015-03-01 14:14:29.445029+08

pg_sleep_until

postgres=# select clock_timestamp(),pg_sleep_until(timestamp 'now' + '2 second'),clock_timestamp();
 2015-03-01 14:15:58.029409+08 |                | 2015-03-01 14:16:00.029504+08

postgres=# select pg_sleep_until(timestamp 'tomorrow 12:00:00');
...... --时间太长取消了

注意不要在事务中持有锁时间太长,影响其他事务

//END