cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
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)]
Rate this article:
Version history
Last update:
‎03-02-2023 09:34 AM
Updated by:
Contributors