时间与字符串转换
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
