PostgreSQL 时间操作

格式化日期

select to_char(current_date, 'yyyy-MM-dd');

输出

postgres=# select to_char(current_date, 'yyyy-MM-dd');
  to_char
------------
 2025-01-03
(1 row)

毫秒级时间戳格式化为日期

select to_char(to_timestamp(1732607360407 / 1000), 'yyyy-MM-dd');

输出

postgres=# select to_char(to_timestamp(1732607360407 / 1000), 'yyyy-MM-dd');
  to_char
------------
 2024-11-26
(1 row)

相隔天数

两个日期之间相距多少天。

select date '2025-01-03' - date '2024-07-11';
select '2025-01-03'::date - '2024-07-11'::date;

输出

postgres=# select '2025-01-03'::date - '2024-07-11'::date;
 ?column?
----------
      176
(1 row)

日期加减

获取176天之前的日期。

select now()::date - 176;
select current_date - 176;

输出：得到的是date类型（yyyy-MM-dd）

postgres=# select current_date - 176;
  ?column?
------------
 2024-07-11
(1 row)

内置魔法变量

• yesterday：昨天
• today：今天
• tomorrow：明天
• epoch：1970-01-01
• allballs：零点

select 'yesterday'::timestamptz, 'yesterday'::timestamp, 'yesterday'::date;
select 'today'::timestamptz, 'today'::timestamp, 'today'::date;
select 'tomorrow'::timestamptz, 'tomorrow'::timestamp, 'tomorrow'::date;
select 'epoch'::timestamptz, 'epoch'::timestamp, 'epoch'::date;
select 'allballs'::timetz, 'allballs'::time;

输出

postgres=# select 'yesterday'::timestamptz, 'yesterday'::timestamp, 'yesterday'::date;
      timestamptz       |      timestamp      |    date
------------------------+---------------------+------------
 2025-01-02 00:00:00+08 | 2025-01-02 00:00:00 | 2025-01-02
(1 row)

postgres=# select 'today'::timestamptz, 'today'::timestamp, 'today'::date;
      timestamptz       |      timestamp      |    date
------------------------+---------------------+------------
 2025-01-03 00:00:00+08 | 2025-01-03 00:00:00 | 2025-01-03
(1 row)

postgres=# select 'tomorrow'::timestamptz, 'tomorrow'::timestamp, 'tomorrow'::date;
      timestamptz       |      timestamp      |    date
------------------------+---------------------+------------
 2025-01-04 00:00:00+08 | 2025-01-04 00:00:00 | 2025-01-04
(1 row)

postgres=# select 'epoch'::timestamptz, 'epoch'::timestamp, 'epoch'::date;
      timestamptz       |      timestamp      |    date
------------------------+---------------------+------------
 1970-01-01 08:00:00+08 | 1970-01-01 00:00:00 | 1970-01-01
(1 row)

postgres=# select 'allballs'::timetz, 'allballs'::time;
   timetz    |   time
-------------+----------
 00:00:00+00 | 00:00:00
(1 row)

文档

https://www.postgresql.org/docs/current/datatype-datetime.html