PostgreSQL Interval，Date，Timestamp和Time Data类型

PostgreSQL中文社区 2021-02-02

7062

译者简介

张岩&崔鹏&海能达DBA团队，任职于海能达通信股份有限公司哈尔滨平台中心，数据库开发高级工程师，致力于PostgreSQL数据库在专网通信领域、公共安全领域的应用与推广，个人兴趣主要集中在：分布式数据库系统设计、高并发高可用数据库架构设计与开源数据库的源码研究。

校对者简介

朱君鹏，博士研究生。主要研究方向为数据库管理系统，尤其是内存数据库、事务处理系统、软硬件协同设计、日志系统。

首先，我们有PostgreSQL Interval，Date和Timestamp数据类型的基本知识。以下是一些问题：

l 他们是什么类型的？他们有什么选择？

l 哪些postgresql.conf变量会影响日期和时间I O？

什么是可用日期和时间数据类型？

Date

使用公历的一年中的一天。

Time [(p)]

一天中的时间。

Timestamp[tz] [(p)]

日期和时间以及可选的时区。

Interval [fields] [(p)]

时间段（时间间隔），使用可选单位来限制类型。有效类型为

· YEAR

· MONTH

· DAY

· HOUR

· MINUTE

· SECOND

· YEAR TO MONTH

· DAY TO HOUR

· DAY TO MINUTE

· DAY TO SECOND

· HOUR TO MINUTE

· HOUR TO SECOND

· MINUTE TO SECOND

该列表包括例如（3）的[（p）]。这意味着该类型的值的精度为3毫秒。“p”可以为0-6，但类型必须包含秒。“tz”是PostgreSQL的“with time zone”的缩写。

日期和时间类型可用的选项

TIMEZONE=’US/PACIFIC’

接受的时区在视图pg_timezone_names中可见

DATESTYLE=’ISO, MDY’

此显示（客户端）日期形式。第一部分是样式的选择，第二部分是月，日和年的顺序。

INTERVALSTYLE=’POSTGRES’

有四种可能的间隔样式：sql_standard，postgres，postgres_verbose，ISO-8601。

上面的每个postgresql.conf变量也可以使用SET变量TO'value'在SQL中进行设置；在您的客户环境中，您还可以将PGDATESTYLE设置为可用的日期样式之一，

例如 PGDATESTYLE ='Postgres , mdy'。

从手册中：

Date Style values: Table 8.14. Date/Time Output Styles

https://www.postgresql.org/docs/current/datatype-datetime.html

还有其他三种日期/时间数据类型。这些范围由两个单独的值组成，其中整个值是从左值到右值。它可以包含端点，也可以不包含端点。包含性指定为带有方括号“ [”或“]”的端点，并且使用圆括号的端点不包括该端点。

TSRANGE	不带时区的时间戳范围
TSTZRANGE	带有时区的时间戳范围
DATERANGE	日期范围

=> create table dtranges ( ts tsrange, tstz tstzrange, dater daterange);
=> insert into dtranges (ts, tstz, dater) values (
            '[09-21-2019 10:45AM, 10-06-2019 23:59)', -- oktoberfest in germany
            '[07-16-1969 06:32 -7, 07-21-1969 17:54 -0 )', -- apollo 11
            '[01-01-1863, 06-19-1865)' -- emancipation proclamation declared
复制

    );
=> select * from dtranges;
-[ RECORD 1 ]----------------------------------------------
ts    | ["2019-09-21 10:45:00","2019-10-06 23:59:00")
tstz  | ["1969-07-16 06:32:00-07","1969-07-21 10:54:00-07")
dater | [1863-01-01,1865-06-19)
复制

日期，时间，时间间隔和时间戳（哦，天哪！）都存储在UTC时区中的数据库中。然后将它们显示在客户的样式规范中。例如，如果您的服务器在德国，但客户在纽约，那么您将看到的值取决于上面的客户样式规范。如果您使用的是时区类型，则将根据postgresql.conf中指定的时区或通过使用AT TIME ZONE的SQL来处理服务器中的时区。

输入数据相当容易。事情并不总是如我所愿，但通常都是这样。最近的一个例子是尝试为“上午6点”分配时间。该死，这是postgres不支持的功能之一。我不得不改用“6:00 am”来代替。当有疑问时，尤其是时间间隔，请选择强制转换。我在“手册”中喜欢的另一件事是，这些表详细描述了数据和时间类型的输入。我将在这里包括链接供您细读，而不是重复它们。

l Date and Time (and Timestamp) Input

https://www.postgresql.org/docs/11/datatype-datetime.html

l Interval Input

https://www.postgresql.org/docs/11/datatype-datetime.html

l Time Zone Input

https://www.postgresql.org/docs/11/datatype-datetime.html

还可以使用特殊的输入值常量。因为这些是常量，所以必须将它们用单引号引起来，并记住在读取SQL时会标识这些值。

这些输入值对日期和时间戳有效。’now’在时间上也有效。

INPUT	DESCRIPTION
EPOCH	1970-01-01 00:00:00+00 (Unix系统时间零)
INFINITY	晚于所有其他时间戳
-INFINITY	早于所有其他时间戳
NOW	当前事务开始时间
TODAY	今天午夜
TOMORROW	明天午夜
YESTERDAY	昨天午夜

还有一些看起来像变量的函数，可以提供当前的日期/时间值。如果要同时跟踪事务中的所有内容，请使用“now”，但是，如果要使用当前时间，则使用适当的函数之一。

稍后会更多地介绍函数，但请记住常量“now”是事务的开始时间，而current_ *函数是实时的。

QUERY	RETURNS
select ‘now’::time + ’27 seconds’;	12:40:27.915772
select ‘now’::time + ’27 hours’;	15:40:13.297067
select ‘now’::date + ’27 hours’;	error ERROR: operator is not unique: date + unknown LINE 1: select ‘now’::date + ’27 hours’; HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
select ‘now’::date + ’27 hours’::interval;	2019-07-01 03:00:00

有几种不同的方式进行类型转换，例如间隔“32 hours 3 min”；我更喜欢PostgreSQL风格的:: casting操作符。然而，这在按间隔进行算术时又提出了另一个棘手的问题。尽可能使用通用字段似乎很重要。在下面的第一个示例中，我们看到日期字段已更改。结果很尴尬但正确。第二，“hour”字段是通用的，因此我们得到了更清晰的答案。

尽早进入下一部分，让我们看一下justify函数，它们可以帮助解决尴尬。糟糕，justify_days()在“1 day”方面也遇到了同样的尴尬问题，但其他的都很好用。

QUERYRESULT
select interval ’32 hours 3 mins’ – ‘1 day’);	-1 days +32:03:00
select interval ’32 hours 3 mins’ – ’24 hours’;	08:03:00

select justify_interval(interval ’32 hours 3 mins’ – ’24 hours’);	08:03:00
select justify_interval(interval ’48 hours 3 mins’) – ‘1 day’::interval;	1 day 00:03:00
select justify_days(interval ’48 hours 3 mins’) – ‘1 day’::interval;	-1 days +48:03:00
select justify_hours(interval ’48 hours 3 mins’) – ‘1 day’::interval;	1 day 00:03:00
select justify_hours(interval ’84 hours 3 mins’ – ’24 hours’);	2 days 12:03:00

我会承认的。我掉进了一个兔子洞，为使用timestamp（tz），date，interval和time功能提供了一个很好的例子。该示例是费尔菲尔德（美国地名）人造运输跟踪系统。（FFSTT –大声说出来。）所有数据都是按顺序生成的，以反映适当的公交系统（bus system）。我们将在其中看到一些生成的数据。购买和维护日期是使用时间戳类型分配的，当您使用间隔进行算术时，对强制转换间隔的要求有点模糊。

为了生成公交车数据，我决定将有25辆公交车，每年5辆。我只是在询问维护日期，将维护时间设置为购买日期加上两个月。这些公交车将被标识为1001-1025。

Get first purchase date.

In a loop on each bus,

if this is the sixth bus for this purchase date,

then increment the purchase := purchase + '1 year'::interval,

set the maintenance := purchase + '2 months'::interval

insert into the buses table.

有一组完美的普通操作符和一组丰富的函数，可用于interval，timestamp(tz)，date和time。下面的运算符示例应提醒您，对date和time类型进行算术运算时最好使用相同的单位。

除了前面提到的current_ *函数之外，这里还有一些可用的其他函数的示例。

让我们看一下公共汽车公司，看看日期和时间函数在准现实生活中的表现。

管理层的第一个问题是公共汽车的年龄状况。具体来说，使用age（timestamp），我们拥有的公共汽车用了多久了？

这三个查询询问：

l 公共汽车用了多久了？

l 哪些公交车已经使用了五年以上？

l 公交车的平均使用年限是多少？

select count(bus), age(purchase) from buses group by age(purchase) order by age(purchase);
 count |          age
-------+------------------------
     5 | 1 year 4 mons 14 days
     5 | 2 years 4 mons 14 days
     5 | 3 years 4 mons 15 days
     5 | 4 years 4 mons 14 days
     5 | 5 years 4 mons 14 days
(5 rows)

select bus, age(purchase) from buses where purchase &lt current_timestamp - '5 years'::interval order by bus;
 bus  |          age
------+------------------------
 1001 | 5 years 4 mons 14 days
 1002 | 5 years 4 mons 14 days
 1003 | 5 years 4 mons 14 days
 1004 | 5 years 4 mons 14 days
 1005 | 5 years 4 mons 14 days
(5 rows)

select avg(age(purchase)) from buses;
               avg
---------------------------------
 3 years 4 mons 14 days 04:48:00
(1 row)
复制

函数date_part('text'，timestamp)根据您所请求的单位返回一个双精度数字。例如，如果您请求“hour”，那么您将获得以一个双精度数字表示的小时。该函数等效于extract(field from timestamp)，除了单位上的引号和from的要求以外。extract函数也可以采用extract(interval from interval)形式；

更多问题

然后，管理层想知道每条路线和站点的实际到达时间与计划到达时间之间的差异。但是他们只关心大约一分钟的时间。使用date_part()函数可以从实际值中减去计划后，以双精度算术执行此操作。（规则：所有公交车都晚点了。延迟时间是通过路线运行累积的。城市位于ABC 123网格布局中。）rtstops字段是路线停靠点的顺序标识符，与正式站点相反，独立的站点号，可以是任何路线的站点。

select s.route, s.rtstop, c.xstr1||'x'||c.xstr2||'('||c.corner||')' as stop_street,
       t.arrival as act_arrival, s.arrival as sched_arrival,
       date_part('seconds',(t.arrival - s.arrival)) as sched_diff
from tracktime t join schedules s using (route, run, rtstop, stopno, corner)
join stops c using (stopno, corner)
where date_part('seconds',(t.arrival - s.arrival)) &gt 58 and s.run = 1
order by route, t.arrival, s.rtstop;

 route  | rtstop | stop_street |        act_arrival         | sched_arrival | sched_diff
--------+--------+-------------+----------------------------+---------------+------------
 DIA-2  |     14 | 13xM(e)     | 2019-06-22 07:29:40.691579 | 07:28:41.73   |  58.961579
 DIA-2  |     17 | 7xG(n)      | 2019-06-22 07:50:49.473361 | 07:49:51.36   |  58.113361
 DIA-2  |     13 | 15xO(n)     | 2019-06-24 07:22:38.22922  | 07:21:38.52   |   59.70922
 MID-ew |     14 | 10xM(n)     | 2019-06-24 07:29:41.234253 | 07:28:41.73   |  59.504253
 MID-ew |     17 | 10xG(n)     | 2019-06-24 07:50:49.964865 | 07:49:51.36   |  58.604865
 MID-ns |     16 | 9xJ(e)      | 2019-06-22 07:43:46.284443 | 07:42:48.15   |  58.134443
 MID-ns |     13 | 15xJ(e)     | 2019-06-24 07:22:36.706033 | 07:21:38.52   |  58.186033
复制

也可以使用间隔而不是双精度来完成，但是需要一点强制转换。这显示了DIA-1路线的前五个停靠站。在查询中，将date_part（“ seconds”，（t.arrival – s.arrival））替换为（（t.arrival – t.rundate）:: time – s.arrival）:: interval

route  | rtstop | stop_street |   act_arrival   | sched_arrival |   sched_diff
--------+--------+-------------+-----------------+---------------+-----------------
 DIA-1  |      1 | 1xA(s)      | 05:57:01.488634 | 05:57:00      | 00:00:01.488634
 DIA-1  |      2 | 3xC(e)      | 06:04:04.795069 | 06:04:03.21   | 00:00:01.585069
 DIA-1  |      3 | 5xE(s)      | 06:11:10.535381 | 06:11:06.42   | 00:00:04.115381
 DIA-1  |      4 | 7xG(e)      | 06:18:22.281734 | 06:18:09.63   | 00:00:12.651734
 DIA-1  |      5 | 9xI(s)      | 06:25:25.691776 | 06:25:12.84   | 00:00:12.851776
复制

现在，我们重新问芝加哥提出的问题：“有人真的知道现在几点吗？”。年，月，日，小时，分钟，秒–这些都可以是PostgreSQL日期时间数据类型的值。逻辑操作可以在正确的时区提供正确的时间，但是请注意，有些部分比较棘手的。

请点击文章底部“阅读原文”查看原文内容

PostgreSQL中文社区欢迎广大技术人员投稿
投稿邮箱：press@postgres.cn

postgresql

文章转载自PostgreSQL中文社区，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL Interval，Date，Timestamp和Time Data类型

评论