'Truncate timestamp to custom unit of time in SQL Snowflake

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

timestampsnowflake-cloud-data-platformtruncate


Solution 1:[1]

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

Solution 2:[2]

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, ...)

Solution 3:[3]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Simeon Pilgrim
Solution 2 Marcin Zukowski
Solution 3

Related Questions