Mistj's Digital Garden

Appearance

POSTGRESQL-常用的时间处理

获取系统时间 ​

sql
select now();  -- 2023-01-31 10:08:47.311760 +00:00  

-- 等同于  
select current_timestamp;
select now();  -- 2023-01-31 10:08:47.311760 +00:00  

-- 等同于  
select current_timestamp;

获取当前日期 ​

sql
select current_date; -- 2023-01-31
select current_date; -- 2023-01-31

时间计算-多久后 ​

sql
select now() + interval '2 years'; -- 2025-01-31 10:08:33.580103 +00:00  
select current_date + interval '2 years'; -- 2025-01-31 00:00:00.000000  
  
select now() + interval '2 Y';  
select now() + interval '2 y';  
select now() + interval '2Y';  
select now() + interval '2y';  
  
select now() + interval '1 month'; -- 2023-01-31 10:12:39.551128 +00:00  不可缩写  
select now() + interval '10 min';  
select now() + interval '3 week';  
select now() + '3 day'; -- 可省略interval
select now() + interval '2 years'; -- 2025-01-31 10:08:33.580103 +00:00  
select current_date + interval '2 years'; -- 2025-01-31 00:00:00.000000  
  
select now() + interval '2 Y';  
select now() + interval '2 y';  
select now() + interval '2Y';  
select now() + interval '2y';  
  
select now() + interval '1 month'; -- 2023-01-31 10:12:39.551128 +00:00  不可缩写  
select now() + interval '10 min';  
select now() + interval '3 week';  
select now() + '3 day'; -- 可省略interval

时间计算-时间差 ​

age

sql
select age(now(), timestamp '1999-02-14'); -- 23 years 11 mons 17 days 10 hours 14 mins 27.071154 secs  

select date_part('year', age(now(), timestamp '1999-02-14')) -- 23
select age(now(), timestamp '1999-02-14'); -- 23 years 11 mons 17 days 10 hours 14 mins 27.071154 secs  

select date_part('year', age(now(), timestamp '1999-02-14')) -- 23

age支持一个参数： 表示从current_date减去参数后的结果。

例如：

sql

select now(); -- 2023-02-01 06:44:54.730569 +00:00  
select age(timestamp '2023-01-31'); -- 0 years 0 mons 1 days 0 hours 0 mins 0.0 secs

select now(); -- 2023-02-01 06:44:54.730569 +00:00  
select age(timestamp '2023-01-31'); -- 0 years 0 mons 1 days 0 hours 0 mins 0.0 secs

提取年月日 ​

extract:

sql
select extract(month from timestamp '2013-04-13'); -- 4    
select extract(year from now()); -- 2023
select extract(month from timestamp '2013-04-13'); -- 4    
select extract(year from now()); -- 2023

date_part:

sql
select date_part('year', now()); -- 2023  
select date_part('year', timestamp '2013-04-13'); -- 2023
select date_part('year', now()); -- 2023  
select date_part('year', timestamp '2013-04-13'); -- 2023

参考
PostgreSQL: Documentation: 15: 9.9. Date/Time Functions and Operators
时间/日期函数和操作符