13 布尔值与日期

使用布尔值、日期、时间戳、时区和时间间隔。

PostgreSQL 的 boolean 类型存储 true、false 或 NULL。日期和时间有一组常用类型：date 表示日历日期，time 表示一天中的时间，timestamp 表示不带时区的日期和时间，而 timestamptz（timestamp with time zone）表示一个按会话时区显示的时间点（不会保留原始时区/偏移量）。对于几乎所有应用场景，应该使用 timestamptz。

本章要点

boolean 类型及其多种输入形式（true、't'、'yes'、1）
四种核心日期/时间类型及各自适用的场景
为什么优先选择 timestamptz 而不是 timestamp
current_date、current_timestamp、now()、localtime
使用 interval 进行日期运算

示例

示例 13.1 布尔值与日期

-- Boolean: true, false, NULL
SELECT true AS t, false AS f, (1 = 1) AS same;

-- Boolean accepts many input forms
SELECT 'yes'::boolean AS yes, 'n'::boolean AS n, '1'::boolean AS one;

-- Dates and times
SELECT current_date AS today,
       current_timestamp AS now_tz,
       localtimestamp AS now_local,
       current_time AS time_now;

-- Casting date/time literals
SELECT '2024-01-15'::date AS d,
       '2024-01-15 10:30:00'::timestamp AS ts,
       '2024-01-15 10:30:00+00'::timestamptz AS ts_tz;

-- Arithmetic with interval
SELECT current_date + 7 AS in_a_week,
       current_timestamp - interval '1 hour' AS an_hour_ago,
       date '2024-12-31' - date '2024-01-01' AS days_between;

-- Extracting parts
SELECT extract(year FROM current_date) AS year,
       extract(dow  FROM current_date) AS day_of_week;

boolean 类型接受多种输入字符串：true、false、t、f、yes、no、y、n、1、0、on、off。在 psql 中，输出总是显示为 t 或 f。比较（=、<> 等）会产生布尔值；可以把比较结果存储在 boolean 列中。

timestamp without time zone（即 timestamp）存储挂钟时间值，不记录它在地球上的哪个位置。如果插入 2024-01-15 10:00:00，无论由谁读取，取回的都完全是这个值。timestamp with time zone（timestamptz）在内部以 UTC 存储同一个时间点，并按会话时区显示。处于不同时区的两个会话看到的 timestamptz 会是不同的挂钟时间，但底层的时间点相同。

对于几乎所有“这个事件是什么时候发生的？”的问题——下单、写入日志、创建用户——timestamptz 都是正确答案。普通 timestamp 适用于“食谱要求在当地时间上午 10 点烘焙，用户在哪个时区就按哪个当地时间执行”之类的场景，这种情况很少见。

运行示例

运行方式：

$ psql -f source/boolean-and-dates.sql postgres
 t | f | same
---+---+------
 t | f | t
(1 row)
 ...
   today
------------
 2024-xx-xx
(1 row)
 ...

常见陷阱

把事件时间存为 timestamp 而不是 timestamptz：一年后，你无法判断它是 UTC、服务器本地时间还是用户本地时间。一律使用 timestamptz。
将 date 或 timestamp without time zone 与 timestamptz 比较时，会使用会话时区来解释不带时区的值。重要时务必明确指定。
now() 返回的是事务开始时刻，而不是当前挂钟时间。在一个长事务中，重复调用会返回相同值。要获取真正的挂钟时间，请使用 clock_timestamp()。
interval '1 month' 加上日期会得到下个月的时间戳，有时会带来意外（January 31 + 1 month = February 28/29）。

提示

使用 SET TIME ZONE 'UTC'; 设置会话的时区。在生产环境中，让数据库以 UTC 运行，并在向用户显示时于应用层进行转换。

动手练习

运行 SELECT current_timestamp, now(), clock_timestamp();——current_timestamp 和 now() 相同；clock_timestamp() 即使在同一条语句内也可能已经不同。然后运行 BEGIN; SELECT now(); SELECT pg_sleep(1); SELECT now(); COMMIT; 并观察结果。

13 布尔值与日期

本章要点

示例

运行示例

常见陷阱

下载