PostgreSQL的一些常用的时间相关的内置函数

PostgreSQL的一些常用的时间相关的内置函数：

-- 1.时间相关
-- 1.1 基本时间
-- 1.1.1 完整时间（日期+时间）
select CURRENT_TIMESTAMP; --2021-01-01 12:00:14.654607+08
select now(); -- 2021-01-01 12:09:09.433443+08

-- 1.1.2 日期
select current_date; -- 2021-01-01

-- 1.1.3 时间
select current_time; -- 12:00:14.631598+08

-- 1.1.4 本地时间日期

SELECT localtime; -- 本地时间。 12:00:00.653848
SELECT localtimestamp; --本地日期时间 2021-01-01 12:01:03.287146

-- 1.2 日期计算(使用 interval 关键字进行时间加减，interval 也可以不写(最好写上，有时候减法好像会有问题))
-- 1.2.1 年： years year y Y
SELECT now();
select now() + interval '2 years'; --2023-01-01 17:05:12.478947+08
select now() + interval '2years';
select now() + interval '2 y'; 
select now() + interval '2 Y'; 
select now() - interval '2 y';


-- 1.2.2 月 month M (注意小写m是分钟)
select now() + interval '1 month';
select now() + interval '1 m';


-- 1.2.3 日 day
select now() + interval '1 day';
select now() + interval '1 d';
select now() + interval '1 days';


-- 1.2.4 周 week w
select now() + interval '1 week';
select now() + interval '1 w';


-- 1.2.5 小时 hour
select now() + interval '1 hour';
select now() + interval '1 h';

-- 1.2.6 分钟 minute m
select now() + interval '1 minute';
select now() + interval '1 m';

-- 1.2.7 秒 second s
select now();
select now() + interval '1 second';
select now() + interval '1 s';


-- 1.2.8 省略interval
select now();
select now() +  '1 second';
select now() +  '1 s';

-- 1.3 计算两个时间差 age(timestamp, timestamp)
select now();
select age(now(), now() +  '1 h');
select age(now(), now() +  '1 d');
select age(now(), now() +  '1 MONTH');
select age(now(), now() +  '1 y');
select age(now(), now() - INTERVAL '1 y');
-- select age(now(), now() -  '1 y');  -- ERROR:  invalid input syntax for type timestamp with time zone: "1 y"
select age(now(), timestamp '1997-05-06');
select age(timestamp '1997-05-06');
select age(timestamp '1970-01-01');

-- 1.4 时间字段的截取 (https://blog.qdac.cc/?p=70 , https://www.runoob.com/postgresql/postgresql-datetime.html ，http://www.postgres.cn/docs/9.4/functions-datetime.html)
-- 1.4.1 EXTRACT(field FROM source)   提取:field表示取的时间对象（年，月，日，小时等值），source 表示取的日期来源，类型为 timestamp、time 或 interval。
select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());
select extract(hour from now());
select extract(hour from now());

-- 查看今天是一年中的第几天 day of year
select extract(doy from now());
select extract(doy from date '2020-12-31');

--每周的星期号，星期天(0)到星期六(6)
select extract(dow from now()); -- 5

-- 查看现在距1970-01-01 00:00:00 UTC 的秒数
select extract(epoch from now());

-- 把epoch 值转换回时间戳
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1369755555 * INTERVAL '1 second'; 


-- 1.4.2 date_part('field'，source) 获取子域(等效于extract)；
select date_part('hour',now());
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');

-- 1.4.3 date_trunc('field'，source) 截断成指定的精度，不要的都置为0
SELECT date_trunc('hour', timestamp '2001-02-16 20:38:40');
SELECT date_trunc('day', TIMESTAMP '2001-02-16 20:38:40');

-- 1.4.4 isfinite(date) 测试是否为有穷日期(不是 +/-无穷) ，返回true 或者false，不会用
-- infinite adj. 无限的，无穷的
SELECT isfinite(date '2001-02-16');
SELECT isfinite(date '0000-02-16');

-- 1.4.5 justify_days(interval) 按照每月 30 天调整时间间隔
-- justify 使齐整
SELECT justify_days(interval '35 days');  -- 1 mon 5 days
SELECT justify_hours(interval '25 h'); --1 day 01:00:00
 -- 使用justify_days和justify_hours调整时间间隔的同时进行正负号调整
SELECT justify_interval(interval '1 mon -1 hour'); --29 days 23:00:00 

-- 1.4.6 构造日期 make_date(year int, month int, day int)
select make_date(2013, 7, 15);
select make_interval(days := 10);
select make_time(8, 15, 23.5);

-- 1.4.7  timeofday() 与clock_timestamp相同，但结果是一个text 字符串；
select clock_timestamp();
SELECT timeofday();


-- 1.5 日期转文本 https://blog.csdn.net/snn1410/article/details/7741283 

-- 模式	描述
-- 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)(第一周从该年的第一天开始)


-- 注意这里不区分大小写
select to_char(current_date,'yyyy');
select to_char(current_date,'y'); -- 年的最后一位
select to_char(current_date,'yy');
select to_char(current_date,'yyy');
SELECT to_char(current_date - interval '1 day','yyyy-MM-dd');
SELECT now();
SELECT to_char(now(),'YYYY-MM-DD HH24:MI:SS'); -- MM 月份，MI 分钟，SS 分钟，MS 毫秒
SELECT to_char(now(),'yyyy-mm-dd hh24:mi:ss');
SELECT to_char(now(),'YYYY-MM-DD HH24:MM:SS.MS');
SELECT to_char(now(),'YYYY-MM-DD HH:MI:SS'); -- HH24 24 小时制，HH 12小时制

-- 注意这里区分大小写 !!
SELECT to_char(now(),'MONTH'); -- 全长大写月份名(空白填充为9字符) JANUARY  
SELECT to_char(now(),'Month'); -- January  
SELECT to_char(now(),'month'); -- 全长小写月份名(空白填充为9字符) january  

-- DDD	一年里的日子(001-366)
-- DD	一个月里的日子(01-31)
-- D	一周里的日子(1-7；周日是1)
-- W	一个月里的周数(1-5)(第一周从该月第一天开始)
-- WW	一年里的周数(1-53)(第一周从该年的第一天开始)
SELECT to_char(now(),'DDD'); -- 001

---

参考：

• https://www.cnblogs.com/mchina/archive/2013/04/15/3010418.html
• https://www.runoob.com/postgresql/postgresql-datetime.html