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