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.

note: Data 360 SQL outputs interval data in the ISO-8601 style. For example, P1DT2H3M4S corresponds to 1 day, 2 hours, 3 minutes, and 4 seconds.

Operators

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.

SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Result: 10537200

SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
/ 60 / 60 / 24;
Result: 121.958333333333

SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
Result: 121 days 23:00:00

Functions

EXTRACT and date_part

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.

EXTRACT(
    <field>
    FROM
        <source>
        [ , <fiscal_option>, ...]
        [ , <localized_week_option>, ...]
)

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

Fields

• century

  • : The century. The first century starts at 0001-01-01 00:00:00 AD. There's no century number 0.

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

• day

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

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

• decade

  • : The year field divided by 10

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

• dow

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

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

• doy

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

    SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
    Result: 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

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

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

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

• hour

  • : The hour field (0 - 23)

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

• fiscal_month

  • : The month within the fiscal year (1 - 12)

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

• fiscal_quarter

  • : The quarter within the fiscal year (1 - 4)

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

• fiscal_week

  • : The week within the fiscal year (1 - 54)

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

• fiscal_year

  • : The fiscal year

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

• isodow

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

    SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
    Result: 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.

    SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
    Result: 2005
    
    SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
    Result: 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.

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

• millennium

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

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

• milliseconds

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

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

• minute

  • : The minutes field (0 - 59)

    SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
    Result: 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)

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

• quarter

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

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

• second

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

    SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
    Result: 40
    
    SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
    Result: 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.

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

• year

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

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

date_trunc

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

date_trunc(
  <field>,
  <source>
    [, <time_zone>]
    [, <fiscal_option> [, ...] ]
    [, first_day_of_localized_week => <value> ]
)

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

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00

SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Result: 2001-02-16 00:00:00-05

SELECT date_trunc('fiscal_year', TIMESTAMP '2001-02-16 20:38:40', fiscal_year_start_month => 2);
Result: 2001-02-01 00:00:00

Current Date/Time

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:

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Result: 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.

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';
CREATE 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.

Fiscal Calendar Options

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, and TO_CHAR functions when the field is a fiscal date unit:

fiscal_week
fiscal_month
fiscal_quarter
fiscal_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:

DayDate__c                      date    -- The calendar date (lookup key for the table)
YearNumber__c                   int     -- The fiscal year number (e.g., 2025)
QuarterNumber__c                int     -- Fiscal quarter within the year (1-4)
MonthNumber__c                  int     -- Fiscal month within the year (e.g., 1-12 or 1-13)
WeekNumber__c                   int     -- Fiscal week within the year
FiscalYearFirstDayDate__c       date    -- First day of the fiscal year
FiscalQuarterFirstDayDate__c    date    -- First day of the fiscal quarter
FiscalMonthFirstDayDate__c      date    -- First day of the fiscal month
FiscalWeekFirstDayDate__c       date    -- First day of the fiscal week
RunningYearNumber__c            int     -- Absolute year index (1, 2, 3, ...)
RunningQuarterNumber__c         int     -- Absolute quarter index (1, 2, 3, ...)
RunningMonthNumber__c           int     -- Absolute month index (1, 2, 3, ...)
RunningWeekNumber__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:

SELECT EXTRACT(fiscal_week FROM timestamp '2000-01-02 20:38:40', first_day_of_fiscal_week => 7);
Result: 2

SELECT EXTRACT(fiscal_year FROM timestamp '2000-02-16 20:38:40', fiscal_year_start_month => 3);
Result: 1999

SELECT EXTRACT(fiscal_year FROM timestamp '2000-02-16 20:38:40', use_start_date_as_fiscal_year_name => false);
Result: 2001

SELECT datediff('fiscal_year', date '2025-02-01', date '2025-03-01', fiscal_year_start_month => 3);
Result: 1

Localized Week Options

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:

SELECT EXTRACT(localized_week FROM timestamp '2023-01-01', first_day_of_localized_week => 1, minimal_days_in_localized_first_week => 1);
Result: 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.

SELECT EXTRACT(localized_week FROM timestamp '2023-01-01', first_day_of_localized_week => 1, minimal_days_in_localized_first_week => 2);
Result: 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.

SELECT EXTRACT(localized_week FROM timestamp '2023-01-01', first_day_of_localized_week => 1, minimal_days_in_localized_first_week => 3);
Result: 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.