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