9.9. Date/Time Functions and Operators

9.9.1. EXTRACT, date_part
9.9.2. date_trunc
9.9.3. date_bin
9.9.4. AT TIME ZONE
9.9.5. Current Date/Time
9.9.6. Delaying Execution

+9.9. Date/Time Functions and Operators

9.9. Date/Time Functions and Operators
Prev	Up	Chapter 9. Functions and Operators	Home	Next

9.9. Date/Time Functions and Operators

9.9.1. EXTRACT, date_part
9.9.2. date_trunc
9.9.3. date_bin
9.9.4. AT TIME ZONE
9.9.5. Current Date/Time
9.9.6. Delaying Execution

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 @@ -183,7 +183,7 @@

Subtract timestamps (converting 24-hour intervals into days, - similarly to justify_hours()) + similarly to justify_hours())

timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' @@ -433,27 +433,27 @@ isfinite(interval '4 hours') → true

- + justify_days ( interval ) → interval

- Adjust interval so 30-day time periods are represented as months + Adjust interval, converting 30-day time periods to months

- justify_days(interval '35 days') - → 1 mon 5 days + justify_days(interval '1 year 65 days') + → 1 year 2 mons 5 days

- + justify_hours ( interval ) → interval

- Adjust interval so 24-hour time periods are represented as days + Adjust interval, converting 24-hour time periods to days

- justify_hours(interval '27 hours') - → 1 day 03:00:00 + justify_hours(interval '50 hours 10 minutes') + → 2 days 02:10:00

justify_interval ( interval ) @@ -758,38 +758,40 @@ EXTRACT(field FROM extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of - type timestamp, time, or interval. - (Expressions of type date are - cast to timestamp and can therefore be used as - well.) field is an identifier or + 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 + 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
-

- The first century starts at 0001-01-01 00:00:00 AD, although - they did not know it at the time. This definition applies to all - Gregorian calendar countries. There is no century number 0, - you go from -1 century to 1 century. - - If you disagree with this, please write your complaint to: - Pope, Cathedral Saint-Peter of Roma, Vatican. -

day

- For timestamp values, the day (of the month) field - (1–31) ; for interval values, the number of days +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

@@ -824,10 +826,8 @@ SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');

 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

@@ -843,7 +843,8 @@ SELECT to_timestamp(982384720.12); assume that the original value had been given in UTC, which might not be the case.

hour

- The hour field (0–23) + The hour field (0–23 in timestamps, unrestricted in + intervals)

 SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
 Result: 20
@@ -858,7 +859,7 @@ SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
         matches the ISO 8601 day of the week numbering.

isoyear

The ISO 8601 week-numbering year that the date - falls in (not applicable to intervals) + falls in

 SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
 Result: 2005
@@ -870,11 +871,9 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
         January or late December the ISO year may be
         different from the Gregorian year.  See the week
         field for more information.
-       

-        This field is not available in PostgreSQL releases prior to 8.3.

julian

The Julian Date corresponding to the - date or timestamp (not applicable to intervals). Timestamps + date or timestamp. Timestamps that are not local midnight result in a fractional value. See Section B.7 for more information.

@@ -889,10 +888,13 @@ SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
 SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
 Result: 28500000

millennium

- The 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. @@ -908,16 +910,14 @@ SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 38

month

- For timestamp values, the number of the month - within the year (1–12) ; for interval values, - the number of months, modulo 12 (0–11) + 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

@@ -930,7 +930,6 @@ SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');

 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

@@ -967,6 +966,18 @@ SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 2001

+ 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: 20
+

Note

When the input value is +/-Infinity, extract returns +/-Infinity for monotonically-increasing fields (epoch, @@ -996,7 +1007,6 @@ date_part('field', SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); Result: 16 - SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); Result: 4

9.9.2. `date_trunc`

@@ -1039,16 +1049,12 @@ date_trunc(field, 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:00
@@ -1073,7 +1079,6 @@ date_bin(stride, 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
@@ -1149,10 +1154,8 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-0
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
@@ -1203,16 +1206,12 @@ LOCALTIMESTAMP(precision)
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
@@ -1313,4 +1312,4 @@ SELECT pg_sleep_until('tomorrow 03:00'); when calling pg_sleep or its variants. Otherwise other sessions might have to wait for your sleeping process, slowing down the entire system. -

Prev	Up	Next
9.8. Data Type Formatting Functions	Home	9.10. Enum Support Functions

9.9. Date/Time Functions and Operators

9.9. Date/Time Functions and Operators

Note

9.9.2. date_trunc

9.9.2. `date_trunc`