PostgreSQL 时间转换

> 本文由 [简悦 SimpRead](http://ksria.com/simpread/) 转码， 原文地址 [www.cnblogs.com](https://www.cnblogs.com/WinterSir/p/15154546.html)

背景：最近频繁使用到时间转换相关的操作，特此小记。

#### 1、实时取最近 24 小时内数据。[#](#1实时取最近24小时内数据)

select now() - interval '24h';  
通过 sql 获得符合要求的时间段，当做 where 条件即可。如：

```
select * from table1 where createTime between now() - interval '24h' and now()
```

'24h' 等同于 '24 hour'，更多参数见文档。

#### 2、时间差转换为时、分、秒。[#](#2时间差转换为时分秒)

关键函数：EXTRACT(field FROM source)、date_part('field', source)

```
SELECT date_part('epoch', TIMESTAMP '2021-08-17 21:00:00' - TIMESTAMP '2021-08-17 20:00:00') 秒;
--结果：3600
SELECT date_part('epoch', TIMESTAMP '2021-08-17 21:00:00' - TIMESTAMP '2021-08-17 20:00:00')/60 分钟;
--结果：60
SELECT date_part('epoch', TIMESTAMP '2021-08-17 21:00:00' - TIMESTAMP '2021-08-17 20:00:00')/60/60 小时;
--结果：1
SELECT date_part('epoch', TIMESTAMP '2022-08-17 20:00:00' - TIMESTAMP '2021-08-17 20:00:00')/60/60/24 天; 
--结果：365，如果不需要精确到小数有更简单的： 
SELECT date '2022-08-17' - date '2021-08-17' 天;
--结果：365
```

月和年的天数不确定，不能用以上方法，需要分别计算相加，如：

```
SELECT date_part('year', age(TIMESTAMP '2023-02-17 20:00:00' , TIMESTAMP '2021-08-17 20:00:00'))*12 + date_part('month', age(TIMESTAMP '2023-02-17 20:00:00' , TIMESTAMP '2021-08-17 20:00:00')) 月;
--结果：18
SELECT date_part('year', age(TIMESTAMP '2023-02-17 20:00:00' , TIMESTAMP '2021-08-17 20:00:00')) + date_part('month', age(TIMESTAMP '2023-02-17 20:00:00' , TIMESTAMP '2021-08-17 20:00:00'))/12 年;
--结果：1.5
```

以上示例均可使用 extract 函数，如：

```
SELECT extract(EPOCH FROM TIMESTAMP '2021-08-17 21:00:00' - TIMESTAMP '2021-08-17 20:00:00') 秒;
--结果：3600，此处不再一一举例
```

#### 3、注意[#](#3注意)

##### 3.1 field 域值

field 可以是 day、month、minute、year，也可以是：dow（一周中的日，从周日（0）到周六（6））、doy（一年的第几天（1–365/366）），等等、、、但是前者获取的只是域值。如：

```
SELECT date_part('hour', TIMESTAMP '2021-08-17 20:00:00');
--结果：20
SELECT date_part('hour', TIMESTAMP '2021-08-17 21:00:00' - TIMESTAMP '2021-08-17 20:00:00');
SELECT date_part('hour', TIMESTAMP '2021-08-18 21:00:00' - TIMESTAMP '2021-08-17 20:00:00');
--结果: 1
结果相同都是1，并不会做跨天的处理。
```

###### 3.2 epoch

对于 timestamp with time zone 值， 是自 1970-01-01 00:00:00 UTC 以来的秒数（结果可能是负数）； 对于 date and timestamp 值，是自本地时间 1970-01-01 00:00:00 以来的描述；对于 interval 值，它是时间间隔的总秒数 (简单理解就是秒数差值......)

#### 4、附上链接[#](#4附上链接)

[时间 / 日期函数和操作 (官网)](https://www.postgresql.org/docs/13/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)  
[时间 / 日期函数和操作 (中文)](http://www.postgres.cn/docs/13/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT)