How to Query Date and Time in Snowflake 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' , '2020-04-18 12:04:00.000 -0700') -- output in seconds

Previous

Next

Ready for a modern SQL editor?