postgres 时间查询与计算

计算时间差
date_part(‘day’, ‘2019-11-18 12:05’::timestamp - ‘2019-10-18 12:05’::timestamp)
截取时间段
select SUBSTRING(‘abcd’,1,2);
获取当前时间
CURRENT_DATE
SELECT CURRENT_TIME;
结果：14:39:53.662522-05

SELECT CURRENT_DATE;
结果：2001-12-23

SELECT CURRENT_TIMESTAMP;
结果：2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
结果：2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;

获取计算时间偏移
SELECT now()::timestamp + ‘1 year’; --当前时间加1年
SELECT now()::timestamp + ‘1 month’; --当前时间加一个月
SELECT now()::timestamp + ‘1 day’; --当前时间加一天
SELECT now()::timestamp + ‘1 hour’; --当前时间加一个小时
SELECT now()::timestamp + ‘1 min’; --当前时间加一分钟
SELECT now()::timestamp + ‘1 sec’; --加一秒钟
select now()::timestamp + ‘1 year 1 month 1 day 1 hour 1 min 1 sec’; --加1年1月1天1时1分1秒
SELECT now()::timestamp + (col || ’ day’)::interval FROM table --把col字段转换成天，然后相加

• date ‘2001-09-28’ + integer ‘7’ date ‘2001-10-05’
• date ‘2001-09-28’ + interval ‘1 hour’ timestamp ‘2001-09-28 01:00’
• date ‘2001-09-28’ + time ‘03:00’ timestamp ‘2001-09-28 03:00’
• interval ‘1 day’ + interval ‘1 hour’ interval ‘1 day 01:00’
• timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’ timestamp ‘2001-09-29 00:00’
• time ‘01:00’ + interval ‘3 hours’ time ‘04:00’

• • interval ‘23 hours’ interval ‘-23:00’
• date ‘2001-10-01’ - date ‘2001-09-28’ integer ‘3’
• date ‘2001-10-01’ - integer ‘7’ date ‘2001-09-24’
• date ‘2001-09-28’ - interval ‘1 hour’ timestamp ‘2001-09-27 23:00’
• time ‘05:00’ - time ‘03:00’ interval ‘02:00’
• time ‘05:00’ - interval ‘2 hours’ time ‘03:00’
• timestamp ‘2001-09-28 23:00’ - interval ‘23 hours’ timestamp ‘2001-09-28 00:00’
• interval ‘1 day’ - interval ‘1 hour’ interval ‘23:00’
• timestamp ‘2001-09-29 03:00’ - timestamp ‘2001-09-27 12:00’ interval ‘1 day 15:00’

• interval ‘1 hour’ * double precision ‘3.5’ interval ‘03:30’
  / interval ‘1 hour’ / double precision ‘1.5’ interval ‘00:40’
  2. 日期/时间函数：

函数 返回类型 描述 例子 结果
age(timestamp, timestamp) interval 减去参数，生成一个使用年、月的"符号化"的结果 age(‘2001-04-10’, timestamp ‘1957-06-13’) 43 years 9 mons 27 days
age(timestamp) interval 从current_date减去得到的数值 age(timestamp ‘1957-06-13’) 43 years 8 mons 3 days
current_date date 今天的日期
current_time time 现在的时间
current_timestamp timestamp 日期和时间
date_part(text, timestamp) double 获取子域(等效于extract) date_part(‘hour’, timestamp ‘2001-02-16 20:38:40’) 20
date_part(text, interval) double 获取子域(等效于extract) date_part(‘month’, interval ‘2 years 3 months’) 3
date_trunc(text, timestamp) timestamp 截断成指定的精度 date_trunc(‘hour’, timestamp ‘2001-02-16 20:38:40’) 2001-02-16 20:00:00+00
extract(field from timestamp) double 获取子域 extract(hour from timestamp ‘2001-02-16 20:38:40’) 20
extract(field from interval) double 获取子域 extract(month from interval ‘2 years 3 months’) 3
localtime time 今日的时间
localtimestamp timestamp 日期和时间
now() timestamp 当前的日期和时间(等效于 current_timestamp)
timeofday() text 当前日期和时间
3. EXTRACT，date_part函数支持的field：

域 描述 例子 结果
CENTURY 世纪 EXTRACT(CENTURY FROM TIMESTAMP ‘2000-12-16 12:21:13’); 20
DAY (月分)里的日期域(1-31) EXTRACT(DAY from TIMESTAMP ‘2001-02-16 20:38:40’); 16
DECADE 年份域除以10 EXTRACT(DECADE from TIMESTAMP ‘2001-02-16 20:38:40’); 200
DOW 每周的星期号(0-6；星期天是0) (仅用于timestamp) EXTRACT(DOW FROM TIMESTAMP ‘2001-02-16 20:38:40’); 5
DOY 一年的第几天(1 -365/366) (仅用于 timestamp) EXTRACT(DOY from TIMESTAMP ‘2001-02-16 20:38:40’); 47
HOUR 小时域(0-23) EXTRACT(HOUR from TIMESTAMP ‘2001-02-16 20:38:40’); 20
MICROSECONDS 秒域，包括小数部分，乘以 1,000,000。 EXTRACT(MICROSECONDS from TIME ‘17:12:28.5’); 28500000
MILLENNIUM 千年 EXTRACT(MILLENNIUM from TIMESTAMP ‘2001-02-16 20:38:40’); 3
MILLISECONDS 秒域，包括小数部分，乘以 1000。 EXTRACT(MILLISECONDS from TIME ‘17:12:28.5’); 28500
MINUTE 分钟域(0-59) EXTRACT(MINUTE from TIMESTAMP ‘2001-02-16 20:38:40’); 38
MONTH 对于timestamp数值，它是一年里的月份数(1-12)；对于interval数值，它是月的数目，然后对12取模(0-11) EXTRACT(MONTH from TIMESTAMP ‘2001-02-16 20:38:40’); 2
QUARTER 该天所在的该年的季度(1-4)(仅用于 timestamp) EXTRACT(QUARTER from TIMESTAMP ‘2001-02-16 20:38:40’); 1
SECOND 秒域，包括小数部分(0-59[1]) EXTRACT(SECOND from TIMESTAMP ‘2001-02-16 20:38:40’); 40
WEEK 该天在所在的年份里是第几周。 EXTRACT(WEEK from TIMESTAMP ‘2001-02-16 20:38:40’); 7
YEAR 年份域 EXTRACT(YEAR from TIMESTAMP ‘2001-02-16 20:38:40’); 2001
4. 当前日期/时间：
我们可以使用下面的函数获取当前的日期和/或时间∶
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (precision)
CURRENT_TIMESTAMP (precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (precision)
LOCALTIMESTAMP (precision)
时间转字符

to_char(time,‘YYYY-MM-DD hh24:mi:ss’) as time1,
to_char(time,‘YYYY-MM-DD’) as time2,
to_char(time,‘YYYY-MM-DD hh:mi:ss’) as time3