Date/Time Functions and Operators

Applies to: ✅ Data Cloud SQL ✅ Tableau Hyper API

Use date and time functions to create time-based queries against your data. To work with data and time functions, review the Date/Time Data Types.

Most of the functions and operators on this page take the time or timestamp variant, time with time zone or timestamp with time zone. The + and * operators come in commutative pairs, for example, both date + integer and integer + date.

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 (+, -, *, /) for dates, times, and intervals.

Operation	Return Type	Example
`date + integer`	`date`	`date '2024-03-15' + 7` → `2024-03-22`
`date + interval`	`timestamp`	`date '2024-03-15' + interval '1 day'` → `2024-03-16 00:00:00`
`date + time`	`timestamp`	`date '2024-03-15' + time '10:00:00'` → `2024-03-15 10:00:00`
`interval + interval`	`interval`	`interval '1 hour' + interval '30 minutes'` → `01:30:00`
`timestamp + interval`	`timestamp`	`timestamp '2024-03-15 12:00:00' + interval '2 hours'` → `2024-03-15 14:00:00`
`time + interval`	`time`	`time '12:00:00' + interval '1 hour'` → `13:00:00`
`- interval`	`interval`	`- interval '1 hour'` → `-01:00:00`
`date - date`	`integer`	`date '2024-03-22' - date '2024-03-15'` → `7`
`date - integer`	`date`	`date '2024-03-22' - 7` → `2024-03-15`
`date - interval`	`timestamp`	`date '2024-03-16' - interval '1 day'` → `2024-03-15 00:00:00`
`time - time`	`interval`	`time '14:00:00' - time '12:00:00'` → `02:00:00`
`time - interval`	`time`	`time '13:00:00' - interval '1 hour'` → `12:00:00`
`timestamp - interval`	`timestamp`	`timestamp '2024-03-15 14:00:00' - interval '2 hours'` → `2024-03-15 12:00:00`
`interval - interval`	`interval`	`interval '1 hour' - interval '30 minutes'` → `00:30:00`
`timestamp - timestamp`	`interval`	`timestamp '2024-03-15 14:00:00' - timestamp '2024-03-15 12:00:00'` → `02:00:00`
`interval * integer`	`interval`	`interval '30 minutes' * 2` → `01:00:00`
`interval * double precision`	`interval`	`interval '1 hour' * 1.5` → `01:30:00`
`interval / double precision`	`interval`	`interval '1 hour' / 2` → `00:30:00`

To simplify timestamp subtraction, use EXTRACT(EPOCH FROM ...) to convert the values into seconds 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 savings 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 also includes a daylight-savings time change between the two dates.

Function	Return Type	Description	Example
`current_date`	`date`	Return the current date. See Current Date/Time.
`current_time`	`time with time zone`	Return the current time of day with time zone. See Current Date/Time.
`current_timestamp`	`timestamp with time zone`	Return the current date and time with time zone (start of current statement). See Current Date/Time.
`datediff(text, timestamp, timestamp)`	`integer`	Calculate the difference between two timestamps in the specified unit.	`datediff('day', timestamp '2001-02-03', timestamp '2001-02-13')` → `10`
`date_add(text, int, timestamp)`	`timestamp`	Add a multiple of the specified date unit to a given date or timestamp	`date_add('year', 2, timestamp '2021-02-16 11:01:02')` → `2023-02-16 11:01:02`
`date_part(text, timestamp)`	`double precision`	Return a subfield from a timestamp (equivalent to EXTRACT).	`date_part('hour', timestamp '2001-02-16 20:38:40')` → `20`
`date_part(text, interval)`	`double precision`	Return a subfield from an interval (equivalent to EXTRACT).	`date_part('month', interval '2 years 3 months')` → `3`
`date_trunc(text, timestamp)`	`timestamp`	Truncate a timestamp to the 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 an interval to the specified precision. See date_trunc.	`date_trunc('hour', interval '2 days 3 hours 40 minutes')` → `2 days 03:00:00`
`day(timestamp)`	`integer`	Return the day from a timestamp (equivalent to `EXTRACT(DAY FROM ...)`).	`day(timestamp '2001-02-16 20:38:40')` → `16`
`day(interval)`	`integer`	Return the day from an interval (equivalent to `EXTRACT(DAY FROM ...)`).	`day(interval '42 days')` → `42`
`extract(field from timestamp)`	various data types	Return a subfield from a timestamp. See EXTRACT.	`extract (hour from timestamp '2001-02-16 20:38:40')` → `20`
`extract(field from interval)`	various data types	Return a subfield from an interval. See EXTRACT.	`extract(month from interval '2 years3 months')` → `3`
`hour(timestamp)`	`integer`	Return the hour from a timestamp (equivalent to `EXTRACT(HOUR FROM ...)`).	`hour(timestamp '2001-02-16 20:38:40')` → `20`
`hour(interval)`	`integer`	Return the hour from an interval (equivalent to `EXTRACT(HOUR FROM ...)`).	`hour(interval '121 minutes')` → `2`
`justify_days(interval)`	`interval`	Adjust an interval so 30-day time periods are represented as months.	`justify_days(interval '35 days')` → `1 mon 5 days`
`justify_hours(interval)`	`interval`	Adjust an 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 an 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 a 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 a 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 a 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`	Return the minute from a timestamp (equivalent to `EXTRACT(MINUTE FROM ...)`).	`minute(timestamp '2001-02-16 20:38:40')` → `38`
`minute(interval)`	`integer`	Return the minute from an interval (equivalent to `EXTRACT(MINUTE FROM ...)`).	`minute(interval '30 minutes')` → `30`
`month(timestamp)`	`integer`	Return the month from a timestamp (equivalent to `EXTRACT(MONTH FROM ...)`).	`month(timestamp '2001-02-16 20:38:40')` → `2`
`month(interval)`	`integer`	Return the month from an interval (equivalent to `EXTRACT(MONTH FROM ...)`).	`month (interval '6 months')` → `6`
`now()`	`timestamp with time zone`	Return the current date and time with time zone (start of current statement). See Current Date/Time.
`quarter(timestamp)`	`integer`	Return the quarter from a timestamp (equivalent to `EXTRACT(QUARTER FROM ...)`).	`quarter(timestamp '2001-02-16 20:38:40')` → `1`
`second(timestamp)`	`numeric`	Return the second from a timestamp (equivalent to `EXTRACT(SECOND FROM ...)`).	`second(timestamp '2001-02-16 20:38:40.500')` → `40.500`
`second (interval)`	`numeric`	Return the second from an interval (equivalent to `EXTRACT(SECOND FROM ...)`)	`second(interval '21 seconds')` → `21.0`
`timestamp at time zone 'timezone'`	`timestamptz`	Convert the timestamp in the specified time zone to timestamptz with the 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 to the timestamp in the 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 the UTC offset defined by `interval` to timestamptz with the 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 to the timestamp in the 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 a Unix epoch (seconds since 1970-01-01 00:00:00+00) to a timestamp.	`to_timestamp(1284352323)` → `2010-09-13 04:32:03+00`
`week(timestamp)`	`integer`	Return the week from a timestamp (equivalent to `EXTRACT(WEEK FROM ...)`).	`week(timestamp '2001-02-16 20:38:40')` → `7`
`year(timestamp)`	`integer`	Return the year from a timestamp (equivalent to `EXTRACT(YEAR FROM ...)`).	`year(timestamp '2001-02-16 20:38:40')` → `2001`
`year (interval)`	`integer`	Return the year from an interval (equivalent to `EXTRACT(YEAR FROM ...)`).	`year(interval '8 years')` → `8`

The extract and date_part are equivalent functions. These functions retrieve subfields such as year or hour from date and time values. The extract function is primarily used for computational processing. To display date or time values, see Formatting Functions.

<field>: An identifier or string that selects what field to extract from the source value. EXTRACT and date_part support the same field options. For valid options, see Fields.
<source>: The type and value to extract the field from. Must be a value expression of type date, timestamp, timestamptz, time, or interval. (These functions cast expressions of type date to timestamp)
<fiscal_option>: The fiscal calendar options define the start date of the fiscal calendar. <source> must be of type date, timestamp or timestamptz, and <field> must be fiscal_week, fiscal_month, fiscal_quarter, or fiscal_year. See Fiscal Calendar Options.
<localized_week_option>: The localized week options can define the start of a week and a year. <source> must be type date, timestamp, or timestamptz, and <field> is localized_week. See Localized Week Options.

The result type of extract depends on the given <field>. The result type of date_part is always double precision, independent of the selected field.

numeric(8,6) for field second
numeric(18,6) for field epoch
integer for any other field

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). For interval 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 and timestamp 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

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 to dow 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 and minimal_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)

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, and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31 is part of the first week of 2013. It’s recommended that you use the isoyear field together with week to get consistent results.

year

The year field. There's no 0 AD, so subtract BC years from AD years with care.

The date_trunc function truncates any timestamp expression or literal based on the time interval that you specify, such as hour, day, or month.

<field>

Define the precision for truncating the input value. Sets all other fields to zero (or one, for day and month).

Valid values: microseconds, milliseconds, second, minute, hour, day, week, localized_week, fiscal_week, month, fiscal_month, quarter, fiscal_quarter, year, fiscal_year, decade, century, and millennium.

Return value: timestamp, timestamp with time zone, or interval.

<source>

The value to truncate.

Valid values: timestamp, timestamp with time zone, or interval. (date_trunc casts values of type date and time to timestamp or interval, respectively.)

Limitations: <source> can't be of type interval if <field> is week because a month can contain a fractional number of weeks. You can't provide a time zone with timestamp without time zone or interval inputs. With type timestamp with time zone, date_trunc performs the truncation with respect to a defined time zone. For example, truncation to day produces a value that is midnight in that zone. By default, date_trunc uses the current time zone.

<fiscal_option>

The fiscal calendar options define the start date of the fiscal calendar. <source> must be of type date, timestamp or timestamptz, and <field> must be fiscal_week, fiscal_month, fiscal_quarter, or fiscal_year. See Fiscal Calendar Options.

<localized_week>

The localized week options define the start of a week and a year. <source> must be type date, timestamp, or timestamptz, and <field> is localized_week. See Localized Week Options.

Examples, assuming the local time zone is America/New_York:

These functions return values based on the start time of the current statement.

CURRENT_DATE
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TIME(<precision>)
CURRENT_TIMESTAMP(<precision>)

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.

The fourth example uses the current timestamp at the time of insertion of a tuple, 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.

You can use the fiscal calendar options in datediff, EXTRACT, date_part, and date_trunc functions, if the field for those functions is one of the fiscal date units:

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 be 2. The default value is 1 (January).
first_day_of_fiscal_week => <value> : The first day of the fiscal week. If the fiscal year starts with January and <value> is 1 (Monday), fiscal_week on date 2023-01-02 returns 2, since 2023-01-01 is a Sunday. The default value is 1 (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 at 2020-04-01 is 2020 if value is true. If value is false, 2021 is the result. The default value is true.

Some examples:

The localized week options define the start of a week and a year in the EXTRACT, date_part, and date_trunc.

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.