How to Query Date and Time in Snowflake
Get the date and time right now (where Snowflake is running):
select current_timestamp;
select getdate();
select systimestamp();
select localtimestamp;
Find rows between two dates or timestamps:
select *
from events
where event_date between '2020-04-18' and '2020-04-20';
-- can include time by specifying in YYYY-MM-DD hh:mm:ss format:
select *
from events
where event_date between '2020-04-18 12:00:00' and '2020-04-20 23:30:00';
Note: always put the dates in the 'between .. and' statement in ascending order(from smaller to larger date value)
Find rows created within the last week:
select *
from events
where event_date > (select dateadd(week, -1, getdate()));
Find events scheduled between one week ago and 3 days from now:
select *
from events
where event_date between (select dateadd(week, -1, getdate())) and (select dateadd(day, +3, getdate()));
Extracting part of a timestamp and returning an integer:
select day(getdate()); -- or month() or year()
select date_part(hour, getdate()); -- or hour, week, month, quarter, year
Extracting part of a timestamp and returning a string (e.g. "Feb" or "Mon"):
select dayname(getdate());
select monthname(getdate());
Get the day of the week from a timestamp:
select date_part(weekday, getdate()); -- returns 0-6 (integer), starting with 0 as Sunday
How to convert a timestamp to a unix timestamp:
select datediff(second, '1970-01-01' , current_timestamp())
To calculate the difference between two timestamps, convert them to unix timestamps then subtract:
select datediff(second, '1970-01-01' , '2020-04-20 00:00:00.000 -0700') - datediff(second, '1970-01-01
Previous
How to Group by Time