【PostgreSQL】从零开始:(二十)数据类型-日期/时间类型

2023-12-21 06:42:07

日期/时间类型是一种数据类型,用于表示日期和时间的值。在PostgreSQL数据库中,日期/时间类型通常包括以下几种形式:

名字存储大小描述最低值最高值最小单位
timestamp [(p)][without time zone]8字节日期和时间(无时区公元前4713年294276 AD1微秒
timestamp [(p)]with time zone8字节日期和时间,带时区公元前4713年294276 AD1微秒
date4字节日期(无时间)公元前4713年5874897 AD1天
time [(p)][without time zone ]8字节一天中的时间(无日期00:00:0024:00:001微秒
time [(p)]with time zone12字节一天中的时间(无日期),带时区00:00:00+155924:00:00-15591微秒
interval [fields ][(p)]16字节时间间隔-178000000年178000000年1微秒

?time?和 timestamp?interval接受可选的精度值?p,该值指定在秒字段中保留的小数位数。默认情况下,精度没有显式限制。p?的允许范围为 0 到 6。

该类型有一个附加选项,即通过编写以下短语之一来限制存储字段集:interval

  1. YEAR
  2. MONTH
  3. DAY
  4. HOUR
  5. MINUTE
  6. SECOND
  7. YEAR TO MONTH
  8. DAY TO HOUR
  9. DAY TO MINUTE
  10. DAY TO SECOND
  11. HOUR TO MINUTE
  12. HOUR TO SECOND
  13. MINUTE TO SECOND

请注意,如果同时指定字段和?p,则这些字段必须包含 ,因为精度仅适用于秒。

该类型由 SQL 标准定义,但该定义表现出导致有用性可疑的属性。在大多数情况下,time with time zonetimestamp without time zonetimestamp with time zonedatetime?和的组合应提供任何应用程序所需的完整日期/时间功能。

日期/时间输入

日期

描述
1999/1/8国际标准化组织 8601;1 月 8 日,任何模式(推荐格式)
1999年1月8日在任何输入模式下都明确无误datestyle
1/8/19991 月 8 日模式;8 月 1 日模式MDYDMY
1/18/19991 月 18 日模式;在其他模式下被拒绝MDY
2001/2/32003 年 1 月 2 日模式;2003 年 2 月 1 日模式;2001 年 2 月 3 日模式MDYDMYYMD
1999- 1月-081 月 8 日以任何模式
1999年1月8日1 月 8 日以任何模式
1999 年 1 月 8 日1 月 8 日以任何模式
08 年 1 月 99-1 月 8 日模式,否则错误YMD
08 1月991 月 8 日,模式错误除外YMD
1月-08-991 月 8 日,模式错误除外YMD
19990108国际标准化组织 8601;1999 年 1 月 8 日,以任何模式
990108国际标准化组织 8601;1999 年 1 月 8 日,以任何模式
1999.008年和年中的某一天
J2451187儒略日期
公元前99年1月8日公元前99年

时间

时间类型为?time with time zone?和 time without time zone。 单独等价于 .time [ (p) ] without time zonetime [ (p) ]

这些类型的有效输入由一天中的时间后跟可选的时区组成。如果在 的输入中指定了时区,则会以静默方式忽略该时区。您也可以指定日期,但该日期将被忽略,除非您使用涉及夏令时规则的时区名称,例如 .在这种情况下,需要指定日期才能确定是time without time zone标准夏令时还是America/New_Yorktime with time zone夏令时。适当的时区偏移量记录在值中,并按存储方式输出;它不会根据活动时区进行调整。

时间输入

描述
05:06.8国际标准化组织 8601
4:05:06国际标准化组织 8601
4:05国际标准化组织 8601
40506国际标准化组织 8601
4:05 AM同于04:05;AM不影响价值
4:05 PM同16:05;输入小时必须为 <= 12
04:05:06.789-8ISO 8601,时区为 UTC 偏移量
04:05:06-08:00ISO 8601,时区为 UTC 偏移量
04:05-08:00ISO 8601,时区为 UTC 偏移量
040506-08ISO 8601,时区为 UTC 偏移量
040506+0730ISO 8601,以小数小时时区作为 UTC 偏移量
040506+07:30:00指定为秒的 UTC 偏移量(ISO 8601 中不允许)
04:05:06 PST缩写指定的时区
2003-04-12 04:05:06 America/New_York全名指定的时区

时区输入

描述
PST缩写(太平洋标准时间)
America/New_York全时区名称
PST8PDTPOSIX 样式的时区规范
-8:00:00PST 的 UTC 偏移量
-8:00PST 的 UTC 偏移量(ISO 8601 扩展格式)
-800PST 的 UTC 偏移量(ISO 8601 基本格式)
-8PST 的 UTC 偏移量(ISO 8601 基本格式)
zuluUTC的军事缩写
z的缩写形式(也在 ISO 8601 中)zulu

?时间戳

时间戳类型的有效输入由日期和时间的串联组成,后跟可选时区,后跟可选ADBC。(或者,/ 可以出现在时区之前,但这不是首选的顺序。因此:

1999-01-08 04:05:06

或者

1999-01-08 04:05:06 -8:00

是符合?ISO?8601 标准的有效值。此外,常见的格式:

January 8 04:05:06 1999 PST

SQL?标准通过存在“+”或“-”符号以及时间后的时区偏移量来区分和文字。

timestamp with time zone:

TIMESTAMP '2004-10-19 10:23:54'

timestamp without time zone:

TIMESTAMP '2004-10-19 10:23:54+02'

特殊值

为方便起见,PostgreSQL?支持几个特殊的日期/时间输入值。值infinity和-infinity在系统内部特别表示,并将保持不变地显示;但其他的只是符号速记,在读取时会转换为普通的日期/时间值。

?有效类型描述
epochdate,timestamp1970-01-01 00:00:00+00(Unix 系统时间零)
infinitydate,timestamp晚于所有其他时间戳
-infinitydate,timestamp早于所有其他时间戳
nowdate, ,timetimestamp当前交易的开始时间
todaydate,timestamp今天午夜 ()00:00
tomorrowdate,timestamp明天午夜 ()00:00
yesterdaydate,timestamp昨天午夜 ()00:00
allballstime世界协调时00:00:00.00

日期/时间输出

日期/时间类型的输出格式可以设置为四种样式之一:ISO 8601、SQL(安格尔)、传统?POSTGRES(Unix?日期格式)或德语。默认值为?ISO?格式。(SQL?标准要求使用 ISO 8601 格式。“SQL”输出格式的名称是历史偶然。下表显示了每种输出样式的示例。根据给定的示例,和datetime类型的输出通常只是日期或时间部分。但是,POSTGRES?样式以?ISO?格式输出仅日期值。

样式规范描述
ISOISO 8601,SQL 标准1997-12-17 07:37:16-08
SQL传统风格12/17/1997 07:37:16.00 PST
Postgres原创风格Wed Dec 17 07:37:16 1997 PST
German区域风格17.12.1997 07:37:16.00 PST

在?SQL?和 POSTGRES 样式中,如果指定了 DMY 字段顺序,则 day 显示在 month 之前,否则 month 显示在 day 之前。

datestyle设置输入排序示例输出
SQL, DMY//17/12/1997 15:37:16.00 CET
SQL, MDY//12/17/1997 07:37:16.00 PST
Postgres, DMY//Wed 17 Dec 07:37:16 1997 PST

在?ISO?样式中,时区始终显示为 UTC 的带符号数字偏移量,格林威治以东的区域使用正号。如果偏移量是整数小时数,则偏移量将显示为 hh(仅小时),如果是整数分钟数,则显示为?hhmm,否则显示为?hhmmss。(第三种情况在任何现代时区标准中都是不可能的,但在使用早于采用标准化时区的时间戳时,可能会出现这种情况。在其他日期样式中,如果当前区域中常用时区,则时区显示为字母缩写。否则,它以 ISO 8601 基本格式(hh?或?hhmm)显示为有符号数字偏移量。

用户可以使用命令、配置文件postgresql.conf中的?DateStyle?参数PGDATESTYLE或服务器或客户端上的环境变量来选择日期/时间样式。

格式化功能to_char也可作为格式化日期/时间输出的更灵活方式。

时区

时区和时区惯例受政治决策的影响,而不仅仅是地球几何。在 1900 年代,世界各地的时区在某种程度上变得标准化,但仍然容易发生任意变化,尤其是在夏令时规则方面。PostgreSQL?使用广泛使用的 IANA (Olson) 时区数据库来获取有关历史时区规则的信息。对于未来的时间,假设给定时区的最新已知规则将在未来很长一段时间内无限期地继续遵守。

PostgreSQL?努力与典型用法的?SQL?标准定义兼容。但是,SQL?标准具有日期和时间类型和功能的奇怪组合。两个明显的问题是:

  • 尽管该类型不能具有关联的时区,但datetime该类型可以。除非与日期和时间相关联,否则现实世界中的时区几乎没有意义,因为偏移量可能会在一年中因夏令时边界而变化。

  • 默认时区指定为与?UTC?的常量数字偏移量。因此,在跨?DST?边界进行日期/时间算术时,不可能适应夏令时。

为了解决这些困难,我们建议在使用时区时使用同时包含日期和时间的日期/时间类型。我们建议使用该类型(尽管?PostgreSQL?支持旧版应用程序并符合?SQL?标准)。PostgreSQL?为仅包含日期或时间的任何类型假定您的本地时区。time with time zone

所有时区感知日期和时间都以?UTC?内部存储。在向客户端显示之前,它们将转换为?TimeZone?配置参数指定的区域中的本地时间。

PostgreSQL?允许您以三种不同的形式指定时区:

  • 一个完整的时区名称,例如America/New_York 。???????pg_timezone_names视图中列出了已识别的时区名称。为此,PostgreSQL?使用广泛使用的 IANA 时区数据,因此其他软件也可以识别相同的时区名称。

  • 时区缩写,例如PST 。这样的规范仅定义了与UTC的特定偏移量,而全时区名称也可能意味着一组夏令时转换规则。视图pg_timezone_abbrevs中列出了已识别的缩写。不能将配置参数?TimeZone
    ?或?log_timezone
    ?设置为时区缩写??????????????AT TIME ZONE,但可以在日期/时间输入值和运算符中使用缩写。

  • 除了时区名称和缩写之外,PostgreSQL?还将接受 POSIX 样式的时区规范。此选项通常不比使用命名时区更可取,但如果没有合适的 IANA 时区条目可用,则可能需要此选项。

简而言之,这就是缩写和全名之间的区别:缩写表示与 UTC 的特定偏移量,而许多全名意味着本地夏令时规则,因此有两种可能的 UTC 偏移量。例如,表示纽约当地时间的中午,此特定日期为东部夏令时 2014-06-04 12:00 America/New_York(UTC-4)。所以指定同一时间瞬间。但指定东部标准时间 ???????2014-06-04 12:00 EDT2014-06-04 12:00 EST(UTC-5) 中午,无论夏令时在该日期名义上是否有效。

更复杂的是,一些司法管辖区使用相同的时区缩写来表示不同时间的不同 UTC 偏移量;例如,在莫斯科,某些年份的 UTC+3 意味着 UTC+4。PostgreSQL根据它们在指定日期的含义(或最近的含义)来解释这些缩写;但是,与上面的示例一样,这不一定与该日期的当地民用时间相同。MSKEST

在所有情况下,时区名称和缩写均不区分大小写。(这是对?PostgreSQL?8.2 之前版本的更改,后者在某些上下文中区分大小写,但在其他上下文中则不区分大小写。

时区名称和缩写都不会硬连接到服务器中;它们是从存储在安装目录.../share/timezone/,???????.../share/timezonesets/下的配置文件中获取的。

TimeZone
?配置参数可以在文件中设置,也可以任何其他标准方式进行设置。还有一些特殊的方法可以设置它:postgresql.conf

  • SQL?命令设置会话的时区。这是具有更符合 SQL 规范的语法的替代拼写。SET TIME ZONESET TIMEZONE TO

  • libpq?客户端使用环境变量在连接时向服务器发送命令。PGTZSET TIME ZONE

间隔输入

interval可以使用以下详细语法编写值:

[@] quantity unit [quantity unit...] [direction]

其中?quantity?是一个数字(可能有符号);单位为microsecond、millisecond、second、minute、hour、day、week、month、year、decade、century、millen、nium、@、ago或这些单位的缩写或复数形式;direction?可以是空的,也可以是空的。at 符号 () 是可选噪声。不同单位的金额通过适当的符号会计隐式添加。 否定所有字段。如果? ?IntervalStyle?设置为 ,则此语法也用于间隔输出。

天、小时、分钟和秒的数量可以指定,而不需要明确的单位标记。例如'1 12:59:10''1 day 12 hours 59 min 10 sec''200-10''200 years 10 months',的读法与。此外,还可以使用破折号指定年份和月份的组合;例如IntervalStyle sql_standard的读法与。(这些较短的表单实际上是SQL标准所允许的唯一表单,当设置为时用于输出。)

间隔值也可以写成 ISO 8601 时间间隔。带有指示符的格式如下所示:

quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

字符串必须以 开头,并且可以包含引入时间单位的 。下表给出了可用的单位缩写。可以省略单位,并且可以按任何顺序指定,但小于一天的单位必须出现在 之后。特别是PTTMT,的含义取决于它是在之前还是在之后。

缩写意义
Y
M月份(在日期部分)
W星期
D
H小时
M分钟(在时间部分)
S

替代格式:

P [ years-months-days ] [ T hours:minutes:seconds ]

字符串必须以PT开头,并且 A 分隔间隔的日期和时间部分。这些值以类似于 ISO 8601 日期的数字形式给出。

使用字段规范编写区间常量时,或将字符串分配给使用字段规范定义的区间列时,未标记数量的解释取决于?字段。例如,读作 1 年,而表示 1 秒。此外,字段规范允许的最低有效字段的“右侧”字段值将被静默丢弃。例如,写入会导致删除秒字段,但不会删除日期字段。INTERVAL '1' YEARINTERVAL '1'INTERVAL '1 day 2:03:04' HOUR TO MINUTE

根据?SQL?标准,区间值的所有字段必须具有相同的符号,因此前导负号适用于所有字段;例如'-1 2:03:04',间隔文本中的负号既适用于天,也适用于小时/分钟/秒部分。PostgreSQL?允许字段具有不同的符号,并且传统上将文本表示中的每个字段视为独立符号,因此在此示例中,小时/分钟/秒部分被视为正数。如果设置为IntervalStyle?sql_standard,则将前导符号视为应用于所有字段(但仅当未显示其他符号时)。否则,将使用传统的?PostgreSQL?解释。为避免歧义,如果任何字段为负数,建议为每个字段附加一个显式符号。字段值可以包含小数部分:例如'1.5 weeks''01:02:03.45''1.5 years',或'1 year 6 mons'但是,由于 interval 内部仅存储三个整数单位(月、天、微秒),因此必须将小数单位溢出到较小的单位。大于月份的单位的小数部分四舍五入为整数月数,例如??????????????'1.75 months' 成为。假设每月 30 天和每天 24 小时,则周和天的小数部分计算为整数天数和微秒数,例如???????1 mon 22 days 12:00:00,变为.只有秒数会在输出时显示为小数。

描述
1月2日SQL 标准格式:1 年 2 个月
3 4:05:06SQL标准格式:3天4小时5分6秒
1 year 2 months 3 days 4 hours 5 minutes 6 seconds传统 Postgres 格式:1 年 2 个月 3 天 4 小时 5 分 6 秒
P1Y2M3DT4H5M6SISO 8601“带指示符的格式”:同上含义
P0001-02-03T04:05:06ISO 8601“替代格式”:同上含义

在内部,值存储为月、日和微秒 。这样做是因为一个月中的天数各不相同,如果涉及夏令时调整,一天可以有 23 或 25 小时。months 和 days 字段是整数,而微秒字段可以存储小数秒。由于间隔通常是从常量字符串或减法创建的,因此此存储方法在大多数情况下效果很好,但可能会导致意外结果:

SELECT EXTRACT(hours from '80 minutes'::interval);
 date_part
-----------
         1

SELECT EXTRACT(days from '80 hours'::interval);
 date_part
-----------
         0

interval timestamp函数,可用于调整超出其正常范围的日期和小时。

间隔输出

可以使用命令 将间隔类型的输出格式设置为四种样式之一sql_standardpostgrespostgres_verbose、iso_8601 ?或 SET interval style postgres。默认值为格式。下表显示了每种输出样式的示例。

样式规范年月间隔天间隔混合间隔
sql_standard1月2日3 4:05:06-1-2 +3 -4:05:06
postgres1 年 2 个月3天 04:05:06-1 年 -2 周一 +3 天 -04:05:06
postgres_verbose@ 1 年 2 个月@ 3 天 4 小时 5 分 6 秒@ 1 年 2 个月 -3 天 4 小时 5 分 6 秒前
iso_8601货号:P1Y2MP3DT4H5M6SP-1Y-2M3DT-4H-5M-6S

文章来源:https://blog.csdn.net/sinat_36528886/article/details/135082328
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。