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:

select [sec_to_time(9001)]

 

 

That's it! Happy Periscoping!

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like3 Follow
  • 3 Likes
  • 2 mths agoLast active
  • 903Views
  • 1 Following