Postgresql 中文操作指南
8.5. Date/Time Types #
PostgreSQL 支持 SQL 日期和时间类型的完整集，如 Table 8.9 中所示。在 Section 9.9 中描述了这些数据类型上适用的操作。日期根据公历进行计数，即使在该日历引入之前也是如此（有关更多信息，请参见 Section B.6）。
PostgreSQL supports the full set of SQL date and time types, shown in Table 8.9. The operations available on these data types are described in Section 9.9. Dates are counted according to the Gregorian calendar, even in years before that calendar was introduced (see Section B.6 for more information).
Table 8.9. Date/Time Types
Name |
Storage Size |
Description |
Low Value |
High Value |
Resolution |
timestamp [ (_p) ] [ without time zone ]_ |
8 bytes |
both date and time (no time zone) |
4713 BC |
294276 AD |
1 microsecond |
timestamp [ (_p) ] with time zone_ |
8 bytes |
both date and time, with time zone |
4713 BC |
294276 AD |
1 microsecond |
date |
4 bytes |
date (no time of day) |
4713 BC |
5874897 AD |
1 day |
time [ (_p) ] [ without time zone ]_ |
8 bytes |
time of day (no date) |
00:00:00 |
24:00:00 |
1 microsecond |
time [ (_p) ] with time zone_ |
12 bytes |
time of day (no date), with time zone |
00:00:00+1559 |
24:00:00-1559 |
1 microsecond |
interval [ _fields ] [ (p) ]_ |
16 bytes |
time interval |
-178000000 years |
178000000 years |
1 microsecond |
Note
SQL 标准要求只写 timestamp 等同于 timestamp without time zone，PostgreSQL 遵守该行为。timestamptz 被接受为 timestamp with time zone 的缩写；这是 PostgreSQL 的扩展。
The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.
time、timestamp_和 _interval 接受可选的精度值 p，该值指定秒字段中保留的小数位数。默认情况下，对精度没有明确限制。p 的允许范围为 0 至 6。
time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.
interval 类型有一个附加选项，即通过编写以下短语之一来限制存储字段的集：
The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases:
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请注意，如果同时指定了 fields 和 p，则 fields 必须包括 SECOND，因为精度仅适用于秒。
Note that if both fields and p are specified, the fields must include SECOND, since the precision applies only to the seconds.
类型 time with time zone 由 SQL 标准定义，但该定义展现出导致有用性存在疑问的属性。在大多数情况下，date、time、_timestamp without time zone_和 _timestamp with time zone_的组合应该可以提供任何应用程序所需的完整日期/时间功能范围。
The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, time, timestamp without time zone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.
8.5.1. Date/Time Input #
日期和时间输入被接受为几乎任何合理的格式，包括 ISO 8601、SQL 兼容、传统 POSTGRES 等。对于某些格式，日期输入中日期、月份和年份的顺序是含糊不清的，支持指定这些字段的预期顺序。将 DateStyle 参数设置为 _MDY_以选择月-日-年解释，_DMY_以选择日-月-年解释，或 _YMD_以选择年-月-日解释。
Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others. For some formats, ordering of day, month, and year in date input is ambiguous and there is support for specifying the expected ordering of these fields. Set the DateStyle parameter to MDY to select month-day-year interpretation, DMY to select day-month-year interpretation, or YMD to select year-month-day interpretation.
PostgreSQL 在处理日期/时间输入方面，比 SQL 标准要求的更灵活。有关日期/时间输入的确切解析规则，以及包括月份、星期和时区在内的已识别文本字段，请参阅 Appendix B。
PostgreSQL is more flexible in handling date/time input than the SQL standard requires. See Appendix B for the exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones.
请记住，任何日期或时间文本输入都需要用单引号引起来，就像文本字符串一样。有关更多信息，请参阅 Section 4.1.2.7。SQL 要求以下语法：
Remember that any date or time literal input needs to be enclosed in single quotes, like text strings. Refer to Section 4.1.2.7 for more information. SQL requires the following syntax
type [ (p) ] 'value'其中 p 是可选的精度规范，提供秒字段中的小数位数。可以为 time、timestamp 和 interval 类型指定精度，范围为 0 到 6。如果在常量规范中未指定精度，则默认为字面值精度（但不多于 6 位数字）。
where p is an optional precision specification giving the number of fractional digits in the seconds field. Precision can be specified for time, timestamp, and interval types, and can range from 0 to 6. If no precision is specified in a constant specification, it defaults to the precision of the literal value (but not more than 6 digits).
8.5.1.1. Dates #
Table 8.10显示了 _date_类型的可能输入。
Table 8.10 shows some possible inputs for the date type.
Table 8.10. Date Input
Example |
Description |
1999-01-08 |
ISO 8601; January 8 in any mode (recommended format) |
January 8, 1999 |
unambiguous in any datestyle input mode |
1/8/1999 |
January 8 in MDY mode; August 1 in DMY mode |
1/18/1999 |
January 18 in MDY mode; rejected in other modes |
01/02/03 |
January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode |
1999-Jan-08 |
January 8 in any mode |
Jan-08-1999 |
January 8 in any mode |
08-Jan-1999 |
January 8 in any mode |
99-Jan-08 |
January 8 in YMD mode, else error |
08-Jan-99 |
January 8, except error in YMD mode |
Jan-08-99 |
January 8, except error in YMD mode |
19990108 |
ISO 8601; January 8, 1999 in any mode |
990108 |
ISO 8601; January 8, 1999 in any mode |
1999.008 |
year and day of year |
J2451187 |
Julian date |
January 8, 99 BC |
year 99 BC |
8.5.1.2. Times #
时间类型的时区包括 time [ (_p）不含时区_ 和 time [ (_p）含时区_。time_本身等效于 _time without time zone。
The time-of-day types are time [ (_p) ] without time zone_ and time [ (_p) ] with time zone_. time alone is equivalent to time without time zone.
对这些类型的有效输入包括：一天的具体时间以及可选时区。（见 Table 8.11和 Table 8.12。）如果为 time without time zone_的输入指定了时区，则会忽略它。您还可以指定日期，但会忽略日期，除非您使用包含夏令时规则的时区名称，例如 _America/New_York。在这种情况下，指定日期是必需的，以便确定适用标准时间还是夏令时。相应的时区偏移量会记录在 _time with time zone_值中，并按储存内容输出；它不会调整为活动时区。
Valid input for these types consists of a time of day followed by an optional time zone. (See Table 8.11 and Table 8.12.) If a time zone is specified in the input for time without time zone, it is silently ignored. You can also specify a date but it will be ignored, except when you use a time zone name that involves a daylight-savings rule, such as America/New_York. In this case specifying the date is required in order to determine whether standard or daylight-savings time applies. The appropriate time zone offset is recorded in the time with time zone value and is output as stored; it is not adjusted to the active time zone.
Table 8.11. Time Input
Example |
Description |
04:05:06.789 |
ISO 8601 |
04:05:06 |
ISO 8601 |
04:05 |
ISO 8601 |
040506 |
ISO 8601 |
04:05 AM |
same as 04:05; AM does not affect value |
04:05 PM |
same as 16:05; input hour must be ⇐ 12 |
04:05:06.789-8 |
ISO 8601, with time zone as UTC offset |
04:05:06-08:00 |
ISO 8601, with time zone as UTC offset |
04:05-08:00 |
ISO 8601, with time zone as UTC offset |
040506-08 |
ISO 8601, with time zone as UTC offset |
040506+0730 |
ISO 8601, with fractional-hour time zone as UTC offset |
040506+07:30:00 |
UTC offset specified to seconds (not allowed in ISO 8601) |
04:05:06 PST |
time zone specified by abbreviation |
2003-04-12 04:05:06 America/New_York |
time zone specified by full name |
Table 8.12. Time Zone Input
Example |
Description |
PST |
Abbreviation (for Pacific Standard Time) |
America/New_York |
Full time zone name |
PST8PDT |
POSIX-style time zone specification |
-8:00:00 |
UTC offset for PST |
-8:00 |
UTC offset for PST (ISO 8601 extended format) |
-800 |
UTC offset for PST (ISO 8601 basic format) |
-8 |
UTC offset for PST (ISO 8601 basic format) |
zulu |
Military abbreviation for UTC |
z |
Short form of zulu (also in ISO 8601) |
有关如何指定时区的更多信息，请参阅 Section 8.5.3。
Refer to Section 8.5.3 for more information on how to specify time zones.
8.5.1.3. Time Stamps #
时间戳类型的有效输入由日期和时间的连接组成，紧随可选时区，再紧随可选 AD 或 BC。（或者，AD/BC 可以出现在时区之前，但这不是首选顺序。）因此：
Valid input for the time stamp types consists of the concatenation of a date and a time, followed by an optional time zone, followed by an optional AD or BC. (Alternatively, AD/BC can appear before the time zone, but this is not the preferred ordering.) Thus:
1999-01-08 04:05:06及：
and:
1999-01-08 04:05:06 -8:00是有效值，遵循 ISO 8601 标准。此外，还有常见格式：
are valid values, which follow the ISO 8601 standard. In addition, the common format:
January 8 04:05:06 1999 PST受支持。
is supported.
SQL 标准通过时间后面是否存在 “+” 或 “-” 符号以及时区偏移量来区分 timestamp without time zone 和 timestamp with time zone 文字。因此，根据该标准，
The SQL standard differentiates timestamp without time zone and timestamp with time zone literals by the presence of a “+” or “-” symbol and time zone offset after the time. Hence, according to the standard,
TIMESTAMP '2004-10-19 10:23:54'是 timestamp without time zone，而
is a timestamp without time zone, while
TIMESTAMP '2004-10-19 10:23:54+02'是 timestamp with time zone。PostgreSQL 在确定文字字符串的类型之前从不检查其内容，因此会将以上两项都视为 timestamp without time zone。为确保文字被视为 timestamp with time zone，请赋予其正确的显式类型：
is a timestamp with time zone. PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'在已确定为_timestamp without time zone_的文本中，PostgreSQL将静默地忽略任何时区指示。也就是说，生成值从输入值的日期/时间字段派生，不针对时区进行调整。
In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.
对于 timestamp with time zone，内部储存的值始终为 UTC（世界统一时间，传统上称为格林尼治标准时间，GMT）。具有明确指定时区的输入值将使用该时区的相应偏移量转换为 UTC。如果输入字符串中未指定时区，则假设它位于由系统的 TimeZone参数指示的时区中，并使用 _timezone_区的偏移量将其转换为 UTC。
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
当输出 _timestamp with time zone_值时，它总是从 UTC 转换为当前 _timezone_区，并在该区显示为当地时间。要查看其他时区中的时间，请更改 _timezone_或使用 _AT TIME ZONE_结构（见 Section 9.9.4）。
When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4).
timestamp without time zone 和 timestamp with time zone 之间的转换通常假设 timestamp without time zone 值应按 timezone 本地时间获取或给出。可使用 AT TIME ZONE 指定转换的不同时区。
Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.
8.5.1.4. Special Values #
PostgreSQL 支持几种特殊日期/时间的输入值以方便使用，如 Table 8.13所示。_infinity_和 _-infinity_在系统内有特殊表示，且将保持不变；但其他只是记号缩写，它将在读取时转换为普通日期/时间值。（特别是，_now_和相关字符串在读取时会转换为特定时间值。）所有这些值在作为 SQL 命令中的常量使用时，都需要用单引号引起来。
PostgreSQL supports several special date/time input values for convenience, as shown in Table 8.13. The values infinity and -infinity are specially represented inside the system and will be displayed unchanged; but the others are simply notational shorthands that will be converted to ordinary date/time values when read. (In particular, now and related strings are converted to a specific time value as soon as they are read.) All of these values need to be enclosed in single quotes when used as constants in SQL commands.
Table 8.13. Special Date/Time Inputs
Input String |
Valid Types |
Description |
epoch |
date, timestamp |
1970-01-01 00:00:00+00 (Unix system time zero) |
infinity |
date, timestamp |
later than all other time stamps |
-infinity |
date, timestamp |
earlier than all other time stamps |
now |
date, time, timestamp |
current transaction’s start time |
today |
date, timestamp |
midnight (00:00) today |
tomorrow |
date, timestamp |
midnight (00:00) tomorrow |
yesterday |
date, timestamp |
midnight (00:00) yesterday |
allballs |
time |
00:00:00.00 UTC |
以下 SQL 兼容函数也可用于获取对应数据类型的当前时间值：CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME、LOCALTIMESTAMP。（见 Section 9.9.5。）请注意，这些是 SQL 函数，并且是 _not_在数据输入字符串中识别的。
The following SQL-compatible functions can also be used to obtain the current time value for the corresponding data type: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. (See Section 9.9.5.) Note that these are SQL functions and are not recognized in data input strings.
Caution
虽然输入字符串 now、today、tomorrow 和 yesterday 在交互式 SQL 命令中使用很合适，但当命令被保存以稍后再执行时，它们可能会有出人意料的行为，比如在已准备好的语句、视图和函数定义中。该字符串可被转换为特定的时间值，并继续用在它变得过时之后的很长一段时间。在这种情况下，改为使用其中一种 SQL 函数。例如，CURRENT_DATE + 1 比 'tomorrow'::date 更安全。
While the input strings now, today, tomorrow, and yesterday are fine to use in interactive SQL commands, they can have surprising behavior when the command is saved to be executed later, for example in prepared statements, views, and function definitions. The string can be converted to a specific time value that continues to be used long after it becomes stale. Use one of the SQL functions instead in such contexts. For example, CURRENT_DATE + 1 is safer than 'tomorrow'::date.
8.5.2. Date/Time Output #
日期/时间类型的输出格式可以设置为四种格式之一：ISO 8601、SQL（Ingres）、传统的 POSTGRES（Unix 日期格式）或德语。默认格式是 ISO 格式。（SQL 标准要求使用 ISO 8601 格式。 “SQL” 输出格式的名称是一个历史巧合。） Table 8.14示出了每种输出格式的示例。_date_和 _time_类型的输出通常仅是日期部分或时间部分，与给定的示例一致。然而，POSTGRES 样式以 ISO 格式输出仅日期值。
The output format of the date/time types can be set to one of the four styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date format), or German. The default is the ISO format. (The SQL standard requires the use of the ISO 8601 format. The name of the “SQL” output format is a historical accident.) Table 8.14 shows examples of each output style. The output of the date and time types is generally only the date or time part in accordance with the given examples. However, the POSTGRES style outputs date-only values in ISO format.
Table 8.14. Date/Time Output Styles
Style Specification |
Description |
Example |
ISO |
ISO 8601, SQL standard |
1997-12-17 07:37:16-08 |
SQL |
traditional style |
12/17/1997 07:37:16.00 PST |
Postgres |
original style |
Wed Dec 17 07:37:16 1997 PST |
German |
regional style |
17.12.1997 07:37:16.00 PST |
Note
ISO 8601 规定使用大写字母 T 分隔日期和时间。PostgreSQL 在输入中接受该格式，但在输出中使用空格而不是 T，如上所示。这是为了可读性和与 RFC 3339 及其他一些数据库系统的一致性。
ISO 8601 specifies the use of uppercase letter T to separate the date and time. PostgreSQL accepts that format on input, but on output it uses a space rather than T, as shown above. This is for readability and for consistency with RFC 3339 as well as some other database systems.
在 SQL 和 POSTGRES 样式中，如果指定了 DMY 字段顺序，则 day 会出现在 month 之前，否则 month 会出现在 day 之前。（请参阅 Section 8.5.1，了解此设置如何影响输入值的解释。） Table 8.15显示示例。
In the SQL and POSTGRES styles, day appears before month if DMY field ordering has been specified, otherwise month appears before day. (See Section 8.5.1 for how this setting also affects interpretation of input values.) Table 8.15 shows examples.
Table 8.15. Date Order Conventions
datestyle Setting |
Input Ordering |
Example Output |
SQL, DMY |
day/month/year |
17/12/1997 15:37:16.00 CET |
SQL, MDY |
month/day/year |
12/17/1997 07:37:16.00 PST |
Postgres, DMY |
day/month/year |
Wed 17 Dec 07:37:16 1997 PST |
在 ISO 样式中，时区始终显示为 UTC 的带符号数值偏移，格林威治以东的时区使用正号。如果偏移量是整数小时数，则会显示为 hh（仅小时）；如果偏移量是整数分钟数，则显示为 hh:_mm_；如果偏移量是整数秒数，则显示为 hh:_mm_:_ss_。（第三种情况在任何现代时区标准中都不可能出现，但当处理早于采用标准时区的时戳时，可能会出现。）在其他日期样式中，如果时区在当前区域中常用，则时区将显示为字母缩写。否则，它将显示为 ISO 8601 基本格式的带符号数值偏移（hh 或 hhmm）。
In the ISO style, the time zone is always shown as a signed numeric offset from UTC, with positive sign used for zones east of Greenwich. The offset will be shown as hh (hours only) if it is an integral number of hours, else as hh:_mm_ if it is an integral number of minutes, else as hh:_mm_:_ss_. (The third case is not possible with any modern time zone standard, but it can appear when working with timestamps that predate the adoption of standardized time zones.) In the other date styles, the time zone is shown as an alphabetic abbreviation if one is in common use in the current zone. Otherwise it appears as a signed numeric offset in ISO 8601 basic format (hh or hhmm).
日期/时间样式可以选择使用 _SET datestyle_命令、_postgresql.conf_配置文件中的 DateStyle参数或服务器或客户端上的 _PGDATESTYLE_环境变量。
The date/time style can be selected by the user using the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client.
格式化函数 to_char（见 Section 9.8）也可作为一种更灵活的方式来格式化日期/时间输出。
The formatting function to_char (see Section 9.8) is also available as a more flexible way to format date/time output.
8.5.3. Time Zones #
时区和时区约定受政治决策影响，而不仅仅是地球几何形状。世界各地的时区在20世纪已经得到了一些标准化，但仍然容易受到任意变化的影响，特别是在夏令时规则方面。PostgreSQL 使用广泛使用的 IANA（奥尔森）时区数据库来获取有关历史时区规则的信息。对于未来的时间，假设给定时区的最新已知规则将继续无限期地遵守。
Time zones, and time-zone conventions, are influenced by political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900s, but continue to be prone to arbitrary changes, particularly with respect to daylight-savings rules. PostgreSQL uses the widely-used IANA (Olson) time zone database for information about historical time zone rules. For times in the future, the assumption is that the latest known rules for a given time zone will continue to be observed indefinitely far into the future.
PostgreSQL 努力与 SQL 标准定义保持兼容，以供典型用法。然而，SQL 标准对日期和时间类型和功能有一种奇怪的混合。两个明显的问题是：
PostgreSQL endeavors to be compatible with the SQL standard definitions for typical usage. However, the SQL standard has an odd mix of date and time types and capabilities. Two obvious problems are:
为了解决这些困难，我们建议在使用时区时使用同时包含日期和时间的日期/时间类型。我们 not 建议使用 time with time zone 类型（尽管 PostgreSQL 出于旧式应用程序和遵守 SQL 标准的目的而支持它）。PostgreSQL 假设仅包含日期或时间的任何类型的本地时区。
To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.
所有支持时区的日期和时间都以 UTC 格式内部存储。它们在显示给客户端之前，会根据 TimeZone配置参数指定的时区转换为当地时间。
All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.
PostgreSQL 允许您以三种不同的形式指定时区：
PostgreSQL allows you to specify time zones in three different forms:
简而言之，这就是缩写和全称之间的区别：缩写表示与 UTC 的特定偏移，而很多全称暗示了当地的夏令时规则，因此可能有两种可能的 UTC 偏移。例如，2014-06-04 12:00 America/New_York 表示纽约的本地时间正午，对于这个特定日期，它是东部夏令时（UTC-4）。因此，2014-06-04 12:00 EDT 指定了同一时间瞬间。但是，2014-06-04 12:00 EST 指定了东部标准时间（UTC-5）的正午，而不管该日期是否名义上实施夏令时制。
In short, this is the difference between abbreviations and full names: abbreviations represent a specific offset from UTC, whereas many of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets. As an example, 2014-06-04 12:00 America/New_York represents noon local time in New York, which for this particular date was Eastern Daylight Time (UTC-4). So 2014-06-04 12:00 EDT specifies that same time instant. But 2014-06-04 12:00 EST specifies noon Eastern Standard Time (UTC-5), regardless of whether daylight savings was nominally in effect on that date.
为了使事情变得复杂，一些司法管辖区使用了相同的时区缩写来表示不同时期的不同 UTC 偏移量；例如，在莫斯科中 MSK 在某些年份中表示 UTC+3 而在其他年份中表示 UTC+4。PostgreSQL 根据指定日期的含义（或最近的含义）解析此类缩写；但与上述 EST 示例一样，这不一定是该日期的当地民用时间。
To complicate matters, some jurisdictions have used the same timezone abbreviation to mean different UTC offsets at different times; for example, in Moscow MSK has meant UTC+3 in some years and UTC+4 in others. PostgreSQL interprets such abbreviations according to whatever they meant (or had most recently meant) on the specified date; but, as with the EST example above, this is not necessarily the same as local civil time on that date.
在所有情况下，时区名称和缩写都区分大小写。（这是与 8.2 之前的 PostgreSQL 版本相比的更改，后者在某些情况下区分大小写，而在其他情况下不区分大小写。）
In all cases, timezone names and abbreviations are recognized case-insensitively. (This is a change from PostgreSQL versions prior to 8.2, which were case-sensitive in some contexts but not others.)
时区名称或缩写都没有硬连接到服务器；它们是从安装目录的 _…/share/timezone/_和 _…/share/timezonesets/_下存储的配置文件中获取的（参见 Section B.4）。
Neither timezone names nor abbreviations are hard-wired into the server; they are obtained from configuration files stored under …/share/timezone/ and …/share/timezonesets/ of the installation directory (see Section B.4).
TimeZone配置参数可以在文件 _postgresql.conf_中设置，或在 Chapter 20中描述的任何其他标准方式中设置。还有一些特殊的方式来设置它：
The TimeZone configuration parameter can be set in the file postgresql.conf, or in any of the other standard ways described in Chapter 20. There are also some special ways to set it:
8.5.4. Interval Input #
interval 值可以使用以下详细语法编写：
interval values can be written using the following verbose syntax:
[@] quantity unit [quantity unit...] [direction]quantity_为数字（可能带有符号）；_unit_为 _microsecond、millisecond、second、minute、hour、day、week、month、year、decade、century、millennium、或这些单位的缩写或复数；direction_可以是 _ago_或空。at 符号 (@) 是可选的噪声。不同单位的量会隐式相加，并进行相应的符号核算。_ago_会对所有字段取反。如果 IntervalStyle设置为 _postgres_verbose，则此语法也会用于间隔输出。
where quantity is a number (possibly signed); unit is microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, or abbreviations or plurals of these units; direction can be ago or empty. The at sign (@) is optional noise. The amounts of the different units are implicitly added with appropriate sign accounting. ago negates all the fields. This syntax is also used for interval output, if IntervalStyle is set to postgres_verbose.
天、小时、分钟和秒的数量可以在没有显式单位标记的情况下指定。例如，'1 12:59:10' 与 '1 day 12 hours 59 min 10 sec' 相同。此外，可以使用破折号指定年和月的组合；例如 '200-10' 与 '200 years 10 months' 相同。（实际上，这些较短的格式是 SQL 标准唯一允许的格式，并且在 IntervalStyle 设置为 sql_standard 时用于输出。）
Quantities of days, hours, minutes, and seconds can be specified without explicit unit markings. For example, '1 12:59:10' is read the same as '1 day 12 hours 59 min 10 sec'. Also, a combination of years and months can be specified with a dash; for example '200-10' is read the same as '200 years 10 months'. (These shorter forms are in fact the only ones allowed by the SQL standard, and are used for output when IntervalStyle is set to sql_standard.)
间隔值也可以写为 ISO 8601 时间间隔，使用该标准 4.4.3.2 部分的“带有指示符的格式”或 4.4.3.3 部分的“备用格式”。带有指示符的格式如下所示：
Interval values can also be written as ISO 8601 time intervals, using either the “format with designators” of the standard’s section 4.4.3.2 or the “alternative format” of section 4.4.3.3. The format with designators looks like this:
P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]字符串必须以 P_开头，并且可能包含引入一天中时间单位的 _T。 Table 8.16中给出了可用的单位缩写。可以省略单位，并且可以按任何顺序指定单位，但比一天小的单位必须出现在 _T_之后。特别是，_M_的含义取决于它是在 _T_之前还是之后。
The string must start with a P, and may include a T that introduces the time-of-day units. The available unit abbreviations are given in Table 8.16. Units may be omitted, and may be specified in any order, but units smaller than a day must appear after T. In particular, the meaning of M depends on whether it is before or after T.
Table 8.16. ISO 8601 Interval Unit Abbreviations
Abbreviation |
Meaning |
Y |
Years |
M |
Months (in the date part) |
W |
Weeks |
D |
Days |
H |
Hours |
M |
Minutes (in the time part) |
S |
Seconds |
在备用格式中：
In the alternative format:
P [ years-months-days ] [ T hours:minutes:seconds ]字符串必须以 P 开头，T 分隔间隔的日期和时间部分。这些值类似于 ISO 8601 日期以数字给出。
the string must begin with P, and a T separates the date and time parts of the interval. The values are given as numbers similar to ISO 8601 dates.
使用 fields 规范编写间隔常量或将字符串分配给使用 fields 规范定义的间隔列时，未标记数量的解释取决于 fields。例如 INTERVAL '1' YEAR 被读为 1 年，而 INTERVAL '1' 表示 1 秒。此外，fields 规范允许的最小有效字段“右侧”的字段值将被静默丢弃。例如，编写 INTERVAL '1 day 2:03:04' HOUR TO MINUTE 将导致删除秒字段，但不会删除天字段。
When writing an interval constant with a fields specification, or when assigning a string to an interval column that was defined with a fields specification, the interpretation of unmarked quantities depends on the fields. For example INTERVAL '1' YEAR is read as 1 year, whereas INTERVAL '1' means 1 second. Also, field values “to the right” of the least significant field allowed by the fields specification are silently discarded. For example, writing INTERVAL '1 day 2:03:04' HOUR TO MINUTE results in dropping the seconds field, but not the day field.
根据 SQL 标准，间隔值的所有字段必须具有相同的符号，因此前导负号适用于所有字段；例如，间隔文字 '-1 2:03:04' 中的负号同时适用于天数和小时/分钟/秒部分。PostgreSQL 允许字段具有不同的符号，并且传统上将文本表示中的每个字段视为独立的符号，因此在这个示例中，小时/分钟/秒部分被认为是正的。如果将 IntervalStyle 设置为 sql_standard，则前导符号将被视为适用于所有字段（但前提是没有出现其他符号）。否则将采用传统的 PostgreSQL 解释。为了避免歧义，建议对任何负字段附加显式符号。
According to the SQL standard all fields of an interval value must have the same sign, so a leading negative sign applies to all fields; for example the negative sign in the interval literal '-1 2:03:04' applies to both the days and hour/minute/second parts. PostgreSQL allows the fields to have different signs, and traditionally treats each field in the textual representation as independently signed, so that the hour/minute/second part is considered positive in this example. If IntervalStyle is set to sql_standard then a leading sign is considered to apply to all fields (but only if no additional signs appear). Otherwise the traditional PostgreSQL interpretation is used. To avoid ambiguity, it’s recommended to attach an explicit sign to each field if any field is negative.
在内部，interval 值存储为三个整数字段：月、日和微秒。这些字段是分开的，因为一个月的日子数是可变的，而如果涉及夏令时过渡，一天可能有 23 或 25 小时。使用其他单位的间隔输入字符串已标准化为这种格式，然后以标准化方式重建以进行输出，例如：
Internally, interval values are stored as three integral fields: months, days, and microseconds. These fields are kept separate because the number of days in a month varies, while a day can have 23 or 25 hours if a daylight savings time transition is involved. An interval input string that uses other units is normalized into this format, and then reconstructed in a standardized way for output, for example:
SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
interval
---------------------------------------
3 years 3 mons 700 days 133:17:36.789这里，“7 天”为一周理解，已被分开，而较小和较大的时间单位已合并并标准化。
Here weeks, which are understood as “7 days”, have been kept separate, while the smaller and larger time units were combined and normalized.
输入字段值可以有分数部分，例如 '1.5 weeks' 或 '01:02:03.45'。但是，因为 interval 内部仅存储整数字段，所以分数值必须转换为更小的单位。大于月份的单位的分数部分被舍入为一个月的整数，例如 '1.5 years' 变为 '1 year 6 mons'。星期和天数的分数部分计算为天数和微秒的整数，假设每月 30 天，每天 24 小时，例如 '1.75 months' 变为 1 mon 22 days 12:00:00。只有秒在输出时才会显示为分数。
Input field values can have fractional parts, for example '1.5 weeks' or '01:02:03.45'. However, because interval internally stores only integral fields, fractional values must be converted into smaller units. Fractional parts of units greater than months are rounded to be an integer number of months, e.g. '1.5 years' becomes '1 year 6 mons'. Fractional parts of weeks and days are computed to be an integer number of days and microseconds, assuming 30 days per month and 24 hours per day, e.g., '1.75 months' becomes 1 mon 22 days 12:00:00. Only seconds will ever be shown as fractional on output.
Table 8.17显示了有效_interval_输入的一些示例。
Table 8.17 shows some examples of valid interval input.
Table 8.17. Interval Input
Example |
Description |
1-2 |
SQL standard format: 1 year 2 months |
3 4:05:06 |
SQL standard format: 3 days 4 hours 5 minutes 6 seconds |
1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
Traditional Postgres format: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
P1Y2M3DT4H5M6S |
ISO 8601 “format with designators”: same meaning as above |
P0001-02-03T04:05:06 |
ISO 8601 “alternative format”: same meaning as above |
8.5.5. Interval Output #
如前所述，PostgreSQL 将 interval 值存储为月、日和微秒。对于输出，月份字段通过除以 12 转换为年和月。天字段按原样显示。微秒字段转换为小时、分钟、秒和小数秒。因此，月、分和秒将永远不会显示为超过 0-11、0-59 和 0-59 的范围，而显示的年、天和小时字段可能很大。（如果需要将较大的天或小时值转置到下一个较高字段，可以使用 justify_days 和 justify_hours 函数。）
As previously explained, PostgreSQL stores interval values as months, days, and microseconds. For output, the months field is converted to years and months by dividing by 12. The days field is shown as-is. The microseconds field is converted to hours, minutes, seconds, and fractional seconds. Thus months, minutes, and seconds will never be shown as exceeding the ranges 0–11, 0–59, and 0–59 respectively, while the displayed years, days, and hours fields can be quite large. (The justify_days and justify_hours functions can be used if it is desirable to transpose large days or hours values into the next higher field.)
使用命令_SET intervalstyle_，间隔类型的输出格式可以设置成四种样式之一_sql_standardpostgrespostgres_verbose_或者_iso_8601_。默认是_postgres_样式。 Table 8.18显示了每种输出样式的示例。
The output format of the interval type can be set to one of the four styles sql_standard, postgres, postgres_verbose, or iso_8601, using the command SET intervalstyle. The default is the postgres format. Table 8.18 shows examples of each output style.
如果区间值符合标准的限制（仅限年-月或仅限一天-时间，且不允许正负组件混合），则 sql_standard 格式生成符合 SQL 标准对区间字面值字符串的规范的输出。否则，输出看起来像一个标准的年-月字面值字符串，后接一个日-时字面值字符串，使用明确的符号消歧义混合符号区间。
The sql_standard style produces output that conforms to the SQL standard’s specification for interval literal strings, if the interval value meets the standard’s restrictions (either year-month only or day-time only, with no mixing of positive and negative components). Otherwise the output looks like a standard year-month literal string followed by a day-time literal string, with explicit signs added to disambiguate mixed-sign intervals.
_postgres_样式的输出匹配 DateStyle参数设置为_ISO_时8.4之前的PostgreSQL发布版本的输出。
The output of the postgres style matches the output of PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to ISO.
postgres_verbose 格式的输出与在 DateStyle 参数设置为非 ISO 输出时，PostgreSQL 8.4 之前版本的输出相匹配。
The output of the postgres_verbose style matches the output of PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to non-ISO output.
iso_8601 格式的输出与 ISO 8601 标准的章节 4.4.3.2 中描述的“带指示符的格式”相匹配。
The output of the iso_8601 style matches the “format with designators” described in section 4.4.3.2 of the ISO 8601 standard.
Table 8.18. Interval Output Style Examples
Style Specification |
Year-Month Interval |
Day-Time Interval |
Mixed Interval |
sql_standard |
1-2 |
3 4:05:06 |
-1-2 +3 -4:05:06 |
postgres |
1 year 2 mons |
3 days 04:05:06 |
-1 year -2 mons +3 days -04:05:06 |
postgres_verbose |
@ 1 year 2 mons |
@ 3 days 4 hours 5 mins 6 secs |
@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago |
iso_8601 |
P1Y2M |
P3DT4H5M6S |
P-1Y-2M3DT-4H-5M-6S |