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.

OperatorExample
+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'
FunctionDescriptionExample
current_datedateCurrent date; see Current Date/Time
current_timetime with time zoneCurrent time of day; see Current Date/Time
current_timestamptimestamp with time zoneCurrent date and time (start of current statement); see Current Date/Time
date_part(text, timestamp)double precisionGet subfield (equivalent to EXTRACT)date_part('hour', timestamp '2001-02-16 20:38:40')20
date_part(text, interval)double precisionGet subfield (equivalent to EXTRACT)date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp)timestampTruncate to specified precision; see date_truncdate_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00
date_trunc(text, interval)intervalTruncate to specified precision; see date_truncdate_trunc('hour', interval '2 days 3 hours 40 minutes')2 days 03:00:00
day(timestamp)integerGet day (equivalent to EXTRACT(DAY FROM ...))day(timestamp '2001-02-16 20:38:40')16
day(interval)integerGet day (equivalent to EXTRACT(DAY FROM ...))day(interval '42 days')42
extract(field from timestamp) → variadicGet subfield; see EXTRACTextract (hour from timestamp '2001-02-16 20:38:40')20
extract (field from interval) → variadicGet subfield; see EXTRACTextract(month from interval '2 years3 months')3
hour(timestamp)integerGet hour (equivalent to EXTRACT(HOUR FROM ...))hour(timestamp '2001-02-16 20:38:40')20
hour(interval)integerGet hour (equivalent to EXTRACT(HOUR FROM ...))hour(interval '121 minutes')2
justify_days(interval)intervalAdjust interval so 30-day time periods are represented as monthsjustify_days(interval '35 days')1 mon 5 days
justify_hours(interval)intervalAdjust interval so 24-hour time periods are represented as daysjustify_hours (interval '27 hours')1 day 03:00:00
justify_interval(interval)intervalAdjust interval using justify_days and justify_hours, with additional sign adjustmentsjustify_interval(interval '1 mon -1 hour')29 days 23:00:00
make_date(year int, month int, day int)dateCreate date from year, month and day fieldsmake_date(2013, 7, 15)2013-07-15
make_time(hour int, min int, sec double precision)timeCreate time from hour, minute and seconds fieldsmake_time(8,15, 23.5)08:15:23.5
make_timestamp(year int, month int, day int, hour int, min int, sec double precision)timestampCreate timestamp from year, month, day, hour, minute and seconds fieldsmake_timestamp(2013, 7, 15, 8,15, 23.5)2013-07-15 08:15:23.5
minute(timestamp)integerGet minute (equivalent to EXTRACT(MINUTE FROM ...))minute(timestamp '2001-02-16 20:38:40')38
minute(interval)integerGet minute (equivalent to EXTRACT(MINUTE FROM ...))minute(interval '30 minutes')30
month(timestamp)integerGet month (equivalent to EXTRACT(MONTH FROM ...))month(timestamp '2001-02-16 20:38:40')2
month(interval)integerGet month (equivalent to EXTRACT(MONTH FROM ...))month (interval '6 months')6
now()timestamp with time zoneCurrent date and time (start of current statement); see Current Date/Time
quarter(timestamp)integerGet quarter (equivalent to EXTRACT(QUARTER FROM ...))quarter(timestamp '2001-02-16 20:38:40')1
second(timestamp)numericGet second (equivalent to EXTRACT(SECOND FROM ...))second(timestamp '2001-02-16 20:38:40.500')40.500
second (interval)numericGet second (equivalent to EXTRACT(SECOND FROM ...))second(interval '21 seconds')21.0
timestamp at time zone 'timezone'timestamptzConvert the timestamp in specified time zone into timestamptz with default time zonetimestamp '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'timestampConvert the timestamptz into the timestamp in specified time zonetimestamptz '2001-08-16 18:38:40+00' at time zone 'Europe/Berlin'2001-08-16 20:38:40
timestamp at time zone interval 'interval'timestamptzConvert the timestamp in UTC offset defined by interval into timestamptz with default time zonetimestamp '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'timestampConvert the timestamptz into the timestamp in UTC offset defined by intervaltimestamptz '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 zoneConvert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestampto_timestamp(1284352323)2010-09-13 04:32:03+00
week(timestamp)integerGet week (equivalent to EXTRACT(WEEK FROM ...))week(timestamp '2001-02-16 20:38:40')7
year(timestamp)integerGet year (equivalent to EXTRACT(YEAR FROM ...))year(timestamp '2001-02-16 20:38:40')2001
year (interval)integerGet 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). 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 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 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 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:

MondayTuesdayWednesdayThursdayFridaySaturdaySunday
2021-12-272021-12-282021-12-292021-12-302021-12-312022-01-012022-01-02
2022-01-032022-01-042022-01-052022-01-062022-01-072022-01-082022-01-09
.....................
2022-12-262022-12-272022-12-282022-12-292022-12-302022-12-312023-01-01
2023-01-022023-01-032023-01-042023-01-052023-01-062023-01-072023-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.