How to convert seconds to hh:mm:ss format?

[kriticar]

Hi,

I have a table with "seconds" as integer field.

I would like to convert this integer to string in format "hh:mm:ss".

I don't se function that could help.

How to do that?

Does someone have an idea?

Regards.

[markthedak]

Probably something of this?

https://clickhouse.yandex/docs/en/query_language/functions/date_time_functions/#formatdatetimetime-format91-timezone93

[kriticar]

I don't have time, I have an integer.

[markthedak]

don't know if there's an easier/prettier way, but you can cast the integer to date and format it afterwards.

https://clickhouse.yandex/docs/en/query_language/functions/type_conversion_functions/#castx-t

[kriticar]

How to cast integer to datetime?

[markthedak]

SELECT CAST(1342343433 AS DATETIME);

--> 2012-07-15 11:10:33

[kriticar]

Thanks, but this one doesn't work for me:

select formatDateTime(now(), '%m/%d/%Y %H:%M:%S')

Unknown function formatDateTime, e.what() = DB::Exception

[kriticar]

ClickHouse client version 1.1.54362.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.54362.

[kriticar]

Looks like my clickhouse server version is to low.

I tried what you have suggested on another server and it works.

Thank you very very much.

Best regards.

[Denis Zhuravlev]

select 360001 s, intDiv(s, 3600) h, intDiv(s-3600*h, 60) m, s-3600*h-m*60 ss
┌──────s─┬───h─┬─m─┬─ss─┐
│ 360001 │ 100 │ 0 │  1 │
└────────┴─────┴───┴────┘

[kriticar]

Yes, at first, this was my idea too.

But, when I had to implement it to convert table field with seconds to format HH:MM:SS (with leading zeros), things went pretty ugly.

for example, Conversation_Time is in ms:

,           toUInt32(Conversation_Time/1000) DurationS
,            concat(if(intDivOrZero(toUInt32(Conversation_Time/1000), 3600)=0, '00:',
                if(intDivOrZero(toUInt32(Conversation_Time/1000), 3600) < 10, concat('0', toString(intDivOrZero(toUInt32(Conversation_Time/1000), 3600)), ':'), concat(toString(intDivOrZero(toUInt32(Conversation_Time/1000), 3600)), ':'))
            ),
            if(intDivOrZero(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600), 60)=0, '00',
                if(intDivOrZero(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600), 60) < 10, concat('0', toString(intDivOrZero(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600), 60)), ':'), concat(toString(intDivOrZero(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600), 60)), ':'))
            ) ,
            if(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600) - (intDivOrZero(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600), 60) * 60)=0, '00',
                if(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600) - (intDivOrZero(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600), 60) * 60) < 10, concat('0', toString(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600) - (intDivOrZero(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600), 60) * 60))), toString(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600) - (intDivOrZero(toUInt32(Conversation_Time/1000) - (intDivOrZero(toUInt32(Conversation_Time/1000), 3600) * 3600), 60) * 60)))
            )
            ) hms   

which can be replaced with:

,             formatDateTime(CAST(toUInt32(Conversation_Time/1000) AS DATETIME), '%H:%M:%S')

[Mikhail Filimonov]

OMG :)

May be that will help?

SELECT substring(toString(now()), 12, 8)

Also - it can sounds a bit stupid but will work perfect:you can just create a table or dictionary with key from range 0..86399 and values in range '00:00:00' .. '23:59:59'.

[kriticar]

SELECT substring(toString(now()), 12, 8)

is even simper solution than

formatDateTime(CAST(toUInt32(Conversation_Time/1000) AS DATETIME), '%H:%M:%S')

Thanks Mikhail.