CODEWITHSUNDEEP
snowflake-cloud-data-platform
Minh-Long Luu
Minh-Long Luu

Reputation: 2731

Snowflake: calculate seconds difference between date and start of that date

Let’s say I have a table that has one column as timestamp ntz. I want to perform a select that calculates the number of seconds difference between that moment in the table vs the start of that date. For pure timestamp type I can just use the timediff function:

select timediff(second, 
    ‘2022-01-01 00:00:05’::timestamp_ntz, 
    ‘2022-01-01 00:00:00’::timestamp_ntz
)

Which returns 5.

How can I do it with a value in a table, for example:

select timediff(second, my_date, my_date_startofdate)

Where my_date_startofdate will be converted to that day but at time 00:00:00.

Upvotes: 0

Views: 904

Answers (1)

Mike Walton
Mike Walton

Reputation: 7339

Use date_trunc. This function takes a timestamp or date and truncates it to whatever element you want. So, if you take your timestamp column and trunc it day, it removes the time elements and gives you the beginning of the day.

select timediff(second, date_trunc('day',my_date), my_date)

https://docs.snowflake.com/en/sql-reference/functions/date_trunc.html

Upvotes: 1

Related Questions