How to use date and time functions and operators-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL supports the time and date functions and operators from PostgreSQL. For the full PostgreSQL reference, see Date/Time Functions and Operators.

Date and time functions

Functions are grouped by purpose. Within each group, functions are listed alphabetically.

Current time

These functions return the current date or time. Their behavior differs depending on whether they are called inside a transaction.

Functions that return the transaction start time (current_timestamp, now(), transaction_timestamp()) return the same value throughout a transaction — repeated calls within the same transaction return identical results. Functions that reflect actual wall-clock time (clock_timestamp(), statement_timestamp()) always return the current time, regardless of transaction context.

Function	Return type	Description	Example	Result
`clock_timestamp()`	`timestamp with time zone`	Returns the current wall-clock time, updated on every call.	`clock_timestamp()`	`2024-07-01 20:00:00`
`current_date`	`date`	Returns the current date.	`current_date`	`2024-07-01`
`current_time`	`time with time zone`	Returns the current time.	`current_time`	`20:00:00+00`
`current_timestamp`	`timestamp with time zone`	Returns the timestamp when the current transaction started.	`current_timestamp`	`2024-07-01 20:00:00+00`
`localtime`	`time`	Returns the current local time of the AnalyticDB for PostgreSQL instance.	`localtime`	`08:15:23`
`localtimestamp`	`timestamp`	Returns the current local timestamp of the AnalyticDB for PostgreSQL instance, with second or millisecond precision.	`localtimestamp`	`2024-07-01 08:15:23`
`now()`	`timestamp with time zone`	Returns the timestamp when the current transaction started. Equivalent to `current_timestamp`.	`now()`	`2024-07-01 08:15:23.5+01`
`statement_timestamp()`	`timestamp with time zone`	Returns the wall-clock time when the current statement started executing.	`statement_timestamp()`	`2024-07-01 08:15:23.5+01`
`timeofday()`	`text`	Returns the current wall-clock time as a text string, including hours, minutes, and seconds.	`timeofday()`	`2024-07-01 08:15:23.5+01`
`transaction_timestamp()`	`timestamp with time zone`	Returns the timestamp when the current transaction started. Equivalent to `current_timestamp`.	`transaction_timestamp()`	`2024-07-01 08:15:23.5+01`

Construction

These functions build a date, time, or timestamp from individual field values.

Function	Return type	Description	Example	Result
`make_date(year, month, day)`	`date`	Creates a date from year, month, and day fields.	`make_date(2013, 7, 15)`	`2013-07-15`
`make_interval([years] [, months] [, weeks] [, days] [, hours] [, mins] [, secs])`	`interval`	Creates an interval from the specified fields. All fields default to `0`.	`make_interval(days => 10)`	`10 days`
`make_time(hour, min, sec)`	`time`	Creates a time from hour, minute, and second fields. The second field accepts decimal fractions.	`make_time(8, 15, 23.5)`	`08:15:23.5`
`make_timestamp(year, month, day, hour, min, sec)`	`timestamp`	Creates a timestamp from year, month, day, hour, minute, and second fields. The second field accepts decimal fractions.	`make_timestamp(2013, 7, 15, 8, 15, 23.5)`	`2013-07-15 08:15:23.5`
`make_timestamptz(year, month, day, hour, min, sec [, timezone])`	`timestamp with time zone`	Creates a timestamp with time zone. If no time zone is specified, the current session time zone is used. The second field accepts decimal fractions.	`make_timestamptz(2013, 7, 15, 8, 15, 23.5)`	`2013-07-15 08:15:23.5+01`
`to_timestamp(double precision)`	`timestamp with time zone`	Converts a Unix epoch value (seconds elapsed since 1970-01-01 00:00:00 UTC) to a timestamp.	`to_timestamp(1284352323)`	`2010-09-13 04:32:03+00`

Extraction

These functions extract a numeric subfield (such as year, month, or hour) from a date, time, or timestamp. date_part() and extract serve the same purpose; extract uses SQL-standard syntax.

Extraction returns a number. This differs from truncation, which returns a complete timestamp or interval anchored to a precision boundary. For example, extract(quarter from timestamp '2001-02-16 20:38:40') returns 1 (the quarter number), while date_trunc('quarter', timestamp '2001-02-16 20:38:40') returns 2001-01-01 00:00:00 (the start of the quarter).

The following subfields are valid for extraction functions:

Subfield	Aliases	Applies to	Returns
`century`		timestamp, interval	The century number
`day`		timestamp, interval	The day of the month (1–31) for timestamps; the number of days in the interval
`decade`		timestamp	The year divided by 10
`dow`		timestamp	The day of the week (0=Sunday, 6=Saturday)
`doy`		timestamp	The day of the year (1–365/366)
`epoch`		timestamp, interval	Seconds since 1970-01-01 00:00:00 UTC for timestamps; total seconds for intervals
`hour`		timestamp, interval	The hour field (0–23)
`isodow`		timestamp	The day of the week (1=Monday, 7=Sunday)
`isoyear`		timestamp	The ISO 8601 week-numbering year
`microseconds`		timestamp, interval	The seconds field, including fractional parts, multiplied by 1,000,000
`millennium`		timestamp	The millennium number
`milliseconds`	`ms`	timestamp, interval	The seconds field, including fractional parts, multiplied by 1,000
`minute`		timestamp, interval	The minutes field (0–59)
`month`		timestamp, interval	The month number (1–12) for timestamps; the number of months (0–11) for intervals
`quarter`		timestamp	The quarter of the year (1–4)
`second`	`sec`	timestamp, interval	The seconds field, including fractional parts
`timezone`		timestamp with time zone	The time zone offset from UTC, in seconds
`timezone_hour`		timestamp with time zone	The hours component of the time zone offset
`timezone_minute`		timestamp with time zone	The minutes component of the time zone offset
`week`		timestamp	The ISO 8601 week number of the year
`year`		timestamp, interval	The year field

Function	Return type	Description	Example	Result
`date_part('subfield', timestamp)`	`double precision`	Extracts the value of a subfield from a timestamp.	`date_part('hour', timestamp '2001-02-16 20:38:40')`	`20`
`date_part('subfield', interval)`	`double precision`	Extracts the value of a subfield from an interval.	`date_part('month', interval '2 years 3 months')`	`3`
`extract(subfield from timestamp)`	`double precision`	Extracts the value of a subfield from a timestamp. SQL-standard equivalent of `date_part()`.	`extract(hour from timestamp '2001-02-16 20:38:40')`	`20`
`extract(subfield from interval)`	`double precision`	Extracts the value of a subfield from an interval. SQL-standard equivalent of `date_part()`.	`extract(month from interval '2 years 3 months')`	`3`

Truncation

Truncation sets all subfields below a given precision to zero (or the lowest valid value) and returns a complete timestamp or interval. For example, truncating to hour sets minutes and seconds to zero.

Truncation returns a complete timestamp or interval, not a number. This differs from extraction. For example, date_trunc('hour', timestamp '2001-02-16 20:38:40') returns 2001-02-16 20:00:00, while date_part('hour', timestamp '2001-02-16 20:38:40') returns 20.

Valid precision values for date_trunc: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium.

Function	Return type	Description	Example	Result
`date_trunc('precision', timestamp)`	`timestamp`	Truncates a timestamp to the specified precision.	`date_trunc('hour', timestamp '2001-02-16 20:38:40')`	`2001-02-16 20:00:00`
`date_trunc('precision', timestamptz, 'timezone')`	`timestamp with time zone`	Truncates a timestamp to the specified precision in the specified time zone.	`date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')`	`2001-02-16 13:00:00+00`
`date_trunc('precision', interval)`	`interval`	Truncates an interval to the specified precision.	`date_trunc('hour', interval '2 days 3 hours 40 minutes')`	`2 days 03:00:00`

Age and interval adjustment

Function	Return type	Description	Example	Result
`age(timestamp, timestamp)`	`interval`	Returns the interval between two timestamps, expressed in years, months, and days.	`age(timestamp '2001-04-10', timestamp '1957-06-13')`	`43 years 9 mons 27 days`
`age(timestamp)`	`interval`	Returns the interval between the current date and the specified timestamp.	`age(timestamp '1957-06-13')`	`43 years 8 mons 3 day`
`justify_days(interval)`	`interval`	Converts a day-based interval to months and days, treating 30 days as one month.	`justify_days(interval '35 days')`	`1 mon 5 days`
`justify_hours(interval)`	`interval`	Converts an hour-based interval to days and hours, treating 24 hours as one day.	`justify_hours(interval '27 hours')`	`1 day 03:00:00`
`justify_interval(interval)`	`interval`	Adjusts an interval using both `justify_days()` and `justify_hours()`.	`justify_interval(interval '1 mon -1 hour')`	`29 days 23:00:00`

Validation

Function	Return type	Description	Example	Result
`isfinite(date)`	`boolean`	Returns `true` if the date is finite (not infinity).	`isfinite(date '2001-02-16')`	`true`
`isfinite(timestamp)`	`boolean`	Returns `true` if the timestamp is finite (not infinity).	`isfinite(timestamp '2001-02-16 21:28:30')`	`true`
`isfinite(interval)`	`boolean`	Returns `true` if the interval is finite (not infinity).	`isfinite(interval '4 hours')`	`true`

Time and date operators

Operator	Example	Result
`+`	`date '2001-09-28' + integer '7'`	`date '2001-10-05'`
`+`	`date '2001-09-28' + interval '1 hour'`	`timestamp '2001-09-28 01:00:00'`
`+`	`date '2001-09-28' + time '03:00'`	`timestamp '2001-09-28 03:00:00'`
`+`	`interval '1 day' + interval '1 hour'`	`interval '1 day 01:00:00'`
`+`	`timestamp '2001-09-28 01:00' + interval '23 hours'`	`timestamp '2001-09-29 00:00:00'`
`+`	`time '01:00' + interval '3 hours'`	`time '04:00:00'`
`-`	`- interval '23 hours'`	`interval '-23:00:00'`
`-`	`date '2001-10-01' - date '2001-09-28'`	`integer '3'` (days)
`-`	`date '2001-10-01' - integer '7'`	`date '2001-09-24'`
`-`	`date '2001-09-28' - interval '1 hour'`	`timestamp '2001-09-27 23:00:00'`
`-`	`time '05:00' - time '03:00'`	`interval '02:00:00'`
`-`	`time '05:00' - interval '2 hours'`	`time '03:00:00'`
`-`	`timestamp '2001-09-28 23:00' - interval '23 hours'`	`timestamp '2001-09-28 00:00:00'`
`-`	`interval '1 day' - interval '1 hour'`	`interval '1 day -01:00:00'`
`-`	`timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'`	`interval '1 day 15:00:00'`
`*`	`900 * interval '1 second'`	`interval '00:15:00'`
`*`	`21 * interval '1 day'`	`interval '21 days'`
`*`	`double precision '3.5' * interval '1 hour'`	`interval '03:30:00'`
`/`	`interval '1 hour' / double precision '1.5'`	`interval '00:40:00'`