PostgreSQL日期操作总结
公式文件
日期/时间数据类型
日期/时间函数和运算符
演员阵容
以下两者完全相同。
SELECT '2023-09-15 12:34:56'::timestamp;
SELECT CAST('2023-09-15 12:34:56' AS timestamp);
时区显示
每个会话都设有时区。
postgres=# show timezone;
TimeZone
----------
UTC
时区改变
只更改当前会话
set timezone to 'Asia/Tokyo';
在postgresql.conf文件中进行全局配置。
timezone = 'Asia/Tokyo'
为每个数据库设置默认值
-- 変更
ALTER DATABASE db1 SET timezone TO 'Asia/Tokyo';
-- この後db1に接続し直すと反映される
日期的数据类型
时间戳带时区可以简写为”timestamptz”（这是PostgreSQL的扩展，标准SQL中没有）。
timestamp和timestamptz的区别是什么？
- timestamptzは現在のセッションのタイムゾーンに変換して表示される
postgres=# create table t (a timestamp, b timestamptz);
CREATE TABLE
時間: 6.300 ミリ秒
postgres=# insert into t (a, b) values ('2023-09-06 01:02:03', '2023-09-06 01:02:03+09:00');
INSERT 0 1
時間: 2.058 ミリ秒
postgres=# insert into t (a, b) values ('2023-09-06 01:02:03', '2023-09-06 01:02:03+03:00');
INSERT 0 1
時間: 1.199 ミリ秒
postgres=# select * from t;
a | b
---------------------+------------------------
2023-09-06 01:02:03 | 2023-09-06 01:02:03+09
2023-09-06 01:02:03 | 2023-09-06 07:02:03+09
(2 行)
从timestamptz到timestamp的转换
只是忽略了时区部分的日期和时间。
postgres=# select * from t;
a | b
---------------------+------------------------
2023-09-06 01:02:03 | 2023-09-06 01:02:03+09
2023-09-06 01:02:03 | 2023-09-06 07:02:03+09
(2 行)
postgres=# select a, b::timestamp from t;
a | b
---------------------+---------------------
2023-09-06 01:02:03 | 2023-09-06 01:02:03
2023-09-06 01:02:03 | 2023-09-06 07:02:03
(2 行)
将时间戳从timestamp转换为timestamptz。
在现有会话中，只需提供当前的时区。
postgres=# select a::timestamptz, b from t;
a | b
------------------------+------------------------
2023-09-06 01:02:03+09 | 2023-09-06 01:02:03+09
2023-09-06 01:02:03+09 | 2023-09-06 07:02:03+09
(2 行)
将日期进行字符串化
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS.US');
将”日付のパース” 这个日语短语用汉语标准表达的方式是 “日期解析”。
在中国母语中改述如下内容（只需一种选择）：
将::timestamptz转换很容易。
postgres=# SELECT '2021-02-03T04:05:06.789012+09:00'::timestamptz;
timestamptz
-------------------------------
2021-02-02 19:05:06.789012+00 -- DBのタイムゾーンはUTCなので、正常にパースできている
postgres=# SELECT '2021-02-03 04:05:06 +09:00'::timestamptz;
timestamptz
------------------------
2021-02-02 19:05:06+00 -- DBのタイムゾーンはUTCなので、正常にパースできている
(1 row)
postgres=# SELECT '2021-02-03 04:05:06 +09:00'::timestamp;
timestamp
---------------------
2021-02-03 04:05:06 -- +09:00が無視されてしまっている
postgres=# SELECT to_timestamp('2021-02-03T04:05:06+09:00', 'YYYY-MM-DD"T"HH24:MI:SS');
to_timestamp
------------------------
2021-02-03 04:05:06+00 -- +09:00が無視されてしまっている
日期的加减运算
SELECT now() + '3 days'::interval;
postgres=# select '2023-01-31'::date + '1 month'::interval;
?column?
---------------------
2023-02-28 00:00:00 -- 2月31日は存在しないので月末になる
(1 行)
日期的差异
只需简单地进行减法运算即可，结果将会是一个区间型。
select '2023-09-05 12:00:00'::timestamp - '2023-09-05 12:00:00+09:00'::timestamp with time zone;
将日期截断为秒（date_trunc(‘second’, timestamp)）
postgres=# select date_trunc('day', '2021/2/14 01:23:45.678912'::timestamp);
date_trunc
---------------------
2021-02-14 00:00:00
(1 row)
Time: 2.529 ms
postgres=# select date_trunc('second', '2021/2/14 01:23:45.678912'::timestamp);
date_trunc
---------------------
2021-02-14 01:23:45
(1 row)
Time: 1.151 ms
postgres=# select date_trunc('minute', '2021/2/14 01:23:45.678912'::timestamp);
date_trunc
---------------------
2021-02-14 01:23:00
(1 row)
将interval型转换为秒。
postgres=# select extract(epoch from '1 hour'::interval);
date_part
-----------
3600
将interval类型转换为天数
postgres=# SELECT floor(EXTRACT(epoch FROM (SELECT ('2023-09-15 12:00:00'::timestamp - '2023-09-13 19:00:00'::timestamp))) / 86400);
floor
-------
1 -- floorは小数点切り捨て
postgres=# SELECT ceil(EXTRACT(epoch FROM (SELECT ('2023-09-15 12:00:00'::timestamp - '2023-09-13 19:00:00'::timestamp))) / 86400);
ceil
-------
2 -- ceilは小数点切り上げ
将timestamp类型转换为UNIX纪元秒。
postgres=# select extract(epoch from '2023-09-05 12:00:00'::timestamp);
date_part
------------
1693915200
