Date/Time Functions and Operators

Applies to: ✅ Data 360 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.

Basic arithmetic operators (+, -, *, /) for dates, times, and intervals.

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.

1SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
2EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
3Result: 10537200
4
5SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
6EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
7/ 60 / 60 / 24;
8Result: 121.958333333333
9
10SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
11Result: 121 days 23:00:00

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.

1EXTRACT(
2    <field>
3    FROM
4        <source>
5        [ , <fiscal_option>, ...]
6        [ , <localized_week_option>, ...]
7)

<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.

1SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
2Result: 20
3
4SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
5Result: 21

day

For timestamp values, the day of the month field (1 - 31). For interval values, the number of days.

1SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 16
3
4SELECT date_part(DAY FROM INTERVAL '40 days 1 minute');
5Result: 40

decade

The year field divided by 10

1SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 200

dow

The day of the week as Sunday (0) to Saturday (6).

1SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 5

doy

The day of the year (1 - 365/366)

1SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 47

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

1SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
2Result: 982384720.12
3
4SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
5Result: 442800

You can convert an epoch value back to a time stamp with to_timestamp.

1SELECT to_timestamp(982384720.12);
2Result: 2001-02-17 04:38:40.12+00

hour

The hour field (0 - 23)

1SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 20

fiscal_month

The month within the fiscal year (1 - 12)

1SELECT EXTRACT(FISCAL_MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 2

fiscal_quarter

The quarter within the fiscal year (1 - 4)

1SELECT EXTRACT(FISCAL_QUARTER FROM TIMESTAMPTZ '2001-02-16 20:38:40');
2Result: 1

fiscal_week

The week within the fiscal year (1 - 54)

1SELECT EXTRACT(FISCAL_WEEK FROM TIMESTAMP '2001-12-16 20:38:40');
2Result: 50

fiscal_year

The fiscal year

1SELECT EXTRACT(FISCAL_YEAR FROM TIMESTAMP '2001-12-16 20:38:40');
2Result: 2001

isodow

The day of the week as Monday (1) to Sunday (7). This is identical to dow except for Sunday.

1SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
2Result: 7

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.

1SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
2Result: 2005
3
4SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
5Result: 2006

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.

1SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
2Result: 28500000

millennium

The millennium. Years in the 1900s are in the second millennium. The third millennium started January 1, 2001.

1SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 3

milliseconds

The seconds field, including fractional parts, multiplied by 1000.

1SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
2Result: 28500

minute

The minutes field (0 - 59)

1SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 38

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)

1SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 2
3
4SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
5Result: 3
6
7SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
8Result: 1

quarter

The quarter of the year (1 - 4) that the date is in

1SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 1

second

The seconds field, including fractional parts (0 - 59; can be 60 on leap seconds)

1SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 40
3
4SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
5Result: 28.5

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.

1SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 7

year

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

1SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
2Result: 2001

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

1date_trunc(
2  <field>,
3  <source>
4    [, <time_zone>]
5    [, <fiscal_option> [, ...] ]
6    [, first_day_of_localized_week => <value> ]
7)

<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:

1SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
2Result: 2001-02-16 20:00:00
3
4SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
5Result: 2001-01-01 00:00:00
6
7SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
8Result: 3 days 02:00:00
9
10SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
11Result: 2001-02-16 00:00:00-05
12
13SELECT date_trunc('fiscal_year', TIMESTAMP '2001-02-16 20:38:40', fiscal_year_start_month => 2);
14Result: 2001-02-01 00:00:00

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:

1SELECT CURRENT_TIME;
2Result: 14:39:53.662522-05
3
4SELECT CURRENT_DATE;
5Result: 2001-12-23
6
7SELECT CURRENT_TIMESTAMP;
8Result: 2001-12-23 14:39:53.662522-05

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.

1SELECT CURRENT_TIMESTAMP;
2SELECT now();
3SELECT TIMESTAMP 'now';
4CREATE TABLE a (t TIMESTAMP DEFAULT 'now');

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 configure standard or custom fiscal years in your query. The fiscal calendar can differ from the calendar based on your business needs.

You can use the fiscal calendar options in EXTRACT, DATE_PART, DATE_TRUNC, DATEDIFF, DATE_ADD, and TO_CHAR functions when the field is a fiscal date unit:

1fiscal_week
2fiscal_month
3fiscal_quarter
4fiscal_year

Options for Standard Fiscal Calendars:

• fiscal_year_start_month => <value> : The first month of your fiscal year. Default: 1 (January). For example, if the fiscal year starts in February the <value> is 2.

• first_day_of_fiscal_week => <value> : The first day of your fiscal week. Default: 1 (Monday). For example, if the fiscal year starts in January and <value> is 1 (Monday), fiscal_week on date 2023-01-02 returns 2, because 2023-01-01 is a Sunday making 2023-01-02 the second week of the fiscal year.

• use_start_date_as_fiscal_year_name => <value> : Sets the naming for your fiscal year as the current year or the next year. Default: true. For example, the fiscal year starting at 2020-04-01 is 2020 if value is true. If value is false, the fiscal year is 2021.

Options for Custom Fiscal Calendars:

• fiscal_period_table => TABLE(<table_expression>) : Uses a custom fiscal calendar that you define in a <table_expression>. The <table_expression> is a lookup table that maps each day to the fiscal periods. It has these columns:

1DayDate__c                      date    -- The calendar date (lookup key for the table)
2YearNumber__c                   int     -- The fiscal year number (e.g., 2025)
3QuarterNumber__c                int     -- Fiscal quarter within the year (1-4)
4MonthNumber__c                  int     -- Fiscal month within the year (e.g., 1-12 or 1-13)
5WeekNumber__c                   int     -- Fiscal week within the year
6FiscalYearFirstDayDate__c       date    -- First day of the fiscal year
7FiscalQuarterFirstDayDate__c    date    -- First day of the fiscal quarter
8FiscalMonthFirstDayDate__c      date    -- First day of the fiscal month
9FiscalWeekFirstDayDate__c       date    -- First day of the fiscal week
10RunningYearNumber__c            int     -- Absolute year index (1, 2, 3, ...)
11RunningQuarterNumber__c         int     -- Absolute quarter index (1, 2, 3, ...)
12RunningMonthNumber__c           int     -- Absolute month index (1, 2, 3, ...)
13RunningWeekNumber__c            int     -- Absolute week index (1, 2, 3, ...)

Notes:

• You can’t combine options for standard and custom fiscal calendars.

• In Data 360, by default functions use the fiscal calendar configured in your Org. If you haven’t configured a calendar, the function uses a standard fiscal calendar with the default values.

Some examples:

1SELECT EXTRACT(fiscal_week FROM timestamp '2000-01-02 20:38:40', first_day_of_fiscal_week => 7);
2Result: 2
3
4SELECT EXTRACT(fiscal_year FROM timestamp '2000-02-16 20:38:40', fiscal_year_start_month => 3);
5Result: 1999
6
7SELECT EXTRACT(fiscal_year FROM timestamp '2000-02-16 20:38:40', use_start_date_as_fiscal_year_name => false);
8Result: 2001
9
10SELECT DATEDIFF('fiscal_year', date '2025-02-01', date '2025-03-01', fiscal_year_start_month => 3);
11Result: 1

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:

EXTRACT function for localized_week:

1SELECT EXTRACT(localized_week FROM timestamp '2023-01-01', first_day_of_localized_week => 1, minimal_days_in_localized_first_week => 1);
2Result: 1

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.

1SELECT EXTRACT(localized_week FROM timestamp '2023-01-01', first_day_of_localized_week => 1, minimal_days_in_localized_first_week => 2);
2Result: 53

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.

1SELECT EXTRACT(localized_week FROM timestamp '2023-01-01', first_day_of_localized_week => 1, minimal_days_in_localized_first_week => 3);
2Result: 52

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.