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