Date/Time Functions and Operators
Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API
Functions show the available functions for date/time value processing, with details appearing in the following subsections. Operators illustrate the behaviors of the basic arithmetic operators (+
, *
, etc.). For formatting functions, refer to Data Type Formatting Functions. You should be familiar with the background information on date/time data types from Date/Time Types.
Most of the functions and operators described below that take time
or timestamp
inputs actually come in two variants. One takes time with time zone
or timestamp with time zone
, and one takes time without time zone
or timestamp without time zone
. For brevity, these variants aren’t 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.
Data Cloud SQL outputs interval data in the ISO-8601 style. For example, P1DT2H3M4S
corresponds to 1 day, 2 hours, 3 minutes, and 4 seconds.
Basic arithmetic operators (+
, -
, *
, /
) are also available for dates, times, and intervals.
Operator | Example |
---|---|
+ | 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' |
Function | Description | Example |
---|---|---|
current_date → date | Current date; see Current Date/Time | |
current_time → time with time zone | Current time of day; see Current Date/Time | |
current_timestamp → timestamp with time zone | Current date and time (start of current statement); see Current Date/Time | |
date_part(text, timestamp) →double precision | Get subfield (equivalent to EXTRACT) | date_part('hour', timestamp '2001-02-16 20:38:40') → 20 |
date_part(text, interval) → double precision | Get subfield (equivalent to EXTRACT) | date_part('month', interval '2 years 3 months') → 3 |
date_trunc(text, timestamp) → timestamp | Truncate to specified precision; see date_trunc | date_trunc('hour', timestamp '2001-02-16 20:38:40') → 2001-02-16 20:00:00 |
date_trunc(text, interval) → interval | Truncate to specified precision; see date_trunc | date_trunc('hour', interval '2 days 3 hours 40 minutes') → 2 days 03:00:00 |
day(timestamp) → integer | Get day (equivalent to EXTRACT(DAY FROM ...) ) | day(timestamp '2001-02-16 20:38:40') → 16 |
day(interval) → integer | Get day (equivalent to EXTRACT(DAY FROM ...) ) | day(interval '42 days') → 42 |
extract(field from timestamp) → variadic | Get subfield; see EXTRACT | extract (hour from timestamp '2001-02-16 20:38:40') → 20 |
extract (field from interval) → variadic | Get subfield; see EXTRACT | extract(month from interval '2 years3 months') → 3 |
hour(timestamp) → integer | Get hour (equivalent to EXTRACT(HOUR FROM ...) ) | hour(timestamp '2001-02-16 20:38:40') → 20 |
hour(interval) → integer | Get hour (equivalent to EXTRACT(HOUR FROM ...) ) | hour(interval '121 minutes') → 2 |
justify_days(interval) → interval | Adjust interval so 30-day time periods are represented as months | justify_days(interval '35 days') → 1 mon 5 days |
justify_hours(interval) → interval | Adjust interval so 24-hour time periods are represented as days | justify_hours (interval '27 hours') → 1 day 03:00: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 |
make_date(year int, month int, day int) → date | Create date from year, month and day fields | make_date(2013, 7, 15) → 2013-07-15 |
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 | make_timestamp(2013, 7, 15, 8,15, 23.5) → 2013-07-15 08:15:23.5 |
minute(timestamp) → integer | Get minute (equivalent to EXTRACT(MINUTE FROM ...) ) | minute(timestamp '2001-02-16 20:38:40') → 38 |
minute(interval) → integer | Get minute (equivalent to EXTRACT(MINUTE FROM ...) ) | minute(interval '30 minutes') → 30 |
month(timestamp) → integer | Get month (equivalent to EXTRACT(MONTH FROM ...) ) | month(timestamp '2001-02-16 20:38:40') → 2 |
month(interval) → integer | Get month (equivalent to EXTRACT(MONTH FROM ...) ) | month (interval '6 months') → 6 |
now() → timestamp with time zone | Current date and time (start of current statement); see Current Date/Time | |
quarter(timestamp) → integer | Get quarter (equivalent to EXTRACT(QUARTER FROM ...) ) | quarter(timestamp '2001-02-16 20:38:40') → 1 |
second(timestamp) → numeric | Get second (equivalent to EXTRACT(SECOND FROM ...) ) | second(timestamp '2001-02-16 20:38:40.500') → 40.500 |
second (interval) → numeric | Get second (equivalent to EXTRACT(SECOND FROM ...) ) | second(interval '21 seconds') → 21.0 |
timestamp at time zone 'timezone' → timestamptz | Convert the timestamp in specified time zone into timestamptz with default time zone | timestamp '2001-08-16 20:38:40' at time zone 'Europe/Berlin' → 2001-08-16 18:38:40+00 (assume the default time zone is UTC) |
timestamptz at time zone 'timezone' → timestamp | Convert the timestamptz into the timestamp in specified time zone | timestamptz '2001-08-16 18:38:40+00' at time zone 'Europe/Berlin' → 2001-08-16 20:38:40 |
timestamp at time zone interval 'interval' → timestamptz | Convert the timestamp in UTC offset defined by interval into timestamptz with default time zone | timestamp '2001-08-16 20:38:40' at time zone interval '+2:00' → 2001-08-16 18:38:40+00 (assume the default time zone is UTC) |
timestamptz at time zone interval 'interval' → timestamp | Convert the timestamptz into the timestamp in UTC offset defined by interval | timestamptz '2001-08-16 18:38:40+00' at time zone interval '+2:00' → 2001-08-16 20:38:40 |
to_timestamp(double precision) → timestamp with time zone | Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp | to_timestamp(1284352323) → 2010-09-13 04:32:03+00 |
week(timestamp) → integer | Get week (equivalent to EXTRACT(WEEK FROM ...) ) | week(timestamp '2001-02-16 20:38:40') → 7 |
year(timestamp) → integer | Get year (equivalent to EXTRACT(YEAR FROM ...) ) | year(timestamp '2001-02-16 20:38:40') → 2001 |
year (interval) → integer | Get year (equivalent to EXTRACT(YEAR FROM ...) ) | year(interval '8 years') → 8 |
To simplify timestamp subtraction, convert each value to a number of seconds using EXTRACT(EPOCH FROM ...)
and then subtract the results. This process produces the number of seconds between the two values and adjusts for the number of days in each month, time zone changes, and daylight saving time. The "-
" operator makes the same adjustments and returns the number of days (24-hours) and hours/minutes/seconds between the values. These queries show the differences in these approaches. The samples use timezone = 'US/Eastern'
and there's a daylight-saving time change between the two dates.
The extract
function retrieves subfields such as year or hour from date/time values. <source>
must be a value expression of type timestamp
, timestamptz
, time
, or interval
. (Expressions of type date
are cast to timestamp
and can therefore be used as well.) <field>
is an identifier or string that selects what field to extract from the source value.
The <fiscal_option>
can only be used if <source>
is of type date
, timestamp
or timestamptz
, and <field>
is one of: fiscal_week
, fiscal_month
, fiscal_quarter
, or fiscal_year
. See Fiscal Calendar Options.
The <localized_week_option>
can only be used if <source>
is of type date
, timestamp,
or timestamptz
, and <field>
is localized_week
. See Localized Week Options.
The result type of extract
depends on the given <field>
: numeric(8,6)
for field second
, numeric(18,6)
for field epoch
, and integer
for any other field.
These are valid field names:
century
The century. The first century starts at 0001-01-01 00:00:00 AD. There's no century number 0.
day
For
timestamp
values, the day of the month field (1 - 31). Forinterval
values, the number of days.
decade
The year field divided by 10
dow
The day of the week as Sunday (
0
) to Saturday (6
).
doy
The day of the year (1 - 365/366)
epoch
- : For
timestamp with time zone
values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative) - for
date
andtimestamp
values, the number of seconds since 1970-01-01 00:00:00 local time - for
interval
values, the total number of seconds in the interval
- : For
You can convert an epoch value back to a time stamp with to_timestamp
.
hour
The hour field (0 - 23)
fiscal_month
The month within the fiscal year (1 - 12)
fiscal_quarter
The quarter within the fiscal year (1 - 4)
fiscal_week
The week within the fiscal year (1 - 54)
fiscal_year
The fiscal year
isodow
The day of the week as Monday (
1
) to Sunday (7
). This is identical todow
except for Sunday.
isoyear
The ISO 8601 week-numbering year that the date falls in (not applicable to intervals). Each ISO 8601 week-numbering year begins with the Monday of the week containing 4 January. In early January or late December, the ISO year can be different from the Gregorian year. See the
week
field for more information.
localized_week
The week within the year with a custom start date, which is determined by the parameters
first_day_of_localized_week
andminimal_days_in_localized_first_week
. See Localized Week Options for detailed explanation and examples.microseconds
The seconds field, including fractional parts, multiplied by 1000000.
millennium
The millennium. 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.
minute
The minutes field (0 - 59)
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)
- : For
quarter
The quarter of the year (1 - 4) that the date is in
second
The seconds field, including fractional parts (0 - 59; can be 60 on leap seconds)
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.
timezone_hour
The hour part of the time zone offset
timezone_minute
The minute part 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. For example, the first Thursday of a year is in week 1 of that year. In the ISO week-numbering system, early January dates can be in the 52nd or 53rd week of the year before. Late December dates can be in the first week of the next year. For example,
2005-01-01
is part of the 53rd week of year 2004, and2006-01-01
is part of the 52nd week of year 2005, while2012-12-31
is part of the first week of 2013. It’s recommended that you use theisoyear
field together withweek
to get consistent results.
year
The year field. There's no
0 AD
, so subtractBC
years fromAD
years with care.
The extract
function is primarily used for computational processing. To date/time values for display, see Formatting Functions.
The date_part
function is equivalent to the SQL-standard function extract
:
The <fiscal_option>
can only be used if <source>
is of type date
, timestamp
, or timestamptz
. <field>
is one of fiscal_week
, fiscal_month
, fiscal_quarter
, fiscal_year
. See Fiscal Calendar Options.
The <localized_week_option>
can only be used if <source>
is of type date
, timestamp
, or timestamptz
, and <field>
is localized_week
. See Localized Week Options.
The <field>
parameter must be a string value, not a name. The valid field names for date_part
are the same as for extract
. In contrast to extract
, the result type of date_part
is always double precision
, independent of the selected field.
The function date_trunc
is conceptually similar to the trunc
function for numbers.
<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 also of type timestamp, timestamp with time zone, or interval. It has all fields that are less important than the selected one set to zero (or one, for day and month).
The <fiscal_option>
can only be used if <source>
is of type date
, timestamp
, or timestamptz
. <field>
is one of fiscal_week
, fiscal_month
, fiscal_quarter
, fiscal_year
. See Fiscal Calendar Options.
The parameter first_day_of_localized_week
can only be used if <source>
is of type date
, timestamp
, or timestamptz
. <field>
is localized_week
. See Localized Week Options.
Valid values for <field>
are microseconds
, milliseconds
, second
, minute
, hour
, day
, week
, localized_week
, fiscal_week
, month
, fiscal_month
, quarter
, fiscal_quarter
, year
, fiscal_year
, decade
, century
, and millennium
.
If <source>
is of type interval
, then <field>
can't be week
because a month can contain a fractional number of weeks.
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 time zone.
A time zone can't be specified when processing timestamp without time zone
or interval
inputs.
Examples, assuming the local time zone is America/New_York
:
These SQL-standard functions all return values based on the start time of the current statement.
The return value of both CURRENT_TIME
and CURRENT_TIMESTAMP
contains the time zone.
Some examples:
Date and time data types accept the special literal value now
to specify the current date and time. These three types all return the same result.
In the fourth example, the current timestamp at the time of insertion of a tuple is used, and not the timestamp of table creation.
The fiscal calendar options define the start date of the fiscal calendar. A fiscal year can differ from a calendar year, and the first fiscal week, fiscal month, or fiscal quarter can also differ from their calendar counterparts. See Define Your Fiscal Year for more details. Fiscal calendar options are used for such cases.
The fiscal calendar options can be used in EXTRACT, date_trunc, and date_part functions, if the field for those functions is one of the fiscal fields:
Fiscal calendar options:
-
fiscal_year_start_month => <value>
: The month in which the fiscal year starts. If the fiscal year starts in February,<value>
should be2
. The default value is1
(January). -
first_day_of_fiscal_week => <value>
: The first day of the fiscal week. If the fiscal year starts with January and<value>
is1
(Monday),fiscal_week
on date2023-01-02
returns2
, since2023-01-01
is a Sunday. The default value is1
(Monday). -
use_start_date_as_fiscal_year_name => <value>
: Whether to use the year of the start date as its fiscal year name. For example, the fiscal year starting at2020-04-01
is 2020 ifvalue
istrue
. Ifvalue
isfalse
, 2021 is the result. The default value istrue
.
Some examples:
The localized week options can be used to define the start of a week and a year in EXTRACT, date_trunc , and date_part functions. Localized week options:
first_day_of_localized_week => <value>
: The first day of the localized week. If <value>
is 1, every week and every year starts with a Monday. If <value>
is 7, the week and year start with a Sunday.
minimal_days_in_localized_first_week => <value>
: This option defines the minimum number of days in the localized first week of the year for it to qualify as the first week of the year. The start of the first week of a year is the first weekday defined by first_day_of_localized_week
.
For the examples, use this calendar:
Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
---|---|---|---|---|---|---|
2021-12-27 | 2021-12-28 | 2021-12-29 | 2021-12-30 | 2021-12-31 | 2022-01-01 | 2022-01-02 |
2022-01-03 | 2022-01-04 | 2022-01-05 | 2022-01-06 | 2022-01-07 | 2022-01-08 | 2022-01-09 |
... | ... | ... | ... | ... | ... | ... |
2022-12-26 | 2022-12-27 | 2022-12-28 | 2022-12-29 | 2022-12-30 | 2022-12-31 | 2023-01-01 |
2023-01-02 | 2023-01-03 | 2023-01-04 | 2023-01-05 | 2023-01-06 | 2023-01-07 | 2023-01-08 |
EXTRACT
function for localized_week
:
The first day of a week is set to Monday. The first day of the year 2023 is 2022-12-26
, because it is the first Monday of the week that has at least 1 day in year 2023.
The first day of a week is set to Monday. The first day of the year 2023 is 2023-01-02
, because it is the first Monday of the week that has at least 2 days in year 2023.
The first day of a week is set to Monday. The first day of the year 2023 is 2023-01-02
, because it is the first Monday of the week that has at least 3 days in year 2023.