pgsql的日期时间函数总结
-- pgsql的日期函数
-- 减去参数
select age(timestamp '2017-01-26',timestamp '1951-08-15'); -- 65 years 5 mons 11 days 0 hours 0 mins 0.0 secs
-- 用于生产当前年龄。
select age(timestamp '1990-08-15'); -- 32 years 8 mons 23 days 0 hours 0 mins 0.0 secs
-- 获取当前时间：
select current_time; -- 06:38:29.458085 +00:00
-- 获取当前日期：
select current_date; --2023-05-07
-- 获取当前时间戳(两者的日期和时间)
select current_timestamp; --2023-05-07 06:39:56.870093 +00:00
-- 当前时间戳更精确：
select current_timestamp(2); -- 2023-05-07 06:41:01.91 +00:00
-- 本地时间戳：
select localtimestamp; -- 2023-05-07 06:41:42.845451
-- 获取子域（等效于extract);
select date_part('hour',timestamp '2001-02-16 20:38:40'); -- 20
-- 获取子域（等效于extract);
select date_part('month',interval '2 years 3 months'); -- 3
-- 截断成指定的精度；
select date_trunc('hour',timestamp '2001-02-16 20:38:40'); -- 2001-02-16 20:00:00.000000
select date_trunc('month',interval '2 years 3 months 40 minutes'); -- 2 years 3 mons 0 days 0 hours 0 mins 0.0 secs
-- 获取子域；
select extract(hour from timestamp '2001-02-16 20:38:40'); -- 20
select extract(month from interval '2 years 3 months'); -- 3
-- make_date(year int,month int,day int) 为年、月和日字段创建日期
select make_date(2013,5,4); -- 2013-05-04
-- make_time(hour int, min int, sec double precision) 从小时、分钟和秒字段中创建时间
select make_time(8, 15, 23.5); -- 08:15:23
-- make_timestamp(year int, month int, day int,
-- hour int, min int, sec double precision) 从年、月、日、小时、分钟和秒字段中创建时间戳
select make_timestamp(2013, 7, 15, 8, 15, 23.5); -- 2013-07-15 08:15:23.500000
-- now() 当前事务开始时的时间戳；
select now(); -- 2023-05-07 06:50:41.000582 +00:00
-- to_char(timestamp, text) 把时间戳转换成字串
select to_char(current_timestamp, 'HH12:MI:SS'); -- 06:52:25
-- to_char(interval, text) 把时间间隔转为字串
select to_char(interval '15h 2m 12s', 'HH24:MI:SS'); -- 15:02:12
-- to_char(int, text) 把整数转换成字串
select to_char(125, '999'); -- 125
-- to_char(double precision, text) 把实数/双精度数转换成字串
select to_char(125.8::real, '999D9'); -- 125.8
-- to_char(numeric, text) text 把numeric转换成字串
select to_char(-125.8, '999D99S'); -- 125.80-
-- to_date(text, text) date 把字串转换成日期
select to_date('05 Dec 2000', 'DD Mon YYYY'); --2000-12-05
-- to_timestamp(text, text) timestamp 把字串转换成时间戳
select to_timestamp('05 Dec 2000', 'DD Mon YYYY'); --2000-12-05 00:00:00.000000 +00:00
-- to_number(text, text) numeric 把字串转换成numeric
select to_number('12,454.8-', '99G999D9S'); -- -12454.8
-- extract ,date_part函数支持的field
-- 获取年份
select EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40'); --2001
-- 获取月份
select EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40'); -- 2
-- 获取日期域
select EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40'); --16
-- decade 年份域除以10
select EXTRACT(decade from TIMESTAMP '2001-02-16 20:38:40'); -- 200
-- 每周的星期号(0-6；星期天是0) (仅用于timestamp)
select EXTRACT(DOW FROM TIMESTAMP '2023-05-07 07:07:50'); -- 0
-- 一年的第几天(1 -365/366) (仅用于 timestamp)
select EXTRACT(DOY from TIMESTAMP '2023-05-07 07:07:50'); -- 127天
-- QUARTER 该天所在的该年的季度(1-4)(仅用于 timestamp)
select EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40'); -- 1
-- WEEK 该天在所在的年份里是第几周。
select EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40'); -- 7
-- age(timestamp, timestamp)：计算两个时间差
-- age(timestamp)：只输入一个参数，计算current_date与入参的时间间隔。
时间字段的截取
-- EXTRACT(field FROM source)函数：field 表示取的时间对象，source 表示取的日期来源，类型为 timestamp、time 或 interval。
SELECT EXTRACT
( century FROM now( ) ),
EXTRACT ( YEAR FROM now( ) ),
EXTRACT ( decade FROM now( ) ),
EXTRACT ( millennium FROM now( ) ),
EXTRACT ( quarter FROM now( ) ),
EXTRACT ( MONTH FROM now( ) ),
EXTRACT ( week FROM now( ) ),
EXTRACT ( dow FROM now( ) ),
EXTRACT ( DAY FROM now( ) ),
EXTRACT ( doy FROM now( ) ),
EXTRACT ( HOUR FROM now( ) ),
EXTRACT ( MIN FROM now( ) ),
EXTRACT ( sec FROM now( ) ),
EXTRACT ( epoch FROM now( ) );
-- 21,2023,202,3,2,5,18,0,7,127,7,12,4.009702,1683443524.009702
-- date_part(text, timestamp)：获取子域(等效于extract),其中text可以为year,month,day,hour,minute,second等
-- date_part(text, interval)：获取子域(等效于extract),其中text可以为year,month,day,hour,minute,second等
select date_part('hour', timestamp '2001-02-16 20:38:40'),
date_part('month', interval '2 years 3 months');
-- date_trunc(text, timestamp)：截断成指定的精度,指定精度后面的子域用0补充.其中text可以为year,month,day,hour,minute,second等
-- date_trunc(text, interval)：截取指定的精度,指定精度后面的子域用0补充.其中text可以为year,month,day,hour,minute,second等
select date_trunc('hour', timestamp '2001-02-16 20:38:40'),
date_trunc('hour', interval '2 days 3 hours 40 minutes');
-- make_timestamp(year int, month int, day int, hour int, min int, sec double precision)：从年、月、日、小时、分钟和秒字段中创建时间戳
select make_timestamp(2013, 7, 15, 8, 15, 23.5);
SELECT
now( ) + INTERVAL '2 years',--两年后
now( ) + INTERVAL '1 month',--一个月后
now( ) - INTERVAL '3 week',--三周前
now( ) + '10 min';--十分钟后
SELECT DATE
'2001-09-28' + INTEGER '7',
DATE'2001-09-28' + INTERVAL '1 hour',
DATE'2001-09-28' + TIME'03:00',
INTERVAL '1 day' + INTERVAL '1 hour',
TIMESTAMP '2001-09-28 01:00' + INTERVAL '23 hours',
TIME'01:00' + INTERVAL '3 hours',
- INTERVAL '23 hours',
DATE'2001-10-01' - DATE'2001-09-28',
DATE'2001-10-01' - INTEGER '7',
DATE'2001-09-28' - INTERVAL '1 hour',
TIME'05:00' - TIME'03:00',
TIME'05:00' - INTERVAL '2 hours',
TIMESTAMP '2001-09-28 23:00' - INTERVAL '23 hours',
INTERVAL '1 day' - INTERVAL '1 hour',
TIMESTAMP '2001-09-29 03:00' - TIMESTAMP '2001-09-27 12:00',
900 * INTERVAL '1 second',
21 * INTERVAL '1 day',
DOUBLE PRECISION '3.5' * INTERVAL '1 hour',
INTERVAL '1 hour' / DOUBLE PRECISION '1.5';
select now() + interval '2 years';
select now() + interval '2 year';
select now() + interval '2 y';
select now() + interval '2 Y';
select now() + interval '2Y';
-- Y years M months(in the date part) W Weeks D Days
-- H hours M minutes(in the time part) S Seconds
-- 3.1时间转字符串
-- to_char(timestamp,text)
select to_char(now(),'YYYY-MM-DD HH:MI::ss'); -- 2023-05-07 07:21::00
-- 3.2字符串转日期
-- to_date(text,text)
select to_date('05 Dec 2000','DD Mon YYY');
-- HH 一天的小时数(01-12)
-- HH12 一天的小时数(01-12)
-- HH24 一天的小时数(00-23)
-- MI 分钟(00-59)
-- SS 秒(00-59)
-- MS 毫秒(000-999)
-- US 微秒(000000-999999)
-- AM 正午标识(大写)
-- Y,YYY 带逗号的年(4和更多位)
-- YYYY 年(4和更多位)
-- YYY 年的后三位
-- YY 年的后两位
-- Y 年的最后一位
-- MONTH 全长大写月份名(空白填充为9字符)
-- Month 全长混合大小写月份名(空白填充为9字符)
-- month 全长小写月份名(空白填充为9字符)
-- MON 大写缩写月份名(3字符)
-- Mon 缩写混合大小写月份名(3字符)
-- mon 小写缩写月份名(3字符)
-- MM 月份号(01-12)
-- DAY 全长大写日期名(空白填充为9字符)
-- Day 全长混合大小写日期名(空白填充为9字符)
-- day 全长小写日期名(空白填充为9字符)
-- DY 缩写大写日期名(3字符)
-- Dy 缩写混合大小写日期名(3字符)
-- dy 缩写小写日期名(3字符)
-- DDD 一年里的日子(001-366)
-- DD 一个月里的日子(01-31)
-- D 一周里的日子(1-7；周日是1)
-- W 一个月里的周数(1-5)(第一周从该月第一天开始)
-- WW 一年里的周数(1-53)(第一周从该年的第一天开始)
-- 4.2时间截取
-- date_part(text, timestamp)
-- extract(field from timestamp)
select date_part('hour',timestamp '2001-01-02 20:38:44'),
extract(hour from timestamp '2001-01-02 20:38:44');
-- 20,20