Movatterモバイル変換

9.9.1.EXTRACT,date_part

9.9.2.date_trunc

9.9.3.date_bin

9.9.4.AT TIME ZONE andAT LOCAL

9.9.5. Current Date/Time

9.9.6. Delaying Execution

Table 9.32. Date/Time Operators

Table 9.33. Date/Time Functions

EXTRACT(field FROMsource)

Theextract function retrieves subfields such as year or hour from date/time values.source must be a value expression of typetimestamp,date,time, orinterval. (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 adate, while fields of a day or more cannot be extracted from atime. Theextract function returns values of typenumeric.

The following are valid field names:

When processing aninterval value, theextract 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:00SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');Result:20

Theextract function is primarily intended for computational processing. For formatting date/time values for display, seeSection 9.8.

Thedate_part function is modeled on the traditionalIngres equivalent to theSQL-standard functionextract:

date_part('field',source)

Note that here thefield parameter needs to be a string value, not a name. The valid field names fordate_part are the same as forextract. For historical reasons, thedate_part function returns values of typedouble precision. This can result in a loss of precision in certain uses. Usingextract is recommended instead.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');Result:16SELECT date_part('hour', INTERVAL '4 hours 3 minutes');Result:4

The functiondate_trunc is conceptually similar to thetrunc function for numbers.

date_trunc(field,source [,time_zone])

source is a value expression of typetimestamp,timestamp with time zone, orinterval. (Values of typedate andtime are cast automatically totimestamp orinterval, respectively.)field selects to which precision to truncate the input value. The return value is likewise of typetimestamp,timestamp with time zone, orinterval, and it has all fields that are less significant than the selected one set to zero (or one, for day and month).

Valid values forfield are:

When the input value is of typetimestamp with time zone, the truncation is performed with respect to a particular time zone; for example, truncation today produces a value that is midnight in that zone. By default, truncation is done with respect to the currentTimeZone setting, but the optionaltime_zone argument can be provided to specify a different time zone. The time zone name can be specified in any of the ways described inSection 8.5.3.

A time zone cannot be specified when processingtimestamp without time zone orinterval inputs. These are always taken at face value.

Examples (assuming the local time zone isAmerica/New_York):

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');Result:2001-02-16 20:00:00SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');Result:2001-01-01 00:00:00SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');Result:2001-02-16 00:00:00-05SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');Result:2001-02-16 08:00:00-05SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');Result:3 days 02:00:00

The functiondate_bin“bins” the input timestamp into the specified interval (thestride) aligned with a specified origin.

date_bin(stride,source,origin)

source is a value expression of typetimestamp ortimestamp with time zone. (Values of typedate are cast automatically totimestamp.)stride is a value expression of typeinterval. The return value is likewise of typetimestamp ortimestamp with time zone, and it marks the beginning of the bin into which thesource 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:00SELECT 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

In the case of full units (1 minute, 1 hour, etc.), it gives the same result as the analogousdate_trunc call, but the difference is thatdate_bin can truncate to an arbitrary interval.

Thestride interval must be greater than zero and cannot contain units of month or larger.

TheAT TIME ZONE operator converts time stampwithout time zone to/from time stampwith time zone, andtime with time zone values to different time zones.Table 9.34 shows its variants.

In these expressions, the desired time zonezone 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 inSection 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.

The syntaxAT LOCAL may be used as shorthand forAT TIME ZONElocal, wherelocal is the session'sTimeZone value.

Examples (assuming the currentTimeZone setting isAmerica/Los_Angeles):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';Result:2001-02-16 19:38:40-08SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';Result:2001-02-16 18:38:40SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';Result:2001-02-16 05:38:40SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;Result:2001-02-16 17:38:40SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '+05';Result:2001-02-16 20:38:40SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;Result:17:38:40

The first example adds a time zone to a value that lacks it, and displays the value using the currentTimeZone 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 currentTimeZone setting. The third example converts Tokyo time to Chicago time. The fourth example shifts the time stamp with time zone value to the time zone currently specified by theTimeZone setting and returns the value without a time zone. The fifth example demonstrates that the sign in a POSIX-style time zone specification has the opposite meaning of the sign in an ISO-8601 datetime literal, as described inSection 8.5.3 andAppendix B.

The sixth example is a cautionary tale. Due to the fact that there is no date associated with the input value, the conversion is made using the current date of the session. Therefore, this static example may show a wrong result depending on the time of the year it is viewed because'America/Los_Angeles' observes Daylight Savings Time.

The functiontimezone(zone,timestamp) is equivalent to the SQL-conforming constructtimestamp AT TIME ZONEzone.

The functiontimezone(zone,time) is equivalent to the SQL-conforming constructtime AT TIME ZONEzone.

The functiontimezone(timestamp) is equivalent to the SQL-conforming constructtimestamp AT LOCAL.

The functiontimezone(time) is equivalent to the SQL-conforming constructtime AT LOCAL.

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_DATECURRENT_TIMECURRENT_TIMESTAMPCURRENT_TIME(precision)CURRENT_TIMESTAMP(precision)LOCALTIMELOCALTIMESTAMPLOCALTIME(precision)LOCALTIMESTAMP(precision)

CURRENT_TIME andCURRENT_TIMESTAMP deliver values with time zone;LOCALTIME andLOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME,CURRENT_TIMESTAMP,LOCALTIME, andLOCALTIMESTAMP 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-05SELECT CURRENT_DATE;Result:2019-12-23SELECT CURRENT_TIMESTAMP;Result:2019-12-23 14:39:53.662522-05SELECT CURRENT_TIMESTAMP(2);Result:2019-12-23 14:39:53.66-05SELECT 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.

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() is equivalent toCURRENT_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() andtransaction_timestamp() return the same value during the first statement of a transaction, but might differ during subsequent statements.clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL statement.timeofday() is a historicalPostgreSQL function. Likeclock_timestamp(), it returns the actual current time, but as a formattedtext string rather than atimestamp with time zone value.now() is a traditionalPostgreSQL equivalent totransaction_timestamp().

All the date/time data types also accept the special literal valuenow 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 below

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 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 aninterval.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');