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:
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!
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:
0 comments
