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 |
+---------------------------------+
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 |
+---------------------------------+
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;
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);SELECT id, date1 FROM my_table ORDER BY id; +----+------------+ | ID | DATE1 | |----+------------| | 1 | 2016-07-23 | | 2 | NULL | +----+------------+
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'));SELECT id, date1 FROM my_table WHERE id >= 3; +----+------------+ | ID | DATE1 | |----+------------| | 3 | 2020-02-20 | | 4 | 2020-02-24 | +----+------------+
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'));SELECT id, date1 FROM my_table WHERE id = 5; +----+------------+ | ID | DATE1 | |----+------------| | 5 | 1970-01-01 | +----+------------+
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 | +----+----------------------+
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:
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:
SELECT DATEADD(year, 2, current_date());
Add two days to the current date:
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 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 |
+--------------------------------------------------------------------------------+
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 |
+-------------------------------------------------------------------------------------------+
Add one day to a specified date:
SELECT TO_DATE('2019-01-15') + 1;
+---------------------------+
| TO_DATE('2019-01-15') + 1 |
|---------------------------|
| 2019-01-16 |
+---------------------------+
Subtract 9 days from the current date (e.g. Jan 21, 2019):
SELECT CURRENT_DATE() - 9;
+--------------------+
| CURRENT_DATE() - 9 |
|--------------------|
| 2019-01-12 |
+--------------------+
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 |
+-----+------------+-----------------+-----------+---------+