Postgresql 中文操作指南
9.9. Date/Time Functions and Operators #
Table 9.33显示了可用于日期/时间值处理的可用函数，详细信息见以下小节中。 Table 9.32说明了基本算术运算符（+、_*_等）的行为。有关格式化函数，请参阅 Section 9.8。您应该熟悉 Section 8.5中有关日期/时间数据类型的背景信息。
Table 9.33 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 9.32 illustrates the behaviors of the basic arithmetic operators (+, *, etc.). For formatting functions, refer to Section 9.8. You should be familiar with the background information on date/time data types from Section 8.5.
此外， Table 9.1中所示的常用比较运算符可用于日期/时间类型。日期和时间戳（带有或不带有时区）都是可比较的，而时间（带有或不带有时区）和时间间隔只能与同一数据类型的其他值进行比较。在将没有时区的时间戳与带有时区的时间戳进行比较时，会假设前者使用 TimeZone配置参数指定时区给出，并且会将其旋转到 UTC 以便与后者值（内部已经在 UTC 中）进行比较。类似地，在将日期值与时间戳进行比较时，会假设一个日期值表示 _TimeZone_时区的午夜。
In addition, the usual comparison operators shown in Table 9.1 are available for the date/time types. Dates and timestamps (with or without time zone) are all comparable, while times (with or without time zone) and intervals can only be compared to other values of the same data type. When comparing a timestamp without time zone to a timestamp with time zone, the former value is assumed to be given in the time zone specified by the TimeZone configuration parameter, and is rotated to UTC for comparison to the latter value (which is already in UTC internally). Similarly, a date value is assumed to represent midnight in the TimeZone zone when comparing it to a timestamp.
下面描述的所有函数和运算符采用 time 或 timestamp 作为输入时实际上分为两个变体：一种采用 time with time zone 或 timestamp with time zone，另一种采用 time without time zone 或 timestamp without time zone。为简便起见，这些变体不单独显示。此外，+ 和 * 运算符呈交换对（例如 date + integer 和 integer + date）；我们仅显示每一对中的一个。
All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone. For brevity, these variants are not shown separately. Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair.
Table 9.32. Date/Time Operators
Operator Description Example(s) |
date + integer → date Add a number of days to a date date '2001-09-28' + 7 → 2001-10-05 |
date + interval → timestamp Add an interval to a date date '2001-09-28' + interval '1 hour' → 2001-09-28 01:00:00 |
date + time → timestamp Add a time-of-day to a date date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00 |
interval + interval → interval Add intervals interval '1 day' + interval '1 hour' → 1 day 01:00:00 |
timestamp + interval → timestamp Add an interval to a timestamp timestamp '2001-09-28 01:00' + interval '23 hours' → 2001-09-29 00:00:00 |
time + interval → time Add an interval to a time time '01:00' + interval '3 hours' → 04:00:00 |
- interval → interval Negate an interval - interval '23 hours' → -23:00:00 |
date - date → integer Subtract dates, producing the number of days elapsed date '2001-10-01' - date '2001-09-28' → 3 |
date - integer → date Subtract a number of days from a date date '2001-10-01' - 7 → 2001-09-24 |
date - interval → timestamp Subtract an interval from a date date '2001-09-28' - interval '1 hour' → 2001-09-27 23:00:00 |
time - time → interval Subtract times time '05:00' - time '03:00' → 02:00:00 |
time - interval → time Subtract an interval from a time time '05:00' - interval '2 hours' → 03:00:00 |
timestamp - interval → timestamp Subtract an interval from a timestamp timestamp '2001-09-28 23:00' - interval '23 hours' → 2001-09-28 00:00:00 |
interval - interval → interval Subtract intervals interval '1 day' - interval '1 hour' → 1 day -01:00:00 |
timestamp - timestamp → interval Subtract timestamps (converting 24-hour intervals into days, similarly to justify_hours()) timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' → 63 days 15:00:00 |
interval * double precision → interval Multiply an interval by a scalar interval '1 second' * 900 → 00:15:00 interval '1 day' * 21 → 21 days interval '1 hour' * 3.5 → 03:30:00 |
interval / double precision → interval Divide an interval by a scalar interval '1 hour' / 1.5 → 00:40:00 |
Table 9.33. Date/Time Functions
Function Description Example(s) |
age ( timestamp, timestamp ) → interval Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days age(timestamp '2001-04-10', timestamp '1957-06-13') → 43 years 9 mons 27 days |
age ( timestamp ) → interval Subtract argument from current_date (at midnight) age(timestamp '1957-06-13') → 62 years 6 mons 10 days |
clock_timestamp ( ) → timestamp with time zone Current date and time (changes during statement execution); see Section 9.9.5 clock_timestamp() → 2019-12-23 14:39:53.662522-05 |
current_date → date Current date; see Section 9.9.5 current_date → 2019-12-23 |
current_time → time with time zone Current time of day; see Section 9.9.5 current_time → 14:39:53.662522-05 |
current_time ( integer ) → time with time zone Current time of day, with limited precision; see Section 9.9.5 current_time(2) → 14:39:53.66-05 |
current_timestamp → timestamp with time zone Current date and time (start of current transaction); see Section 9.9.5 current_timestamp → 2019-12-23 14:39:53.662522-05 |
current_timestamp ( integer ) → timestamp with time zone Current date and time (start of current transaction), with limited precision; see Section 9.9.5 current_timestamp(0) → 2019-12-23 14:39:53-05 |
date_add ( timestamp with time zone, interval [, text ] ) → timestamp with time zone Add an interval to a timestamp with time zone, computing times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted. The form with two arguments is equivalent to the timestamp with time zone + interval operator. date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw') → 2021-10-31 23:00:00+00 |
date_bin ( interval, timestamp, timestamp ) → timestamp Bin input into specified interval aligned with specified origin; see Section 9.9.3 date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00') → 2001-02-16 20:35:00 |
date_part ( text, timestamp ) → double precision Get timestamp subfield (equivalent to extract); see Section 9.9.1 date_part('hour', timestamp '2001-02-16 20:38:40') → 20 |
date_part ( text, interval ) → double precision Get interval subfield (equivalent to extract); see Section 9.9.1 date_part('month', interval '2 years 3 months') → 3 |
date_subtract ( timestamp with time zone, interval [, text ] ) → timestamp with time zone Subtract an interval from a timestamp with time zone, computing times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted. The form with two arguments is equivalent to the timestamp with time zone - interval operator. date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw') → 2021-10-30 22:00:00+00 |
date_trunc ( text, timestamp ) → timestamp Truncate to specified precision; see Section 9.9.2 date_trunc('hour', timestamp '2001-02-16 20:38:40') → 2001-02-16 20:00:00 |
date_trunc ( text, timestamp with time zone, text ) → timestamp with time zone Truncate to specified precision in the specified time zone; see Section 9.9.2 date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') → 2001-02-16 13:00:00+00 |
date_trunc ( text, interval ) → interval Truncate to specified precision; see Section 9.9.2 date_trunc('hour', interval '2 days 3 hours 40 minutes') → 2 days 03:00:00 |
extract ( field from timestamp ) → numeric Get timestamp subfield; see Section 9.9.1 extract(hour from timestamp '2001-02-16 20:38:40') → 20 |
extract ( field from interval ) → numeric Get interval subfield; see Section 9.9.1 extract(month from interval '2 years 3 months') → 3 |
isfinite ( date ) → boolean Test for finite date (not +/-infinity) isfinite(date '2001-02-16') → true |
isfinite ( timestamp ) → boolean Test for finite timestamp (not +/-infinity) isfinite(timestamp 'infinity') → false |
isfinite ( interval ) → boolean Test for finite interval (currently always true) isfinite(interval '4 hours') → true |
justify_days ( interval ) → interval Adjust interval, converting 30-day time periods to months justify_days(interval '1 year 65 days') → 1 year 2 mons 5 days |
justify_hours ( interval ) → interval Adjust interval, converting 24-hour time periods to days justify_hours(interval '50 hours 10 minutes') → 2 days 02:10:00 |
justify_interval ( interval ) → interval Adjust interval using justify_days and justify_hours, with additional sign adjustments justify_interval(interval '1 mon -1 hour') → 29 days 23:00:00 |
localtime → time Current time of day; see Section 9.9.5 localtime → 14:39:53.662522 |
localtime ( integer ) → time Current time of day, with limited precision; see Section 9.9.5 localtime(0) → 14:39:53 |
localtimestamp → timestamp Current date and time (start of current transaction); see Section 9.9.5 localtimestamp → 2019-12-23 14:39:53.662522 |
localtimestamp ( integer ) → timestamp Current date and time (start of current transaction), with limited precision; see Section 9.9.5 localtimestamp(2) → 2019-12-23 14:39:53.66 |
make_date ( year int, month int, day int ) → date Create date from year, month and day fields (negative years signify BC) make_date(2013, 7, 15) → 2013-07-15 |
make_interval ( [ years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]] ) → interval Create interval from years, months, weeks, days, hours, minutes and seconds fields, each of which can default to zero make_interval(days ⇒ 10) → 10 days |
make_time ( hour int, min int, sec double precision ) → time Create time from hour, minute and seconds fields make_time(8, 15, 23.5) → 08:15:23.5 |
make_timestamp ( year int, month int, day int, hour int, min int, sec double precision ) → timestamp Create timestamp from year, month, day, hour, minute and seconds fields (negative years signify BC) make_timestamp(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5 |
make_timestamptz ( year int, month int, day int, hour int, min int, sec double precision [, timezone text ] ) → timestamp with time zone Create timestamp with time zone from year, month, day, hour, minute and seconds fields (negative years signify BC). If timezone is not specified, the current time zone is used; the examples assume the session time zone is Europe/London make_timestamptz(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5+01 make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York') → 2013-07-15 13:15:23.5+01 |
now ( ) → timestamp with time zone Current date and time (start of current transaction); see Section 9.9.5 now() → 2019-12-23 14:39:53.662522-05 |
statement_timestamp ( ) → timestamp with time zone Current date and time (start of current statement); see Section 9.9.5 statement_timestamp() → 2019-12-23 14:39:53.662522-05 |
timeofday ( ) → text Current date and time (like clock_timestamp, but as a text string); see Section 9.9.5 timeofday() → Mon Dec 23 14:39:53.662522 2019 EST |
transaction_timestamp ( ) → timestamp with time zone Current date and time (start of current transaction); see Section 9.9.5 transaction_timestamp() → 2019-12-23 14:39:53.662522-05 |
to_timestamp ( double precision ) → timestamp with time zone Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone to_timestamp(1284352323) → 2010-09-13 04:32:03+00 |
除了这些函数外，还支持 SQL OVERLAPS 运算符：
In addition to these functions, the SQL OVERLAPS operator is supported:
(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)当两个时间段（由时间段的开始和结束时间确定）重叠时，此表达式产生真值，不重叠时产生假值。结束时间可以指定为成对的日期、时间或时间戳；或为日期、时间或时间戳后跟一个间隔。当提供一对值时，可以先写入结束时间还是结束时间；OVERLAPS_自动将该对中的早些值作为开始时间。每个时间段被认为表示半开区间 _start ⇐ time < end，除非 start 和 end 相等，在这种情况下，它表示单个时间的瞬间。这表示两个时间段仅有一个结束时间重合就不会重叠。
This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval. When a pair of values is provided, either the start or the end can be written first; OVERLAPS automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start ⇐ time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.
SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
Result: false
SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30', DATE '2001-10-31');
Result: false
SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
(DATE '2001-10-30', DATE '2001-10-31');
Result: true当添加一个 interval 值（或从 timestamp 或 timestamp with time zone 值中减去一个 interval 值）到或从一个 interval 值中，这些 interval 值的月份、天数和微秒数将会被依次处理。首先，非零的月份数会根据月份数量，向前或向后推移或递减时间戳日期，除非这超过新月份的结束时间，则会使用该月份的最后一天。（例如，3月31日加1个月变成4月30日，但3月31日加2个月变成5月31日。）然后，天数会根据天数的数量，向前或向后推移或递减时间戳日期。在这两个步骤中，一天的时间会保持不变。最后，如果有一个非零的微秒数，它将会被相加或相减。当对一个 timestamp with time zone 值做算术时，该 timestamp with time zone 值位于识别夏时制时区中，这表示添加或减去（例如）interval '1 day'_并不一定会产生与添加或减去 _interval '24 hours' 相同的结果。比如，当会话时区设置为 America/Denver 时：
When adding an interval value to (or subtracting an interval value from) a timestamp or timestamp with time zone value, the months, days, and microseconds fields of the interval value are handled in turn. First, a nonzero months field advances or decrements the date of the timestamp by the indicated number of months, keeping the day of month the same unless it would be past the end of the new month, in which case the last day of that month is used. (For example, March 31 plus 1 month becomes April 30, but March 31 plus 2 months becomes May 31.) Then the days field advances or decrements the date of the timestamp by the indicated number of days. In both these steps the local time of day is kept the same. Finally, if there is a nonzero microseconds field, it is added or subtracted literally. When doing arithmetic on a timestamp with time zone value in a time zone that recognizes DST, this means that adding or subtracting (say) interval '1 day' does not necessarily have the same result as adding or subtracting interval '24 hours'. For example, with the session time zone set to America/Denver:
SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';
Result: 2005-04-03 12:00:00-06
SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';
Result: 2005-04-03 13:00:00-06这是因为在时间 2005-04-03 02:00:00，由于时区 America/Denver 的夏令时发生了变化，导致跳过了一个小时。
This happens because an hour was skipped due to a change in daylight saving time at 2005-04-03 02:00:00 in time zone America/Denver.
请注意，由 age 返回的 months 域中可能有歧义，因为不同的月份有不同的天数。PostgreSQL 的方法在计算不完整月份时使用两个日期中较早的月份。例如，age('2004-06-01', '2004-04-30')_使用四月来生成 _1 mon 1 day，而使用五月会生成 1 mon 2 days，因为五月有31天，而四月只有30天。
Note there can be ambiguity in the months field returned by age because different months have different numbers of days. PostgreSQL’s approach uses the month from the earlier of the two dates when calculating partial months. For example, age('2004-06-01', '2004-04-30') uses April to yield 1 mon 1 day, while using May would yield 1 mon 2 days because May has 31 days, while April has only 30.
日期和时间戳的减法也很复杂。一个概念上简单的方法执行减法是使用 EXTRACT(EPOCH FROM …) 将个值转换为秒数，然后减去结果；这将生成两个值之间的 seconds 个数。这会根据每个月份的天数、时区变更和夏令时调整来进行调整。使用 “-” 运算符对日期或时间戳的值进行减法返回两个值之间的天数（24 小时）和小时数/分钟数/秒数，并进行相同的调整。age 函数返回年数、月数、天数和小时数/分钟数/秒数，执行逐域减法，然后调整负域值。以下查询展示了这些方法中的差异。示例结果使用 timezone = 'US/Eastern' 生成的，两个日期之间有夏令时变化：
Subtraction of dates and timestamps can also be complex. One conceptually simple way to perform subtraction is to convert each value to a number of seconds using EXTRACT(EPOCH FROM …), then subtract the results; this produces the number of seconds between the two values. This will adjust for the number of days in each month, timezone changes, and daylight saving time adjustments. Subtraction of date or timestamp values with the “-” operator returns the number of days (24-hours) and hours/minutes/seconds between the values, making the same adjustments. The age function returns years, months, days, and hours/minutes/seconds, performing field-by-field subtraction and then adjusting for negative field values. The following queries illustrate the differences in these approaches. The sample results were produced with timezone = 'US/Eastern'; there is a daylight saving time change between the two dates used:
SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Result: 10537200.000000
SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
/ 60 / 60 / 24;
Result: 121.9583333333333333
SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
Result: 121 days 23:00:00
SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
Result: 4 mons9.9.1. EXTRACT, date_part #
EXTRACT(field FROM source)extract 函数从日期/时间值检索子域，例如年或小时。source 必须是类型为 timestamp、date、time 或 interval 的值表达式。（时间戳和时间可以带时区或不带时区。）field 是一个标识符或字符串，用于选择从源值中提取哪个域。并非所有域对于所有输入数据类型都是有效的；例如，无法从 date 中提取比一天小的域，而无法从 time 中提取一天或更长的域。extract 函数返回类型 numeric 的值。
The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, date, time, or interval. (Timestamps and times can be with or without time zone.) field is an identifier or string that selects what field to extract from the source value. Not all fields are valid for every input data type; for example, fields smaller than a day cannot be extracted from a date, while fields of a day or more cannot be extracted from a time. The extract function returns values of type numeric.
以下字段名称有效：
The following are valid field names:
-
century
-
The century; for interval values, the year field divided by 100
-
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
Result: 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 21
SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');
Result: 1
SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');
Result: -1
SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years');
Result: 20-
day
-
The day of the month (1–31); for interval values, the number of days
-
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 16
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
Result: 40-
decade
-
The year field divided by 10
-
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 200-
dow
-
The day of the week as Sunday (0) to Saturday (6)
-
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 5-
Note that extract's day of the week numbering differs from that of the to_char(…, 'D') function.
-
doy
-
-
The day of the year (1–365/366)
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 47-
epoch
-
For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (negative for timestamps before that); for date and timestamp values, the nominal number of seconds since 1970-01-01 00:00:00, without regard to timezone or daylight-savings rules; for interval values, the total number of seconds in the interval
-
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
Result: 982384720.120000
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
Result: 982355920.120000
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800.000000-
You can convert an epoch value back to a timestamp with time zone with to_timestamp:
SELECT to_timestamp(982384720.12);
Result: 2001-02-17 04:38:40.12+00-
Beware that applying to_timestamp to an epoch extracted from a date or timestamp value could produce a misleading result: the result will effectively assume that the original value had been given in UTC, which might not be the case.
-
hour
-
-
The hour field (0–23 in timestamps, unrestricted in intervals)
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20-
isodow
-
The day of the week as Monday (1) to Sunday (7)
-
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
Result: 7-
This is identical to dow except for Sunday. This matches the ISO 8601 day of the week numbering.
-
isoyear
-
-
The ISO 8601 week-numbering year that the date falls in
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
Result: 2005
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
Result: 2006-
Each ISO 8601 week-numbering year begins with the Monday of the week containing the 4th of January, so in early January or late December the ISO year may be different from the Gregorian year. See the week field for more information.
-
julian
-
-
The Julian Date corresponding to the date or timestamp. Timestamps that are not local midnight result in a fractional value. See Section B.7 for more information.
SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
Result: 2453737
SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
Result: 2453737.50000000000000000000-
microseconds
-
The seconds field, including fractional parts, multiplied by 1 000 000; note that this includes full seconds
-
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Result: 28500000-
millennium
-
The millennium; for interval values, the year field divided by 1000
-
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 3
SELECT EXTRACT(MILLENNIUM FROM INTERVAL '2001 years');
Result: 2-
Years in the 1900s are in the second millennium. The third millennium started January 1, 2001.
-
milliseconds
-
-
The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
Result: 28500.000-
minute
-
The minutes field (0–59)
-
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 38-
month
-
The number of the month within the year (1–12); for interval values, the number of months modulo 12 (0–11)
-
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
Result: 3
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
Result: 1-
quarter
-
The quarter of the year (1–4) that the date is in
-
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 1-
second
-
The seconds field, including any fractional seconds
-
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 40.000000
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
Result: 28.500000-
timezone
-
The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC. (Technically, PostgreSQL does not use UTC because leap seconds are not handled.)
-
-
timezone_hour
-
The hour component of the time zone offset
-
-
timezone_minute
-
The minute component of the time zone offset
-
-
week
-
The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year.
-
In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31 is part of the first week of 2013. It’s recommended to use the isoyear field together with week to get consistent results.
-
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 7-
year
-
The year field. Keep in mind there is no 0 AD, so subtracting BC years from AD years should be done with care.
-
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2001处理 interval 值时，extract 函数会产生匹配时间间隔输出函数所用解释的字段值。如果从非标准化的时间间隔表示开始，这可能会产生令人惊讶的结果，例如：
When processing an interval value, the extract function produces field values that match the interpretation used by the interval output function. This can produce surprising results if one starts with a non-normalized interval representation, for example:
SELECT INTERVAL '80 minutes';
Result: 01:20:00
SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
Result: 20Note
当输入值为 +/-Infinity 时，extract 为单调递增字段 (epoch、julian、year、isoyear、decade、century 和_millennium_) 返回 +/-Infinity。对于其他字段，返回 NULL。9.6 之前的 PostgreSQL 版本对于所有无限输入情况均返回零。
When the input value is +/-Infinity, extract returns +/-Infinity for monotonically-increasing fields (epoch, julian, year, isoyear, decade, century, and millennium). For other fields, NULL is returned. PostgreSQL versions before 9.6 returned zero for all cases of infinite input.
extract 函数主要用于计算处理。有关显示日期/时间值格式的信息，请参阅 Section 9.8。
The extract function is primarily intended for computational processing. For formatting date/time values for display, see Section 9.8.
date_part 函数模仿了 SQL 标准函数_extract_的传统 Ingres 等效项：
The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:
date_part('field', source)请注意，此处 field 参数需要为字符串值，而不是名称。date_part 的有效字段名称与 extract 的相同。出于历史原因，date_part 函数返回 double precision 类型的值。在某些用法中，这会导致精度损失。相反，建议使用 extract。
Note that here the field parameter needs to be a string value, not a name. The valid field names for date_part are the same as for extract. For historical reasons, the date_part function returns values of type double precision. This can result in a loss of precision in certain uses. Using extract is recommended instead.
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 49.9.2. date_trunc #
date_trunc 函数在概念上类似于数字_trunc_函数。
The function date_trunc is conceptually similar to the trunc function for numbers.
date_trunc(field, source [, time_zone ])source 是类型 timestamp、timestamp with time zone 或 interval 的值表达式。（类型 date 和 time 的值分别自动转换为 timestamp 或 interval。）field 选择将输入值截断到何种精度。返回值同样为类型 timestamp、timestamp with time zone 或 interval，并且它具有一些低于所选字段的所有字段，这些字段设置为 0（或对于 day 和 month 而言为 1）。
source is a value expression of type timestamp, timestamp with time zone, or interval. (Values of type date and time are cast automatically to timestamp or interval, respectively.) field selects to which precision to truncate the input value. The return value is likewise of type timestamp, timestamp with time zone, or interval, and it has all fields that are less significant than the selected one set to zero (or one, for day and month).
field 的有效值为：
Valid values for field are:
当输入值类型为 timestamp with time zone 时，在特定时区执行截断；例如，截断至 day 产生该时区午夜的值。默认情况下，截断是在当前 TimeZone 设置的时区进行的，但可以提供可选的 time_zone 参数以指定其他时区。时区名称可以通过 Section 8.5.3 描述的任何方式指定。
When the input value is of type timestamp with time zone, the truncation is performed with respect to a particular time zone; for example, truncation to day produces a value that is midnight in that zone. By default, truncation is done with respect to the current TimeZone setting, but the optional time_zone argument can be provided to specify a different time zone. The time zone name can be specified in any of the ways described in Section 8.5.3.
处理 timestamp without time zone 或 interval 输入时不能指定时区。这些始终按面值接受。
A time zone cannot be specified when processing timestamp without time zone or interval inputs. These are always taken at face value.
示例（假设本地时区为 America/New_York）：
Examples (assuming the local time zone is America/New_York):
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Result: 2001-02-16 00:00:00-05
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
Result: 2001-02-16 08:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:009.9.3. date_bin #
函数 date_bin 将输入时间戳“分箱”到与指定原点对齐的指定间隔（stride）中。
The function date_bin “bins” the input timestamp into the specified interval (the stride) aligned with a specified origin.
date_bin(stride, source, origin)source 是类型 timestamp 或 timestamp with time zone 的值表达式。（类型 date 的值自动转换为 timestamp。）stride 是类型 interval 的值表达式。返回值同样为类型 timestamp 或 timestamp with time zone，并且它标记了将_source_放置其中的分箱的开端。
source is a value expression of type timestamp or timestamp with time zone. (Values of type date are cast automatically to timestamp.) stride is a value expression of type interval. The return value is likewise of type timestamp or timestamp with time zone, and it marks the beginning of the bin into which the source is placed.
示例：
Examples:
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30对于整单位（1 分钟、1 小时等），这会产生与类似 date_trunc 调用相同的结果，但不同之处在于，date_bin 可以截断为任意间隔。
In the case of full units (1 minute, 1 hour, etc.), it gives the same result as the analogous date_trunc call, but the difference is that date_bin can truncate to an arbitrary interval.
stride 间隔必须大于 0，并且不能包含 month 或更大的单位。
The stride interval must be greater than zero and cannot contain units of month or larger.
9.9.4. AT TIME ZONE #
AT TIME ZONE 运算符将时间戳 without 时区转换为/从时间戳 with 时区及 time with time zone 值转换为不同的时区。 Table 9.34 显示了其变项。
The AT TIME ZONE operator converts time stamp without time zone to/from time stamp with time zone, and time with time zone values to different time zones. Table 9.34 shows its variants.
Table 9.34. AT TIME ZONE Variants
Table 9.34. AT TIME ZONE Variants
Operator Description Example(s) |
timestamp without time zone AT TIME ZONE zone → timestamp with time zone Converts given time stamp without time zone to time stamp with time zone, assuming the given value is in the named time zone. timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001-02-17 03:38:40+00 |
timestamp with time zone AT TIME ZONE zone → timestamp without time zone Converts given time stamp with time zone to time stamp without time zone, as the time would appear in that zone. timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver' → 2001-02-16 18:38:40 |
time with time zone AT TIME ZONE zone → time with time zone Converts given time with time zone to a new time zone. Since no date is supplied, this uses the currently active UTC offset for the named destination zone. time with time zone '05:34:17-05' at time zone 'UTC' → 10:34:17+00 |
在这些表达式中，所需的时区 zone 可以指定为文本值（例如 'America/Los_Angeles'）或间隔（例如 INTERVAL '-08:00'）。在文本情况下，可以按照 Section 8.5.3 中描述的任何方式指定时区名称。间隔情况仅适用于与 UTC 固定的偏移量的时区，因此实际上并不常见。
In these expressions, the desired time zone zone can be specified either as a text value (e.g., 'America/Los_Angeles') or as an interval (e.g., INTERVAL '-08:00'). In the text case, a time zone name can be specified in any of the ways described in Section 8.5.3. The interval case is only useful for zones that have fixed offsets from UTC, so it is not very common in practice.
示例（假设当前 TimeZone 设置为 America/Los_Angeles）：
Examples (assuming the current TimeZone setting is America/Los_Angeles):
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 18:38:40
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Result: 2001-02-16 05:38:40第一个示例将时区添加到一个不包含时区的数值，并使用当前 TimeZone 设置显示该数值。第二个示例将带有时区值的时间戳移至指定时区，并返回不包含时区的数值。这样，便能够存储和显示不同于当前 TimeZone 设置的数值。第三个示例将东京时间转换为芝加哥时间。
The first example adds a time zone to a value that lacks it, and displays the value using the current TimeZone setting. The second example shifts the time stamp with time zone value to the specified time zone, and returns the value without a time zone. This allows storage and display of values different from the current TimeZone setting. The third example converts Tokyo time to Chicago time.
timezone ( zone , timestamp )函数等同于符合 SQL 的结构 _timestamp AT TIME ZONE zone _.
The function timezone(zone, timestamp)_ is equivalent to the SQL-conforming construct timestamp_ AT TIME ZONE zone.
9.9.5. Current Date/Time #
PostgreSQL 提供了许多返回与当前日期和时间相关的值的函数。所有这些 SQL 标准函数基于当前事务的开始时间返回值：
PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction:
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)CURRENT_TIME 和 CURRENT_TIMESTAMP 提供带有时区的值；LOCALTIME 和 LOCALTIMESTAMP 提供不带时区的值。
CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.
CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME 和 LOCALTIMESTAMP 可选地采用精度参数，这样结果将在秒字段中舍入为很多有效数字。如果未采用精度参数，则将结果舍入为完全可用的精度。
CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.
举例：
Some examples:
SELECT CURRENT_TIME;
Result: 14:39:53.662522-05
SELECT CURRENT_DATE;
Result: 2019-12-23
SELECT CURRENT_TIMESTAMP;
Result: 2019-12-23 14:39:53.662522-05
SELECT CURRENT_TIMESTAMP(2);
Result: 2019-12-23 14:39:53.66-05
SELECT LOCALTIMESTAMP;
Result: 2019-12-23 14:39:53.662522由于这些函数返回当前事务的开始时间，因此它们的值在事务期间不会发生变化。这被认为是一个特征：目的是允许单一事务对“当前”时间有一个一致的概念，以便在同一事务中的多个修改带有相同时间戳。
Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.
Note
其他数据库系统可能会更频繁地推动这些值。
Other database systems might advance these values more frequently.
PostgreSQL 还提供返回当前语句的开始时间以及函数调用瞬间实际的当前时间的函数。非 SQL 标准时间函数的完整列表如下：
PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:
transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()transaction_timestamp() 等同于 CURRENT_TIMESTAMP，但其名称清楚地反映了其返回内容。statement_timestamp() 返回当前语句的开始时间（更具体地说，是从客户端接收的最新命令消息的时间）。statement_timestamp() 和 transaction_timestamp() 在事务的第一条命令期间返回相同的值，但在随后的命令期间可能会有所不同。clock_timestamp() 返回实际的当前时间，因此即使在单个 SQL 命令内其值也会发生变化。timeofday() 是 PostgreSQL 的一个历史函数。它像 clock_timestamp() 一样返回实际的当前时间，但以格式化的 text 字符串而非 timestamp with time zone 值返回。now() 是 transaction_timestamp() 的传统 PostgreSQL 等效函数。
transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands. clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command. timeofday() is a historical PostgreSQL function. Like clock_timestamp(), it returns the actual current time, but as a formatted text string rather than a timestamp with time zone value. now() is a traditional PostgreSQL equivalent to transaction_timestamp().
所有日期/时间数据类型还接受特殊文字值 now，以指定当前日期和时间（同样，解释为事务开始时间）。因此，以下三个结果相同：
All the date/time data types also accept the special literal value now to specify the current date and time (again, interpreted as the transaction start time). Thus, the following three all return the same result:
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now'; -- but see tip belowTip
在指定待稍后评估的值（例如表列的 DEFAULT 子句）时，不要使用第三种形式。系统将在解析常量后立即将 now 转换为 timestamp，以便在需要默认值时，将使用创建表的时间！前两种形式不会在使用默认值之前进行评估，因为它们是函数调用。因此，它们会给出按行的插入时间进行默认操作的所需行为。（另请参阅 Section 8.5.1.4。）
Do not use the third form when specifying a value to be evaluated later, for example in a DEFAULT clause for a table column. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used! The first two forms will not be evaluated until the default value is used, because they are function calls. Thus they will give the desired behavior of defaulting to the time of row insertion. (See also Section 8.5.1.4.)
9.9.6. Delaying Execution #
以下函数可用于延迟服务器进程的执行：
The following functions are available to delay execution of the server process:
pg_sleep ( double precision )
pg_sleep_for ( interval )
pg_sleep_until ( timestamp with time zone )pg_sleep 使当前会话的进程休眠，直到给定的秒数过去。可以指定几分之一秒的延迟。pg_sleep_for 是一个便捷函数，允许睡眠时间指定为 interval。pg_sleep_until 是一个便捷函数，当需要一个特定的唤醒时间时。例如：
pg_sleep makes the current session’s process sleep until the given number of seconds have elapsed. Fractional-second delays can be specified. pg_sleep_for is a convenience function to allow the sleep time to be specified as an interval. pg_sleep_until is a convenience function for when a specific wake-up time is desired. For example:
SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');Note
睡眠间隔的有效分辨率取决于平台；0.01 秒是一个常见值。睡眠延迟至少与指定的时间长度一样。它可能会更长，具体取决于服务器负载等因素。特别是，pg_sleep_until 不能保证在指定的时间精确唤醒，但它不会更早唤醒。
The effective resolution of the sleep interval is platform-specific; 0.01 seconds is a common value. The sleep delay will be at least as long as specified. It might be longer depending on factors such as server load. In particular, pg_sleep_until is not guaranteed to wake up exactly at the specified time, but it will not wake up any earlier.