MySQL’s sec_to_time() function in Redshift
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 Periscope. 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:
That's it! Happy Periscoping!