Title
#users-public
j

John Lewis

10/28/2022, 11:50 AM
Does anyone know how to produce a formatted string showing the difference between 2 timestamps in the format dd.HH🇲🇲ss where dd is the total no of days between the timestamps. dd could be be a value 0 to 99 days?
Alex Pelagenko

Alex Pelagenko

10/28/2022, 1:06 PM
with totalseconds as (
  select datediff('s', now(), '2022-01-01') as secs
)
select (secs / (24 * 60 * 60)) as days, 
(secs / 60 / 60 % 24) as hours,
(secs/ 60 % 60) as mins,
(secs % 60) as secs
from totalseconds
j

javier ramirez

10/28/2022, 2:42 PM
An alternative could be
2:42 PM
SELECT
  to_str(
    (
      CAST(
        datediff(
          's',
          to_timestamp('2020-01-23T10:00:00', 'yyyy-MM-ddTHH:mm:ss'),
          to_timestamp('2020-05-27', 'yyyy-MM-dd')
        )
      AS LONG )  * 1000000
    )
  , 
        'D.HH:mm:ss')
2:43 PM
First I am getting the datediff in seconds
2:43 PM
then I multiply times 1 million (need to cast as long, otherwise there’s integer overflow and results are wrong)
2:43 PM
so I have microseconds
2:44 PM
with microseconds I can just apply to_str to convert an epoch to a date
2:44 PM
and I use the string format D.HH🇲🇲ss
2:44 PM
D means the number of day in the year
2:45 PM
as long as you don’t go over 365 days difference that should work. You said the maximum was 99, so it should be fine (if not readable)
j

John Lewis

10/28/2022, 2:54 PM
Many thanks, that works great
j

javier ramirez

10/28/2022, 3:04 PM
a colleage told me we can skip the CAST if we just use * 1000000L
3:04 PM
to convert to long
3:05 PM
which makes it shorter and easier to read
3:05 PM
SELECT
  to_str(
    (      
        datediff(
          's',
          to_timestamp('2020-01-23T10:00:00', 'yyyy-MM-ddTHH:mm:ss'),
          to_timestamp('2020-05-27', 'yyyy-MM-dd')
        )
       * 1000000L
    )
  , 
        'D.HH:mm:ss')
j

John Lewis

10/28/2022, 3:06 PM
@javier ramirez just spotted that if the timestamps differ by less than 1 day then the results still show 1 day
3:06 PM
SELECT to_str( ( CAST( datediff( 's', to_timestamp('2022-10-04T13:04:59', 'yyyy-MM-ddTHH🇲🇲ss') , to_timestamp('2022-10-04T00:00:00', 'yyyy-MM-ddTHH🇲🇲ss') ) AS LONG ) * 1000000 ) , 'D.HH🇲🇲ss')
3:07 PM
sorry didnt mean to push to public again
j

javier ramirez

10/28/2022, 3:10 PM
you are right.. Didn’t notice this would always start on january 1st and not january 0 XD
3:11 PM
I guess then there is no solution but doing it in two steps, as initially suggested