1

I have a sql query on teradata for difference between to timestamp fields

TO_CHAR(MAX(BE.E_END_DATETIME_PST)-MIN(BE.E_START_DATETIME_PST),'DD:HH:MI:SS') END AS "TIME_BTWN_CASE_START&END"

example: (1/16/2021 09:56:05.882000)-(1/1/2021 08:09:49.166000) result: 15:01:46:16

I am trying to write a similar query on snowflake to get the same results. But, my results are wrong and are not in the desired format DD:HH:MI:SS

TO_CHAR(TIMESTAMPDIFF(seconds,min(BE.E_START_DATETIME_PST), max(BE.E_END_DATETIME_PST) )%60 || ' seconds ' || TIMESTAMPDIFF(minutes,min(BE.E_START_DATETIME_PST), max(BE.E_END_DATETIME_PST) )%60|| ' minutes ' || TIMESTAMPDIFF(hours,min(BE.E_START_DATETIME_PST), max(BE.E_END_DATETIME_PST) )% 24 || ' hours ' || TIMESTAMPDIFF(days,min(BE.ESTART_DATETIME_PST), max(BE.E_END_DATETIME_PST) )|| ' days ') END AS "TIME_BTWN_CASE_START&END"

example: (2021-10-19 07:15:45.809)-(2021-03-25 09:56:13.277) result: 32 seconds 19 minutes 22 hours 208 days

Can someone please help me with this?

user18226932
  • 11
  • 1
  • Your problem is that `TIMESTAMPDIFF` effectively first truncates the values to the indicated precision (e.g. day) and then does the subtraction. You could use the highest precision (seconds) for all the TIMESTAMPDIFFs and scale appropriately, e.g. `TRUNC(TIMESTAMPDIFF(seconds,min(BE.E_START_DATETIME_PST), max(BE.E_END_DATETIME_PST) )/3600)%24 || ' hours '` – Fred Feb 17 '22 at 18:13

2 Answers2

0

You could transform to seconds from epoch and then do some math:

with data as (
    select '2021-10-19 07:15:45.809'::timestamp ts1, '2021-03-25 09:56:13.277'::timestamp ts2
)

select 
    date_part(epoch_second, ts1) - date_part(epoch_second, ts2) ms_diff
    , floor(ms_diff/(24*3600)) days
    , floor((ms_diff-(24*3600)*days)/3600) hours
    , floor((ms_diff-(24*3600)*days-3600*hours)/60) minutes
    , floor((ms_diff-(24*3600)*days-3600*hours-minutes*60)) seconds
    , days ||':'|| hours ||':'|| minutes ||':'|| seconds in_format
from data

enter image description here

Then encapsulate in an UDF for easy re-use.

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
0

You could re-jig this a little

CASE WHEN TIMESTAMPDIFF(hour,timestamp_1, timestamp_2)<24 THEN 0 ELSE FLOOR(TIMEDIFF(second,timestamp_1, timestamp_2)/86400)  END||'d '||time_from_parts(0,0,ABS(timestampdiff(second, timestamp_1, timestamp_2)))

This will give the result in the format: 0d 00:11:10

I take the absolute value also as in my case, timestamp_1 might be before or after timestamp_2 and I didn't want negative values

Dean Flinter
  • 654
  • 3
  • 6