让我们创建一个包含单个时间戳列的新表 -
CREATE TABLE timestamp_test( ts timestamp );
现在让我们用一些数据填充它 -
INSERT INTO timestamp_test(ts) VALUES(current_timestamp), (current_timestamp+interval '5 days'), (current_timestamp-interval '18 hours'), (current_timestamp+interval '1 year'), (current_timestamp+interval '3 minutes'), (current_timestamp-interval '6 years');
如果您查询表（SELECT * from timestamp_test），您将看到以下输出 -
| ts |
|---|
| 2021-01-30 19:23:24.008087 |
| 2021-02-04 19:23:24.008087 |
| 2021-01-30 01:23:24.008087 |
| 2022-01-30 19:23:24.008087 |
| 2021-01-30 19:26:24.008087 |
| 2015-01-30 19:23:24.008087 |
现在，为了从时间戳列中提取小时、分钟等，我们使用 EXTRACT 函数。一些例子如下所示 -
SELECT EXTRACT(HOUR from ts) as hour from timestamp_test
输出-
| 小时 |
|---|
| 19 |
| 19 |
| 1 |
| 19 |
| 19 |
| 19 |
同样 -
SELECT EXTRACT(MONTH from ts) as month from timestamp_test
| 月 |
|---|
| 1 |
| 2 |
| 1 |
| 1 |
| 1 |
| 1 |
您还可以提取不那么明显的值，例如 ISO 周或世纪 -
SELECT EXTRACT(CENTURY from ts) as century, EXTRACT(WEEK from ts) as week from timestamp_test
| 世纪 | 星期 |
|---|---|
| 21 | 4 |
| 21 | 5 |
| 21 | 4 |
| 21 | 4 |
| 21 | 4 |
| 21 | 5 |
要获取可以从时间戳列中提取的完整值列表，请参阅https://www.postgresql.org/docs/9.1/functions-datetime.html