Truncate timestamp to custom unit of time in SQL Snowflake

user_8091170 0
Viewed 258

Function DATE_TRUNC in Snowflake (Docs here) allows you to truncate a given timestamp to a given default unit of time, being available:

 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE' and 'SECOND'

As an example:

DATE_TRUNC('MINUTE', '2015-05-08T23:39:35.123') --> 2015-05-08T23:39:00.000'

How would we do it if we want to truncate a date to a customize unit of time as in:

 '5 MINUTE' OR '30 SECONDS'

As an example these customized times would give:

DATE_TRUNC('30 SECONDS', '2015-05-08T23:39:35.123') --> 2015-05-08T23:39:30.000'
DATE_TRUNC('30 SECONDS', '2015-05-08T23:39:15.123') --> 2015-05-08T23:39:00.000'

DATE_TRUNC('5 MINUTES', '2015-05-08T23:39:35.123') --> 2015-05-08T23:35:00.000'
DATE_TRUNC('5 MINUTES', '2015-05-08T23:34:35.123') --> 2015-05-08T23:30:00.000'

Is there a workaround or alternative function to get these results?

Thanks

snowflake-cloud-data-platform truncate timestamp
3 Answers

Change to epoch seconds, division, floor, multiply

thus for 30 seconds steps:

SELECT 
    TO_TIMESTAMP_NTZ('2015-05-08T23:39:35.123') as date
    ,DATE_PART(epoch_second, date) as epoch_sec
    ,floor(epoch_sec / 30) * 30 as epoch_in_30sec_steps
    ,TO_TIMESTAMP_NTZ(epoch_in_30sec_steps) as time_in_30sec_steps;

gives:

DATE EPOCH_SEC EPOCH_IN_30SEC_STEPS TIME_IN_30SEC_STEPS
2015-05-08 23:39:35.123 1431128375 1431128370 2015-05-08 23:39:30.000

and thus 5 minutes is:

TO_TIMESTAMP_NTZ(floor(epoch_sec / 300) * 300) as time_in_5min_steps
user_439920

What Simeon said.

You can put it in a nice little UDF like this

create or replace function trunc_by_seconds(num_seconds int, t timestamp_ntz(0))
returns timestamp_ntz(0)
as $$ to_timestamp_ntz(num_seconds * floor(date_part(epoch_second, t) / num_seconds))$$;

and then just use

select trunc_by_seconds(30, ...)
user_14572580

MOD() approach, however the epoch_sec appears more elegant, less verbose and equally efficient.

I just get a kick out of finding alternative solutions :-)

SELECT
  TO_TIMESTAMP_NTZ('2015-05-08T23:09:35.023') as date
   ,DATE_TRUNC( 'second',date) some_date_time
   ,TIMESTAMPADD(second, -MOD(DATE_PART (second,some_date_time),30), some_date_time)   as Time_In_30sec_Steps

enter image description here

Rare would be the SQL question be with only one answer.

user_13524900
Related