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.

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

Copy

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

Copy

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
FROM utctime;

Copy

Inserting Valid Date Strings Into Date Columns in a Table¶

This example show how to insert a value into a DATE column.

CREATE TABLE my_table(id INTEGER, date1 DATE);
-- July 23, 2016.
INSERT INTO my_table(id, date1) VALUES (1, TO_DATE('2016.07.23', 'YYYY.MM.DD'));
-- NULL.
INSERT INTO my_table(id) VALUES (2);

Copy

SELECT id, date1 
    FROM my_table 
    ORDER BY id;
+----+------------+
| ID | DATE1      |
|----+------------|
|  1 | 2016-07-23 |
|  2 | NULL       |
+----+------------+

Copy

The TO_DATE function accepts TIMESTAMP values and even strings in TIMESTAMP format, but discards the time information (hours, minutes, etc.).

INSERT INTO my_table(id, date1) VALUES
    (3, TO_DATE('2020.02.20 11:15:00', 'YYYY.MM.DD HH:MI:SS')),
    (4, TO_TIMESTAMP('2020.02.24 04:00:00', 'YYYY.MM.DD HH:MI:SS'));

Copy

SELECT id, date1 
    FROM my_table 
    WHERE id >= 3;
+----+------------+
| ID | DATE1      |
|----+------------|
|  3 | 2020-02-20 |
|  4 | 2020-02-24 |
+----+------------+

Copy

If you insert a DATE that was defined with only a time, then the default date is January 1, 1970.

INSERT INTO my_table(id, date1) VALUES 
    (5, TO_DATE('11:20:30', 'hh:mi:ss'));

Copy

SELECT id, date1 
    FROM my_table 
    WHERE id = 5;
+----+------------+
| ID | DATE1      |
|----+------------|
|  5 | 1970-01-01 |
+----+------------+

Copy

When you retrieve DATE information, you can format it as a TIMESTAMP if you wish:

SELECT id, TO_VARCHAR(date1, 'dd-mon-yyyy hh:mi:ss') AS date1
    FROM my_table 
    ORDER BY id;
+----+----------------------+
| ID | DATE1                |
|----+----------------------|
|  1 | 23-Jul-2016 00:00:00 |
|  2 | NULL                 |
|  3 | 20-Feb-2020 00:00:00 |
|  4 | 24-Feb-2020 00:00:00 |
|  5 | 01-Jan-1970 00:00:00 |
+----+----------------------+

Copy

Retrieving the Current Date and Time¶

Get the current date as a DATE value:

SELECT current_date();

Copy

Get the current date and time as a TIMESTAMP value:

SELECT current_timestamp();

Copy

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());

Copy

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');

Copy

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());

Copy

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());

Copy

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

Copy

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());

Copy

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()) ) ) );

Copy

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())));

Copy

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

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

Copy

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');

Copy

Get the date for Monday in the current week:

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

Copy

Get the date for Friday in the current week:

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

Copy

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

SELECT DATEADD(
   day,
   MOD( 7 + 1 - DATE_PART('dayofweek_iso', DATE_TRUNC('month', current_date()) ), 7),
   DATE_TRUNC('month', current_date()));

Copy

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());

Copy

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

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

Copy

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

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

Copy

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

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

Copy

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

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

Copy

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

Copy

Incrementing Date and Time Values¶

Add two years to the current date:

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

Copy

Add two days to the current date:

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

Copy

Add two hours to the current date and time:

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

Copy

Add two minutes to the current date and time:

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

Copy

Add two seconds to the current date and time:

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

Copy

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

Copy

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

Applying Date Arithmetic to 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                                                |
+--------------------------------------------------------------------------------+

Copy

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

Copy

Add one day to a specified date:

SELECT TO_DATE('2019-01-15') + 1;

+---------------------------+
| TO_DATE('2019-01-15') + 1 |
|---------------------------|
| 2019-01-16                |
+---------------------------+

Copy

Subtract 9 days from the current date (e.g. Jan 21, 2019):

SELECT CURRENT_DATE() - 9;

+--------------------+
| CURRENT_DATE() - 9 |
|--------------------|
| 2019-01-12         |
+--------------------+

Copy

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() ) );

Copy

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

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

Copy

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

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

Copy

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

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

Copy

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

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

Copy

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

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

Copy

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

Copy