CODEWITHSUNDEEP
sqlpresto
HaloKu
HaloKu

Reputation: 435

How to format seconds as integer into HH:MM:SS in Presto

I have a table as:

id   time_seconds
5             140
6               5
7           15000

I want to get it as:

id  time_format
5         23:52
6         00:05
7      04:10:00

Basically format of HH:MM:SS Now the thing is that I don't have many records with hours so the HH: should be in the output only if there are hours. Otherwise it should be just MM:SS

Presto has function that does similar thing SELECT parse_duration('60s'); But the output isn't what I need 0 00:01:00.000 and I'm not sure this is the way to use it?

This is what I did so far:

select id, concat(cast(time_seconds as varchar(10)),'s')
from mytable

not sure how to continue

Upvotes: 1

Views: 2809

Answers (2)

cssimsek
cssimsek

Reputation: 1265

This is not a particularly elegant approach, but it'll produce the targeted output format.

Without dropping the potential '00:' hours portion:

select regexp_replace(concat(
   cast(round(time_seconds/3600) as varchar),':',
   cast(round((time_seconds%3600)/60) as varchar),':',
   cast(round((time_seconds%3600)%60) as varchar)
),'(\d+)(:|\b)', match -> if(length(match)=1,'0'||match[1],match[1]) || match[2])

If '00:' hours must be dropped, then one could wrap the above in an additional: regexp_replace(<THE_INNER_RESULT>,'(^00:)(.*)','$2').

Note: This also doesn't satisfy the possible case of time_seconds>86400 (handling days) which @hkravitz points out in their comment.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can readily convert this to a time:

select time '00:00:00' + time_seconds * interval '1' second

I think that that is the best way to represent the value. Adding the hour conditionally seems like a bad idea -- it can lead to confusion.

I think the following will work on times:

select (case when time_seconds < 60 * 60
             then date_format(time '00:00:00' + time_seconds * interval '1' second, '%i:%s')
             else date_format(time '00:00:00' + time_seconds * interval '1' second, '%H:%i:%s')
        end) as hhmmss

Upvotes: 2

Related Questions