postgresql时间相关函数

时间与字符串转换

to_char

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

按照给定的格式输出

to_date,to_timestamp

select to_date('2013-11-11',66)">'yyyy-mm-dd'),to_timestamp('2013-11-11 11:12:13',66)">'yyyy-mm-dd hh24:mi:ss');
 2013-11-11 | 2013-11-11 11:12:13+08

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

时间的计算

日期

日期加减整数,interval,time

select date'2015-2-16' - 1,date'2015-2-16' - interval '1 second',66)">'2015-2-16' + time '12:00:00';
 2015-02-15 | 2015-02-15 23:59:59 | 2015-02-16 12:00:00

日期减日期

'2015-2-28' - date'2015-2-26';
        2

时间戳

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

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

时间戳不支持- 整数

时间戳-时间戳

'2015-2-28 12:12:50' - '2015-2-26 1:16:30';
 P2DT10H56M20S

结果是interval

time

'1:00:00' - '00:00:30',66)">'1:00:00' + interval '1 second';
 PT59M30S | 01:00:01

time 不支持 time + time

interval

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

postgres=# '1 hour' * 3,66)">'1 hour' / 3;
 PT3H     | PT20M

interval 支持乘除

时间相关函数

age

select age('2015-2-28 12:30:50',66)">'2015-2-24 10:21:23');
 P4DT2H9M27S

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

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

current_xxx,localxxx

current_date,100)">current_timestamp,100)">current_time,100)">localtime,100)">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

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

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

判断是否无穷大无穷小

justify_xxx

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

extract(day from date'2015-2-28'),100)">minute from '2015-2-28 23:59:58');
        28 |        59

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

postgres=# select date_part('day',date_part('minute',66)">'month',66)">'2 year 3 month 50 day');
         3

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

date_trunc

select date_trunc('2015-2-28 23:59:58');
 2015-02-01 00:00:00+08 | 2015-02-28 23:59:00

postgres=# '2 year 3 month 50 day');
 P2Y3M

截断到指定位置

跟事务相关的时间戳

begin;
BEGIN postgres=# now(),transaction_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=# end;
COMMIT 

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

clock_timestamp,timeofday

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

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

make_xxx

postgres=# select make_date(2013,12,11),make_time(12,23.33),make_timestamp(2015,2,28,17,27,30.666),make_timestamptz(2015,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,3,4,5,6,7),make_interval(hours:=5,secs:=7);
 P1Y2M25DT5H6M7S | PT5H7S

overlaps

select (date'2010-1-1',66)">'2011-1-1') overlaps (date'2011-1-1',66)">'2012-1-1');
 f

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

postgres=# '1 month') overlaps (date'2011-2-1',66)">'1 hour');
 f

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

延迟执行sleep

pg_sleep

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

pg_sleep_for

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

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

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

//END