1.常用计算函数
1.1 获取系统当前日期时间
select
current_date
,current_time
,current_timestamp
,now()
;
1.2日期时间格式转换
select
to_char(current_date,'yyyymmdd')
,to_date('2023-04-26', 'yyyy-mm-dd')
,date('2023-04-26')
,to_timestamp('2023-04-26', 'yyyy-mm-dd')
,to_timestamp('2023-04-26 12:30:45','yyyy-mm-dd hh24:mi:ss')
,to_timestamp('20230426123045','yyyymmddhh24miss')
,to_timestamp('2023042612'||'3045','yyyymmddhh24miss')
,'2023-04-26 16:30:45'::date
,'2023-04-26 16:30:45'::time
,'2023-04-26 16:30:45'::timestamp
;
select
to_char(to_date('2023-04-26','yyyy-mm-dd'), 'yyyy年mm月dd日')
,to_char(to_timestamp('2023-04-26 16:30:45','yyyy-mm-dd hh24:mi:ss'),'yyyy/mm/dd hh24时mi分ss秒 ')
,to_char(to_timestamp('2023-04-26 16:30:45','yyyy-mm-dd hh24:mi:ss'),'hh12miss am')
;
1.3加减运算
select
interval '2 years 3 months 10 days'
,'2023-04-26'::date - '2021-04-26'::date
,age('2023-04-26', '2021-12-25')
,age('2023-04-26'::date,'2021-12-25'::date)
,age(timestamp '2023-04-26', timestamp '1950-08-01')
,age(timestamp '1950-08-01')
,'2023-01-19 09:12:00'::timestamp + interval '1 day'
,'2023-01-20 09:12:00'::timestamp + interval '4 week'
,'2023-01-20 09:12:00'::date + interval '3 month'
,'2023-01-20 09:12:00'::date + interval '1 year'
,'2023-01-20 09:12:00'::date + interval '100 year'
;
1.4日期提取
select
date_part('year', date '2023-04-26')
,date_part('month', '2023-04-26'::date)
,date_part('week', '2023-04-26'::date)
,date_part('day', '2023-04-26 19:20:21'::timestamp)
,date_part('month', interval '2 years 3 months 10 days')
;