postgresql-时间函数

PostgreSQL-version 9.4.24

1. 系统当前时间

select now();   -- 天、时分秒 2022-09-29 15:50:13.273269+08
 
select current_date;    -- 天 2022-09-29
 
select current_time;    -- 时分秒 15:50:13.507701+08
 
select current_timestamp;  -- 天、时分秒 2022-09-29 15:50:13.522212+08
 
select localtimestamp;   -- 不带时区，2022-09-29 20:47:25.046437
 
select localtime;
 
select clock_timestamp();  -- 带时区
 

2. 格式转换

-- select to_timestamp(double precision)
select to_timestamp(1663740005);  -- unix时间戳转时间
 
select date(to_timestamp(1663743934638::numeric / 1000));  -- 时间保留到天
 
select date(localtimestamp);
 
-- select to_date(text, text)
select to_date('05 Dec 2022', 'DD Mon YYYY');   -- 字符串转日期
 
-- select to_char(timestamp, text)
select to_char(now(), 'YYYY-MM-DD HH:MI:SS');   -- 时间转字符串
 
 
select timestamp '2022-09-29 10:23:18';
 
select date '2022-09-29 10:23:18';
 
select time '2022-09-29 10:23:18';
 
select timestamp with time zone '2022-09-29 10:23:18';
 
select timestamp 'epoch' + 1663740005 * interval '1 second';

3. 时间计算

select now() + interval '2 day';
select now() + interval '2 days';
select now() + interval '2 hour';
select now() + interval '2 minute';
select now() + interval '2 second';
 
-- select age(timestamp, timestamp)
select age('2022-09-21', '2022-09-29'); -- -8days
 
select age(date '2022-09-21');  -- 当前时间与输入时间的间隔
 

interval部分也可以不写，其后的值有以下形式

Abbreviation	Meaning
Y	Years
M	Months (in the date part)
W	Weeks
D	Days
H	Hours
M	Minutes (in the time part)
S	Seconds

4. 时间截取

-- select date_part(text, timestamp)
select date_part('year' , now());
 
-- select extract(field from timestamp)
select extract(year from now());
 
-- select DATE_TRUNC(text, timestamp)
select date_trunc('hour', now());
select date_trunc('day', timestamp '2022-09-28 14:25:01');
 

参考：
http://postgres.cn/docs/9.4/functions-datetime.html