MySQL’s Sec_to_time() Function In Redshift

[intapiuser]

In MySQL, it’s easy to convert an integer representing a number of seconds into a string representing a human-readable amount of time.

This is especially useful when you want to compare two timestamps, for example if you wanted to know how much time elapses before a new user makes a purchase, you’d have a query similar to the following in Redshift:

select datediff('sec', created_at, first_purchase_at)
from users

Which may give results like this:

Which is great, but what does it mean? Wouldn't it be nice to have results like this: 

In MySQL, we can use SEC_TO_TIME() to do this conversion easily. In Redshift, we need another way. The following code makes use of concatenation and type casting to achieve the results we need!

select
  (datediff('sec', created_at, first_purchase_at)::varchar || ' second' )::interval
from
  users

This works by converting the number of seconds to a varchar type, then concatenating it with 'second' to create values like '16149765second' which can then be case directly to a Redshift Interval type, giving the format we like!

We can abstract this code into a Parameterized SQL Snippet for convenient future re-use throughout Sisense. To do that, create a new snippet with a title and sql code like the following:

When a chart or view references [sec_to_time(seconds_integer)], it will substitute 'seconds_integer' with the column or value provided by the query, and perform the transformation we just practiced.

For example, the following code would evaluate 9001 seconds to 2 hours, 30 minutes, and 1 second:

select [sec_to_time(9001)]