88 lines
3.5 KiB
Markdown
88 lines
3.5 KiB
Markdown
PostgreSQL 提供了丰富且强大的时间日期数据类型。为了方便查阅，我将其整理成了下方的表格，涵盖了存储空间、范围、精度及常见用途。
|
||
|
||
### PostgreSQL 时间类型概览
|
||
|
||
| 类型名称 | 别名/常用写法 | 存储空间 | 描述 | 格式示例 | 建议使用场景 |
|
||
| --- | --- | --- | --- | --- | --- |
|
||
| **`timestamp [without time zone]`** | `timestamp` | 8 bytes | 日期和时间 (**不含**时区) | `2024-05-20 14:30:00` | 记录因果发生且与地理位置无关的时间 (如未来的具体的日历事件)。 |
|
||
| **`timestamp with time zone`** | **`timestamptz`** | 8 bytes | 日期和时间 (**含**时区) | `2024-05-20 14:30:00+08` | **最推荐**。记录跨国业务、日志、交易时间。存储时转为 UTC，查询时转为客户端时区。 |
|
||
| **`date`** | - | 4 bytes | 仅日期 (无时间) | `2024-05-20` | 生日、节假日、预定日期。 |
|
||
| **`time [without time zone]`** | `time` | 8 bytes | 仅时间 (无日期) | `14:30:00` | 闹钟设置、每日固定任务时间。 |
|
||
| **`time with time zone`** | `timetz` | 12 bytes | 仅时间 (含时区) | `14:30:00+08` | **不推荐使用** (SQL 标准仅为向后兼容保留)。 |
|
||
| **`interval`** | - | 16 bytes | 时间间隔 (时长) | `1 year 2 mons` <br>
|
||
|
||
<br> `3 days 04:05:06` | 计算两个时间点之间的差值，或做时间的加减运算。 |
|
||
|
||
---
|
||
|
||
### 核心区别详解：`timestamp` vs `timestamptz`
|
||
|
||
这是 PostgreSQL 中最容易混淆但也最重要的概念。
|
||
|
||
#### 1. `timestamp` (无时区)
|
||
|
||
* **特性**：你存什么，它就原封不动地记什么。数据库**不关心**时区。
|
||
* **问题**：如果你在东京存入 `09:00`，在伦敦读出来还是 `09:00`。对于跨国应用，这会导致时间错乱。
|
||
|
||
#### 2. `timestamptz` (含时区)
|
||
|
||
* **特性**：它是**时区感知**的。
|
||
* **存储机制**：当数据存入时，PostgreSQL 会根据当前的 `TimeZone` 配置，将时间转换为 **UTC** (世界标准时间) 进行存储。
|
||
* **读取机制**：当查询数据时，PostgreSQL 会将存储的 UTC 时间转换为当前客户端连接的 `TimeZone` 显示。
|
||
* **误区**：它**不会**存储你原本的时区名字（如 "Asia/Shanghai"），它只存储 UTC 值。
|
||
|
||
> **最佳实践**：在绝大多数应用场景下，建议默认使用 **`timestamptz`**，以避免时区转换带来的麻烦。
|
||
|
||
---
|
||
|
||
### 常用操作速查
|
||
|
||
以下是一些高频使用的 SQL 示例：
|
||
|
||
**1. 获取当前时间**
|
||
|
||
```sql
|
||
SELECT now(); -- 返回当前的 timestamptz (最常用)
|
||
SELECT current_date; -- 返回当前日期
|
||
SELECT current_timestamp; -- 等同于 now()
|
||
|
||
```
|
||
|
||
**2. 类型转换 (Casting)**
|
||
|
||
```sql
|
||
-- 字符串转时间
|
||
SELECT '2024-01-01'::date;
|
||
SELECT '2024-01-01 10:00:00'::timestamp;
|
||
|
||
-- Unix 时间戳 (秒) 转时间
|
||
SELECT to_timestamp(1716180000);
|
||
|
||
```
|
||
|
||
**3. 时间计算 (使用 Interval)**
|
||
|
||
```sql
|
||
-- 当前时间加 7 天
|
||
SELECT now() + interval '7 days';
|
||
|
||
-- 当前时间减 1 小时
|
||
SELECT now() - interval '1 hour';
|
||
|
||
-- 计算两个日期的间隔
|
||
SELECT age(timestamp '2024-12-31', timestamp '2024-01-01');
|
||
|
||
```
|
||
|
||
**4. 截断时间 (Date Trunc)**
|
||
非常适合做报表统计（按月、按天统计）。
|
||
|
||
```sql
|
||
-- 将时间截断到"月" (即使是5月20日，也会变成5月1日 00:00)
|
||
SELECT date_trunc('month', now());
|
||
|
||
```
|
||
|
||
|
||
|
||
如果您正在处理具体的业务场景（例如：“我想计算用户的留存时间” 或 “我需要处理不同时区的订单数据”），您可以告诉我，我可以为您编写具体的 **SQL 查询语句**示例。 |