Why are we using two mods for timestamp_diff of seconds in time

Question

As a part of a course, I have been trying to analyze a dataset of fitness device which has timestamps. I'm trying to take a difference of time in order to calculate no. of hours a user is sleeping. And for that I came across this code -

SELECT
   Id,
   TIME( TIMESTAMP_DIFF(MAX(date),MIN(date),HOUR),
     MOD(TIMESTAMP_DIFF(MAX(date),MIN(date),MINUTE),60),
     MOD(MOD(TIMESTAMP_DIFF(MAX(date),MIN(date),SECOND),3600),60) )AS time_sleeping

My question is why are we using "MOD" two times in the last line of the query when seconds can be converted from hours by only dividing by 3600? PS :- I'm new to SQL so ignore my silly question, if in case it is :)Thank you

Answer 1

You are right, a the two mod can be combinded. Please check yourself:

SELECT
  x,
  MOD(MOD(x,60),3600) a,
  MOD(x,60) b
FROM
  UNNEST(GENERATE_ARRAY(-4000,4000)) x
order by abs(a-b) desc

For a better time conversion see here:

SELECT
  TIME(
    TIMESTAMP_SECONDS(
#     MOD( 
       TIMESTAMP_DIFF(
         TIMESTAMP("2022-09-02 09:30:11"),
         TIMESTAMP("2022-09-01 23:00:02"),
         second
         )
#        ,3600*24      )
    )
  )