Using Dates and Timestamps

Date and time calculations are among the most widely used and most critical computations in analytics and data mining. This topic provides practical examples of common date and time queries and calculations.

In this Topic:

Loading Dates and Timestamps

Loading Timestamps with No Time Zone Attached

In the following example, the TIMESTAMP_TYPE_MAPPING parameter is set to TIMESTAMP_LTZ (local time zone). The TIMEZONE parameter is set to America/Chicago time. If a set of incoming timestamps has no time zone specified, then Snowflake loads those strings assuming the timestamps represent local time in the set time zone.

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';

CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2016-05-01 00:00:00.000');

SELECT * FROM time;

+---------------------------------+
| LTZ                             |
|---------------------------------|
| Sun, 01 May 2016 00:00:00 -0500 |
+---------------------------------+

Loading Timestamps with a Time Zone Attached

In the following example, the TIMESTAMP_TYPE_MAPPING parameter is set to TIMESTAMP_LTZ (local time zone). The ref:label-TIMEZONE parameter is set to America/Chicago time. Suppose a set of incoming timestamps has a different time zone specified. Snowflake loads the string in America/Chicago time.

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
ALTER SESSION SET TIMEZONE = 'America/Chicago';

CREATE OR REPLACE TABLE time (ltz TIMESTAMP);
INSERT INTO time VALUES ('2016-04-30 19:00:00.000 -0800');

SELECT * FROM time;

+---------------------------------+
| LTZ                             |
|---------------------------------|
| Sat, 30 Apr 2016 22:00:00 -0500 |
+---------------------------------+

Converting Timestamps to Alternative Time Zones

In the following example, a set of timestamp values is stored with no time zone data. The timestamps are loaded in UTC time and converted to other time zones:

ALTER SESSION SET TIMEZONE = 'UTC';
ALTER SESSION SET TIMESTAMP_LTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM';

CREATE OR REPLACE TABLE utctime (ntz TIMESTAMP_NTZ);
INSERT INTO utctime VALUES ('2016-05-01 00:00:00.000');

SELECT * FROM utctime;

SELECT CONVERT_TIMEZONE('UTC','America/Chicago', ntz)::timestamp_ltz AS ChicagoTime
FROM utctime;

SELECT CONVERT_TIMEZONE('UTC','America/Los_Angeles', ntz)::timestamp_ltz AS LATime

Inserting Valid Date Strings Into Date Columns in a Table

CREATE OR REPLACE TABLE mytable (date1 date);

INSERT INTO mytable VALUES (TO_DATE ('2016.07.23', 'YYYY.MM.DD')); -- 2016-07-23

INSERT INTO mytable VALUES (TO_DATE ('11:30:40','hh:mi:ss')); -- 1970-01-01

SELECT TO_VARCHAR(date1, 'dd-mon-yyyy hh:mi:ss') FROM mytable;

+-------------------------------------------+
| TO_VARCHAR(DATE1, 'DD-MON-YYYY HH:MI:SS') |
|-------------------------------------------|
| 23-Jul-2016 00:00:00                      |
| 01-Jan-1970 00:00:00                      |
+-------------------------------------------+

Retrieving the Current Date and Time

Get the current date as a DATE value:

SELECT current_date();

Get the current date and time as a TIMESTAMP value:

SELECT current_timestamp();

Retrieving Dates and Days of the Week

Get the current day of the week as a number using the EXTRACT function:

SELECT EXTRACT('dayofweek',current_date());

Note

  • The dayofweek_iso part follows the ISO-8601 data elements and interchange formats standard. The function returns the day of the week as an integer value in the range 1-7, where 1 represents Monday.
  • For compatibility with some other systems, the dayofweek part follows the UNIX standard. The function returns the day of the week as an integer value in the range 0-6, where 0 represents Sunday.

Get the current day of the week as a string using the TO_VARCHAR or DECODE function:

-- Output short English names, e.g. "Sun", "Mon" etc.
SELECT TO_VARCHAR(current_date(), 'DY');

-- Output arbitrary, explicitly-provided weekday names:
SELECT DECODE(EXTRACT ('dayofweek_iso',current_date()),
  1, 'Monday',
  2, 'Tuesday',
  3, 'Wednesday',
  4, 'Thursday',
  5, 'Friday',
  6, 'Saturday',
  7, 'Sunday');

Retrieving Date and Time Parts

Get various date and time parts for the current date and time using the DATE_PART function:

-- Current day of the month
SELECT DATE_PART(day, current_timestamp());

-- Current year
SELECT DATE_PART(year, current_timestamp());

-- Current month
SELECT DATE_PART(month, current_timestamp());

-- Current hour
SELECT DATE_PART(hour, current_timestamp());

-- Current minute
SELECT DATE_PART(minute, current_timestamp());

-- Current second
SELECT DATE_PART(second, current_timestamp());

Alternative option using the EXTRACT function:

-- Current day of the month
SELECT EXTRACT('day', current_timestamp());

-- Current year
SELECT EXTRACT('year', current_timestamp());

-- Current month
SELECT EXTRACT('month', current_timestamp());

-- Current hour
SELECT EXTRACT('hour', current_timestamp());

-- Current minute
SELECT EXTRACT('minute', current_timestamp());

-- Current second
SELECT EXTRACT('second', current_timestamp());

Alternative tabular output:

SELECT day(current_timestamp() ) ,
  hour( current_timestamp() ) ,
  second(current_timestamp()) ,
  minute(current_timestamp()) ,
  month(current_timestamp());

  +---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+
  | DAY(CURRENT_TIMESTAMP() ) | HOUR( CURRENT_TIMESTAMP() ) | SECOND(CURRENT_TIMESTAMP()) | MINUTE(CURRENT_TIMESTAMP()) | MONTH(CURRENT_TIMESTAMP()) |
  |---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------|
  |                         7 |                           6 |                          43 |                          44 |                          9 |
  +---------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+

Calculating Business Calendar Dates and Times

Get the first day of the month as a DATE value using the DATE_TRUNC function. For example, Get the first day of the current month:

SELECT DATE_TRUNC('month', current_date());

Get the last day of the current month as a DATE value using the DATEADD and DATE_TRUNC functions:

SELECT DATEADD('day',-1, DATE_TRUNC('month', DATEADD(day,31,DATE_TRUNC('month',current_date()) ) ) );

Alternative option. In the following example, DATE_TRUNC retrieves the beginning of the current month, adds one month to retrieve the beginning of the next month, and then subtracts 1 day to determine the last day of the current month.

SELECT DATEADD('day', -1,
  DATEADD('month', 1,
  DATE_TRUNC('month', current_date())));

Get the last day of the previous month as a DATE value:

SELECT DATEADD(day, -1, DATE_TRUNC('month',current_date()) );

Get the current month of the year by name:

-- Output short English names, e.g. "Jan", "Dec", etc.
SELECT TO_VARCHAR(current_date(), 'Mon');

-- Output arbitrary, explicitly-provided month names
SELECT DECODE(EXTRACT('month',current_date()),
           1, 'January',
           2, 'February',
           3, 'March',
           4, 'April',
           5, 'May',
           6, 'June',
           7, 'July',
           8, 'August',
           9, 'September',
           10, 'October',
           11, 'November',
           12, 'December');

Get the date for Monday in the current week:

SELECT DATEADD('day', (EXTRACT('dayofweek_iso', current_date()) * -1) +1 , current_date() );

Get the date for Friday in the current week:

SELECT DATEADD('day', (5 - EXTRACT('dayofweek_iso', current_date()) ) , current_date() );

Get the date for the first Monday in the current month using the DATE_PART function:

.. code-block:: sfexample
SELECT DATEADD(
day, MOD( 7 + 1 - DATE_PART(‘dayofweek_iso’, DATE_TRUNC(‘month’, current_date()) ), 7), DATE_TRUNC(‘month’, current_date()));

Note

In the above query, the 1 value in 7 + 1 translates to Monday. To retrieve the date for the first Tuesday, Wednesday, etc., substitute 2, 3, and so on, respectively, through 7 for Sunday.

Get the first day of the current year as a DATE value:

SELECT DATE_TRUNC('year', current_date());

Get the last day of the current year as a DATE value:

SELECT DATEADD('day', -1,
       DATEADD('year', 1,
       DATE_TRUNC('year', current_date())));

Get the last day of the previous year as a DATE value:

SELECT DATEADD('day', -1, DATE_TRUNC('year',current_date()) );

Get the first day of the current quarter as a DATE value:

SELECT DATE_TRUNC('quarter',current_date());

Get the last day of the current quarter as a DATE value:

SELECT DATEADD('day', -1,
  DATEADD('month', 3,
  DATE_TRUNC('quarter', current_date())));

Get the date and timestamp for midnight in the current day:

SELECT DATE_TRUNC('day', current_timestamp());

+----------------------------------------+
| DATE_TRUNC('DAY', CURRENT_TIMESTAMP()) |
|----------------------------------------|
| Wed, 07 Sep 2016 00:00:00 -0700        |
+----------------------------------------+

Incrementing Date and Time Values

Add two years to the current date and time:

SELECT DATEADD(year, 2, current_date());

Add two years to the current date and time:

SELECT DATEADD(day,2,current_date());

Add two hours to the current date and time:

SELECT DATEADD(hour,2,current_timestamp());

Add two minutes to the current date and time:

SELECT DATEADD(minute,2,current_timestamp());

Add two seconds to the current date and time:

SELECT DATEADD(second,2,current_timestamp());

Converting Valid Character Strings to Dates, Times, or Timestamps

In most use cases, Snowflake correctly handles date and timestamp values formatted as strings. In certain cases, such as string-based comparisons or when a result depends on a different timestamp format than is set in the session parameters, we recommend explicitly converting values to the desired format to avoid unexpected results.

For example, without explicit casting, comparing string values produces string-based results:

-- Note the column data type is string
CREATE OR REPLACE TABLE timestamps(timestamp1 string);

INSERT INTO timestamps VALUES
('Fri, 05 Apr 2013 00:00:00 -0700'),
('Sat, 06 Apr 2013 00:00:00 -0700'),
('Sat, 01 Jan 2000 00:00:00 -0800'),
('Wed, 01 Jan 2020 00:00:00 -0800');

-- Comparison without explicit casting
SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01';

+------------+
| TIMESTAMP1 |
|------------|
+------------+

-- Comparison with explicit casting to date
SELECT * FROM timestamps WHERE timestamp1 < '2014-01-01'::date;

+---------------------------------+
| DATE1                           |
|---------------------------------|
| Fri, 05 Apr 2013 00:00:00 -0700 |
| Sat, 06 Apr 2013 00:00:00 -0700 |
| Sat, 01 Jan 2000 00:00:00 -0800 |
+---------------------------------+

For more information about conversion functions, see Date, Time, and Binary Formats in Conversion Functions.

Applying Date Arithmetic to Valid Date Strings

Add five days to the date expressed in a string:

SELECT DATEADD('day',5,TO_TIMESTAMP ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss') );

+--------------------------------------------------------------------------------+
| DATEADD('DAY',5,TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') ) |
|--------------------------------------------------------------------------------|
| Thu, 17 Jan 2016 00:00:00 -0800                                                |
+--------------------------------------------------------------------------------+

Calculate the difference in days between the current date and the date expressed in a string using the DATEDIFF function:

-- Using the TO_TIMESTAMP function
SELECT DATEDIFF('day', TO_TIMESTAMP ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss') , current_date() );

+-------------------------------------------------------------------------------------------------+
| DATEDIFF('DAY', TO_TIMESTAMP ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS') , CURRENT_DATE() ) |
|-------------------------------------------------------------------------------------------------|
|                                                                                            240  |
+-------------------------------------------------------------------------------------------------+

-- Using the TO_DATE function
SELECT DATEDIFF('day', TO_DATE ('12-jan-2016 00:00:00','dd-mon-yyyy hh:mi:ss'), current_date() );

+-------------------------------------------------------------------------------------------+
| DATEDIFF('DAY', TO_DATE ('12-JAN-2016 00:00:00','DD-MON-YYYY HH:MI:SS'), CURRENT_DATE() ) |
|-------------------------------------------------------------------------------------------|
|                                                                                      240  |
+-------------------------------------------------------------------------------------------+

Calculating Differences Between Dates or Times

Calculate the difference between the current date and the date in three years:

SELECT DATEDIFF(year, current_date(),
  DATEADD(year, 3, current_date() ) );

Calculate the difference between the current date and the date in three months:

SELECT DATEDIFF(month, current_date(),
  DATEADD(month, 3, current_date()) );

Calculate the difference between the current date and the date in three days:

SELECT DATEDIFF(day, current_date(),
  DATEADD(day, 3, current_date()) );

Calculate the difference between the current time and the time in three hours:

SELECT DATEDIFF(hour, current_timestamp(),
  DATEADD(hour, 3, current_timestamp()) );

Calculate the difference between the current time and the time in three minutes:

SELECT DATEDIFF(minute, current_timestamp(),
  DATEADD(minute, 3, current_timestamp()) );

Calculate the difference between the current time and the time in three seconds:

SELECT DATEDIFF(second, current_timestamp(),
  DATEADD(second, 3, current_timestamp()) );

Creating Yearly Calendar Views

CREATE OR REPLACE VIEW
calendar_2016 as SELECT n, theDate,
  DECODE (EXTRACT('dayofweek',theDate),
    1 , 'Monday',
    2 , 'Tuesday',
    3 , 'Wednesday',
    4 , 'Thursday',
    5 , 'Friday',
    6 , 'Saturday',
    0 , 'Sunday'
    ) theDayOfTheWeek,
DECODE (EXTRACT(month FROM theDate),
    1 , 'January',
    2 , 'February',
    3 , 'March',
    4 , 'April',
    5 , 'May',
    6 , 'June',
    7 , 'July',
    8 , 'August',
    9 , 'september',
    10, 'October',
    11, 'November',
    12, 'December'
    ) theMonth,
  EXTRACT(year from theDate) theYear
  FROM
  (SELECT
    row_number() OVER (order by seq4()) AS n,
    DATEADD(day, row_number() OVER (order by seq4())-1, TO_DATE('2016-01-01'))
    AS theDate
    FROM table(generator(rowCount => 365)))  ORDER BY n ASC;

SELECT * from CALENDAR_2016;

+-----+------------+-----------------+-----------+---------+
|   N | THEDATE    | THEDAYOFTHEWEEK | THEMONTH  | THEYEAR |
|-----+------------+-----------------+-----------+---------|
|   1 | 2016-01-01 | Friday          | January   |    2016 |
|   2 | 2016-01-02 | Saturday        | January   |    2016 |
  ..
| 364 | 2016-12-29 | Thursday        | December  |    2016 |
| 365 | 2016-12-30 | Friday          | December  |    2016 |
+-----+------------+-----------------+-----------+---------+