The following functions apply to the temporal data types such as: DATE, TIME, TIMESTAMP,TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE,INTERVAL SECOND, INTERVAL DAY (in unit of seconds), and INTERVAL MONTH.
| Literal expression | Description | Example query |
|---|---|---|
DATE 'YYYY-MM-DD' | Converts string value in YYYY-MM-DD format to a date data type. | SELECT DATE '2020-09-28'; |
{d 'YYYY-MM-DD'} | Converts string value in YYYY-MM-DD format to a date data type using ODBC escape sequence notation. | SELECT {d '2020-09-28'}; |
TIME(n) 'HH:MI:SS.ssssss' | Converts string value in HH:MI:SS.ssssss format to a time data type with up to microsecond precision (i.e. 0 ≤ n ≤ 6). | SELECT TIME(6) '18:40:05.123456'; |
{t 'HH:MI:SS.ssssss'} | Converts string value in HH:MI:SS.ssssss format to a time data type using ODBC escape sequence notation. | SELECT {t '18:40:05.123456'}; |
TIME(n) WITH TIME ZONE 'HH:MI:SS.ssssss+HH:MI' | Converts string value in HH:MI:SS.ssssss+HH:MI format to a timetz (i.e. time with time zone) data type with up to microsecond precision (i.e. 0 ≤ n ≤ 6). | SELECT TIME(3) WITH TIME ZONE '18:40:05.123+02:00'; |
TIMESTAMP(n) 'YYYY-MM-DD HH:MI:SS.ssssss' | Converts string value in YYYY-MM-DD HH:MI:SS.ssssss format to a timestamp data type with up to microsecond precision (i.e. 0 ≤ n ≤ 6). | SELECT TIMESTAMP(6) '2020-09-28 18:40:05.123456'; |
{ts 'YYYY-MM-DD HH:MI:SS.ssssss'} | Converts string value in YYYY-MM-DD HH:MI:SS.ssssss format to a timestamp data type using ODBC escape sequence notation. | SELECT {ts '2020-09-28 18:40:05.123456'}; |
TIMESTAMP(n) WITH TIME ZONE 'YYYY-MM-DD HH:MI:SS.ssssss+HH:MI' | Converts string value in YYYY-MM-DD HH:MI:SS.ssssss+HH:MI format to a timestamptz (i.e. timestamp with time zone) data type with up to microsecond precision (i.e. 0 ≤ 0 ≤ n ≤ 6). | SELECT TIMESTAMP(3) WITH TIME ZONE '2020-09-28 18:40:05.123+02:00'; |
INTERVAL 'SS' SECOND | Converts number value string SS to the sec_interval data type. | SELECT INTERVAL '67205' SECOND; |
INTERVAL 'DD' DAY | Converts number value string DD to the day_interval data type. | SELECT INTERVAL '30' DAY; |
INTERVAL 'MM' MONTH | Converts number value string MM to the month_interval data type. | SELECT INTERVAL '6' MONTH; |
INTERVAL 'HH:MI:SS' HOUR TO SECOND; | Converts string value in HH:MI:SS format to sec_interval data type. | SELECT INTERVAL '18:40:05' HOUR TO SECOND; |
INTERVAL 'DD HH:MI:SS' DAY TO SECOND; | Converts string value in DD HH:MI:SS format to sec_interval data type. | SELECT INTERVAL '2 18:40:05' DAY TO SECOND; |
INTERVAL 'YYYY-MM' YEAR TO MONTH | Converts string value in YYYY-MM format to month_interval data type. | SELECT INTERVAL '2020-09' YEAR TO MONTH; |
{interval 'SS' second} | Converts number value string SS to the sec_interval data type using ODBC escape sequence notation. | SELECT {interval '67205' second}; |
{interval 'DD' day} | Converts number value string DD to the day_interval data type using ODBC escape sequence notation. | SELECT {interval '30' day}; |
{interval 'MM' month} | Converts number value string MM to the month_interval data type using ODBC escape sequence notation. | SELECT {interval '6' month}; |
{interval 'HH:MI:SS' hour to second} | Converts string value in HH:MI:SS format to the sec_interval data type using ODBC escape sequence notation. | SELECT {interval '18:40:05' hour to second}; |
{interval 'DD HH:MI:SS' day to second} | Converts string value in DD HH:MI:SS format to the sec_interval data type using ODBC escape sequence notation. | SELECT {interval '2 18:40:05' day to second}; |
{interval 'YYYY-MM' year to month} | Converts string value in YYYY-MM format to the month_interval data type using ODBC escape sequence notation. | SELECT {interval '2020-09' year to month}; |
Note: the arithmetic for dates, times and timestamps is based on intervals, not on numbers.
So you must use the interval '<number>' <interval type> syntax to represent the number and interval unit.
Tip If the number is not static but a column expression, use interval '1' <interval type> * <col_expr> syntax, as done in examples below.
| Operator | Description | Example query | Return type | Result |
|---|---|---|---|---|
| + | Adds days to a date, timestamp, or timestamptz value or column. | SELECT DATE '2020-09-28' + INTERVAL '1' DAY * 7; | date | 2020-10-05 |
| + | Adds months to a date, timestamp, or timestamptz value or column. | SELECT DATE '2020-09-28' + INTERVAL '-3' MONTH; | date | 2020-06-28 |
| + | Adds seconds to a date, time, timetz, timestamp, or timestamptz value or column. | SELECT DATE '2020-09-28' + INTERVAL '1' SECOND * (60*60*24 * 7); | date | 2020-10-05 |
| + | Adds hours to a date, time, timetz, timestamp, or timestamptz value or column. | SELECT TIMESTAMP '2020-09-28 15:35:50' + INTERVAL '50:00' HOUR TO MINUTE; | timestamp | 2020-09-30 17:35:50.000000 |
| - | Subtracts seconds from a date, time, timetz, timestamp, or timestamptz value or column. | SELECT TIME '15:35:50' - 30 * INTERVAL '1.1' SECOND; | time | 15:35:17 |
| - | Subtracts interval hour to second from a date, time, timetz, timestamp, or timestamptz value or column. | SELECT TIME '15:35:50' - INTERVAL '10:30:00' HOUR TO SECOND; | time | 05:05:50 |
| - | Subtracts years and/or months from a date, timestamp, or timestamptz value or column. | SELECT DATE '2026-08-28' - INTERVAL '1-6' YEAR TO MONTH; | date | 2025-02-28 |
| - | Subtracts two date values or columns. | SELECT (DATE '2020-03-28' - DATE '2020-03-08') / (24*60*60); | interval day | 20 |
| - | Subtracts two time, timestamp, or timestamptz values or columns. | SELECT TIME WITH TIME ZONE '14:35:45+02:00' - TIME '02:12:24'; | interval second | 44601.000 |
Warning the interval day values are represented as number of seconds instead of number of days.
So you need to divide it by 86400 (which is the number of seconds for 1 day: 24 * 60 * 60) to convert it into days.
| Function | Return type | Description | Example query | Result |
|---|---|---|---|---|
century(value) | int | Extracts the century from a date, timestamp, or timestamptz value. | SELECT century(DATE '2020-03-22'); | 21 |
curdate() | date | Retrieves current date in YYYY-MM-DD format. | SELECT curdate(); | 2026-06-04 |
current_date() | date | Retrieves current date in YYYY-MM-DD format. | SELECT current_date(); | 2026-06-04 |
current_time() | timetz | Retrieves current time (with time zone) in HH:MI:SS.ssssss+HH:MI format. | SELECT current_time(); | 17:02:18.329951+02:00 |
current_timestamp() | timestamptz | Retrieves current timestamp (with time zone) in YYYY-MM-DD HH:MI:SS.ssssss+HH:MI format. | SELECT current_timestamp(); | 2026-06-04 17:02:18.329951+02:00 |
curtime() | timetz | Retrieves current time (with time zone) in HH:MI:SS.ssssss+HH:MI format. | SELECT curtime(); | 17:02:18.329951+02:00 |
date_to_str(value, format_str) | varchar | Converts a date, timestamp, or timestamptz value to a string value according to a specified string format. | SELECT date_to_str(DATE '2020-09-28', '%D %d.%m.%Y'); | "09/28/20 28.09.2020" |
date_trunc(field_str, value) | timestamp or timestamptz | Truncates a date, timestamp, or timestamptz value to granularity specified by field_str : 'millennium', 'century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'milliseconds', 'microseconds'. | SELECT date_trunc('month', TIMESTAMP '2020-03-22 13:16:57.734639'); | 2020-03-01 00:00:00.000000 |
"day"(value) | tinyint | Extracts the day of the month (range: 1 to 31) from a date, timestamp, or timestamptz value. Same as DAYOFMONTH | SELECT "day"(DATE '2020-03-22'); | 22 |
"day"(sec_interval) | bigint | Converts a sec_interval value to number of days and truncates it to an integer. | SELECT "day"( INTERVAL '3.23' SECOND * (24 * 60 * 60)); | 3 |
dayname(value) | varchar | Extracts name of the day of the week from a date, timestamp, or timestamptz value. | SELECT dayname(DATE '2020-03-22'); | "Sunday" |
dayofmonth(value) | tinyint | Extracts day of the month (range: 1 to 31) from a date, timestamp, or timestamptz value. Same as "day"(value) | SELECT dayofmonth(DATE '2020-03-22'); | 22 |
dayofweek(value) | tinyint | Extracts day of the week (range: 1 to 7) from a date, timestamp, or timestamptz value. | SELECT dayofweek(DATE '2020-03-22'); | 7 |
dayofyear(value) | smallint | Extracts day of the year (range: 1 to 366) from a date, timestamp, or timestamptz value. | SELECT dayofyear(DATE '2020-03-22'); | 82 |
decade(value) | int | Extracts the decade from a date, timestamp, or timestamptz value. | SELECT decade(DATE '2027-03-22'); | 202 |
epoch(seconds) | timestamptz | Converts Unix time as a int or decimal(18,3) value to a timestamptz value. | SELECT epoch(1234567890.123); | 2009-02-14 01:31:30.123000+02:00 |
epoch(value) | int | Converts a date, timestamp, or timestamptz value to Unix time. | SELECT epoch( TIMESTAMP '2009-02-14 01:31:30'); | 1234567890 seconds |
epoch_ms(value) | decimal(18,3) | Converts a date, time, timetz, timestamp, timestamptz, or interval value to Unix time with millisecond precision. | SELECT epoch_ms( TIMESTAMP '2009-02-14 01:31:30.123'); | 1234567890.123 seconds |
extract(field FROM value) | tinyint, smallint, int, or decimal(9,6) | Extracts a specific field from a date, time, timetz, timestamp, timestamptz, or interval value. Supported field identifiers are: CENTURY, DECADE, YEAR, QUARTER, MONTH, WEEK, DAY, DOW, DOY, HOUR, MINUTE, and SECOND. | SELECT extract( DOY FROM DATE '2027-03-22'); | 81 |
greatest(value_1, value_2, ...) | Same as input | Compares two or more date or time or timestamp or interval values, returns the largest value, excluding nulls. | SELECT greatest(DATE '2020-03-18', DATE '2020-03-22', DATE '2020-03-25'); | 2020-03-25 |
"hour"(value) | tinyint | Extracts the hour (range: 0 to 23) from a time, timetz, timestamp, timestamptz, or sec_interval value. | SELECT "hour"( TIMETZ '15:35:02.002345+01:00'); | 16 |
least(value_1, value_2, ...) | Same as input | Compares two or more date or time or timestamp or interval values, returns the smallest value, excluding nulls. | SELECT least(TIME '15:15:15', TIME '16:16:16', TIME '17:17:17'); | 15:15:15 |
localtime() | time | Retrieves current time in HH:MI:SS.ssssss format. | SELECT localtime(); | 17:02:18.329951 |
localtimestamp() | timestamp | Retrieves current timestamp in YYYY-MM-DD HH:MI:SS.ssssss format. | SELECT localtimestamp(); | 2026-06-04 17:02:18.329951 |
local_timezone() | sec_interval | Retrieves local current time zone as an interval in seconds (with millisecond precision). | SELECT local_timezone(); | 7200.000 |
"minute"(value) | tinyint | Extracts the minutes (range: 0 to 59) from a time, timetz, timestamp, timestamptz, or sec_interval value. | SELECT "minute"( TIMETZ '15:35:02.002345+01:00'); | 35 |
"month"(value) | tinyint | Extracts the month index (range: 1 to 12) from a date, timestamp, timestamptz, or month_interval value. | SELECT "month"(DATE '2020-07-22'); | 7 |
monthname(value) | varchar | Extracts the month name from a date, timestamp, or timestamptz value. | SELECT monthname(DATE '2020-03-22'); | "March" |
now() | timestamptz | Retrieves current timestamp (with time zone) in YYYY-MM-DD HH:MI:SS.ssssss+HH:MI format. | SELECT now(); | 2026-06-04 17:02:18.329951+02:00 |
quarter(value) | tinyint | Extracts the fiscal quarter (range: 1 to 4) from a date, timestamp, or timestamptz value. | SELECT quarter(DATE '2020-07-22'); | 3 |
"second"(sec_interval) | int | Returns the remainder of seconds (range: 0 to 59) after converting a sec_interval value into minutes. | SELECT "second"(INTERVAL '140' SECOND); | 20 |
"second"(value) | decimal(9,6) | Extracts the seconds (Range: 0.000000 to 59.999999) from a time, timetz, timestamp, or timestamptz value. | SELECT "second"( TIMETZ '15:35:02.002345+01:00'); | 2.002345 |
str_to_date( str_value, format_str) | date | Parses a string value using a format string specifier and constructs a date value. | SELECT str_to_date( '4/30/2021', '%m/%d/%Y'); | 2021-04-30 |
str_to_time( str_value, format_str) | timetz | Parses a string value using a format string specifier and constructs a timetz value. | SELECT str_to_time( '09:30:49', '%H:%M:%S'); | 09:30:49+02:00 |
str_to_timestamp( str_value, format_str) | timestamptz | Parses a string value using a format string specifier and constructs a timestamptz value. | SELECT str_to_timestamp( '4/30/2021 09:30:49', '%m/%d/%Y %H:%M:%S'); | 2021-04-30 09:30:49.000000+02:00 |
time_to_str( value, format_str) | varchar | Converts a time or timetz value to a string value according to a specified string format. | SELECT time_to_str(TIMETZ '19:30:49', '%l hour %M:%S %p'); | " 7 hour 30:49 PM" |
timestamp_to_str( value, format_str) | varchar | Converts a timestamp or timestamptz value to a string value according to a specified string format. | SELECT timestamp_to_str( TIMESTAMPTZ '2021-12-31 18:00:00', '%m/%d/%Y %l:%M:%S %p'); | "12/31/2021 6:00:00 PM" |
timestampadd( value, interval) | timestamp or timestamptz | Returns addition of sec_interval, day_interval, or month_interval to a date, time, timetz, timestamp, or timestamptz value. | SELECT timestampadd( TIMESTAMP '2021-12-31 18:00:00', INTERVAL '2' MONTH); | 2022-02-28 18:00:00.000000 |
timestampdiff( value_1, value_2) | sec_interval | Returns the difference in number of seconds (as an interval value) between two date, timestamp, or timestamptz values. | SELECT timestampdiff( TIMESTAMP '2021-12-31 18:40:40', TIMESTAMP '2021-12-30 16:30:20'); | 94220.000 seconds |
timestampdiff_day( value_1, value_2) | int | Returns the difference in number of days between two date, timestamp, or timestamptz values. | SELECT timestampdiff_day( TIMESTAMP '2021-12-31 18:40:40', TIMESTAMP '2021-12-20 16:30:20'); | 11 |
timestampdiff_hour( value_1, value_2) | bigint | Returns the difference in number of hours between two date, timestamp, or timestamptz values. | SELECT timestampdiff_hour( TIMESTAMP '2021-12-31 18:40:40', TIMESTAMP '2021-12-20 16:30:20'); | 266 |
timestampdiff_min( value_1, value_2) | bigint | Returns the difference in number of minutes between two date, timestamp, or timestamptz values. | SELECT timestampdiff_min( TIMESTAMP '2021-12-31 18:40:40', TIMESTAMP '2021-12-31 16:30:20'); | 130 |
timestampdiff_month( value_1, value_2) | int | Returns the difference in number of months between two date, timestamp, or timestamptz values. | SELECT timestampdiff_month( TIMESTAMP '2021-12-31 18:40:40', TIMESTAMP '2021-02-20 16:30:20'); | 10 |
timestampdiff_quarter( value_1, value_2) | int | Returns the difference in number of fiscal quarters between two date, timestamp, or timestamptz values. | SELECT timestampdiff_quarter( TIMESTAMP '2021-12-31 18:40:40', TIMESTAMP '2021-02-20 16:30:20'); | 3 |
timestampdiff_sec( value_1, value_2) | bigint | Returns the difference in number of seconds (as an integer value) between two date, timestamp, or timestamptz values. | SELECT timestampdiff_sec( TIMESTAMP '2021-12-31 18:40:40', TIMESTAMP '2021-12-31 16:30:20'); | 7820 |
timestampdiff_week( value_1, value_2) | int | Returns the difference in number of weeks between two date, timestamp, or timestamptz values. | SELECT timestampdiff_week( TIMESTAMP '2021-12-31 18:40:40', TIMESTAMP '2021-02-20 16:30:20'); | 44 |
timestampdiff_year( value_1, value_2) | int | Returns the difference in number of years between two date, timestamp, or timestamptz values. | SELECT timestampdiff_year( TIMESTAMP '2021-12-31 18:40:40', TIMESTAMP '2024-02-20 16:30:20'); | -3 |
usweekofyear(value) | tinyint | Extracts the US week number (range: 1 to 53) from a date, timestamp, or timestamptz value. The US system has weeks from Sunday through Saturday, and partial weeks at the beginning and end of the year. | SELECT usweekofyear(DATE '2020-03-22'); | 13 |
week(value) | tinyint | Extracts the ISO 8601 week number (range: 1 to 53) from a date, timestamp, or timestamptz value. Same as WEEKOFYEAR. | SELECT week(DATE '2020-03-22'); | 12 |
weekofyear(value) | tinyint | Extracts the ISO 8601 week number (range: 1 to 53) from a date, timestamp, or timestamptz value. Same as WEEK. | SELECT weekofyear(DATE '2020-03-22'); | 12 |
"year"(value) | int | Extracts the year from a date, timestamp, timestamptz, or month_interval value. | SELECT "year"(DATE '2020-03-22'); | 2020 |
| Pseudo column | Return type | Description | Query | Result |
|---|---|---|---|---|
CURRENT_DATE | date | Retrieves current date in YYYY-MM-DD format. | SELECT CURRENT_DATE; | 2026-06-04 |
CURRENT_TIME | timetz | Retrieves current time (with time zone) in HH:MI:SS.ssssss+HH:MI format. | SELECT CURRENT_TIME; | 17:02:18.329951+02:00 |
CURRENT_TIMESTAMP | timestamptz | Retrieves current timestamp (with time zone) in YYYY-MM-DD HH:MI:SS.ssssss+HH:MI format. | SELECT CURRENT_TIMESTAMP; | 2026-06-04 17:02:18.329951+02:00 |
CURRENT_TIMEZONE | sec_interval | Retrieves current time zone as an interval in seconds (with millisecond precision). | SELECT CURRENT_TIMEZONE; | 7200.000 |
LOCALTIME | time | Retrieves current time in HH:MI:SS.ssssss format. | SELECT LOCALTIME; | 17:02:18.329951 |
LOCALTIMESTAMP | timestamp | Retrieves current timestamp in YYYY-MM-DD HH:MI:SS.ssssss format. | SELECT LOCALTIMESTAMP; | 2026-06-04 17:02:18.329951 |
NOW | timestamptz | Same as: CURRENT_TIMESTAMP. Retrieves current timestamp (with time zone) in YYYY-MM-DD HH:MI:SS.ssssss+HH:MI format. | SELECT NOW; | 2026-06-04 17:02:18.329951+02:00 |
All possible format string specifiers are based on the Linux Programmer’s Manual.
| Code | Description |
|---|---|
%a | Three-letter abbreviation of the day of the week (e.g. "Mon" for Monday) according to the current locale. |
%A | Full name of the day of the week according to the current locale. |
%b | Three-letter abbreviation of the month name (e.g. "Jan" for January) according to the current locale. |
%B | Full month name according to the current locale. |
%c | The preferred date and time representation for the current locale. |
%C | The century number (year/100) as a 2-digit integer. (SU) |
%d | The day of the month as a decimal number (range 01 to 31). |
%D | Date format equivalent to %m/%d/%y (Americans should note that %d/%m/%y is more common in an international context). (SU) |
%e | Like %d, the day of the month as a decimal number, but a leading zero is replaced by a space. (SU) |
%E | Modifier: use alternative format; see Info box below. (SU) |
%F | Date format equivalent to %Y-%m-%d (the ISO 8601 date format). (C99) |
%G | The ISO 8601 week-based year with century as a decimal number. The 4-digit year corresponding to the ISO week number (see %V). This has the same format and value as %Y, except that if the ISO week number belongs to the previous or next year, that year is used instead. (TZ) |
%g | Like %G, but without century, that is, with a 2-digit year (00-99). (TZ) |
%h | Equivalent to %b. (SU) |
%H | The hour as a decimal number using a 24-hour clock (range 00 to 23). |
%I | The hour as a decimal number using a 12-hour clock (range 01 to 12). |
%j | The day of the year as a decimal number (range 001 to 366). |
%k | The hour (24-hour clock) as a decimal number (range 0 to 23); single digits are preceded by a blank. (See also %H.) (TZ) |
%l | The hour (12-hour clock) as a decimal number (range 1 to 12); single digits are preceded by a blank. (See also %I.) (TZ) |
%m | The month as a decimal number (range 01 to 12). |
%M | The minute as a decimal number (range 00 to 59). |
%n | A newline character. (SU) |
%O | Modifier: use alternative numeric symbols; see Info box below. (SU) |
%p | Either "AM" or "PM" according to the given time value, or the corresponding strings for the current locale. Noon is treated as "PM" and midnight as "AM". |
%P | Like %p but in lowercase: "am" or "pm" or a corresponding string for the current locale. (GNU) |
%r | The time in a.m. or p.m. notation. In the POSIX locale this is equivalent to %I:%M:%S %p. (SU) |
%R | The time in 24-hour notation (%H:%M). (SU) For a version including the seconds, see %T below. |
%s | The number of seconds since the Epoch, 1970-01-01 00:00:00 +0000 (UTC). (TZ) |
%S | The second as a decimal number (range 00 to 60). (The range is up to 60 to allow for occasional leap seconds.) |
%t | A tab character. (SU) |
%T | The time in 24-hour notation (%H:%M:%S). (SU) |
%u | The day of the week as a decimal, range 1 to 7, Monday being 1. See also %w. (SU) |
%U | The week number of the current year as a decimal number, range 00 to 53, starting with the first Sunday as the first day of week 01. See also %V and %W. |
%V | The ISO 8601 week number of the current year as a decimal number, range 01 to 53, where week 1 is the first week that has at least 4 days in the new year. See also %U and %W. (SU) |
%w | The day of the week as a decimal, range 0 to 6, Sunday being 0. See also %u. |
%W | The week number of the current year as a decimal number, range 00 to 53, starting with the first Monday as the first day of week 01. |
%x | The preferred date representation for the current locale without the time. In the POSIX locale this is equivalent to %m/%d/%y. |
%X | The preferred time representation for the current locale without the date. In the POSIX locale this is equivalent to %H:%M:%S. |
%y | The year as a decimal number without a century (range 00 to 99). |
%Y | The year as a decimal number including the century. |
%z | The +hhmm or -hhmm numeric timezone (that is, the hour and minute offset from UTC). (SU) |
%Z | The timezone name or abbreviation. |
%+ | The date and time in date(1) format. (TZ) (Not supported in glibc2.) |
%% | A literal "%" character. |
(SU) The Single UNIX Specification mentions %Ec, %EC, %Ex, %EX, %Ey, %EY, %Od, %Oe, %OH, %OI, %Om, %OM, %OS, %Ou, %OU, %OV, %Ow, %OW, and %Oy, where the effect of the O modifier is to use alternative numeric symbols (such as Roman numerals), and that of the E modifier is to use a locale-dependent alternative representation. If the alternative format or specification does not exist for the current locale, the behavior will be as if the unmodified conversion specification were used.