Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
tfidler240
Contributor II
Contributor II

Convert time in numeric field to hh:mm:ss

In our database time is a plain numeric field in 24 time:

13001 is 1:30 am and 1 sec

111621 is 11:16am and 21 seconds

211144 is 9:11pm and 44 seconds

Is there a function or process to convert them into a time field so they can be used for calculations?

Also how do I combine it with the date to match a full date & time stamp?

My date fields are CJULIAN which I have a conversion function.

3 Replies
Anonymous
Not applicable

This was the best I could come up with pre morning coffee:

Time(Time#(TIMEFIELD, if(len(TIMEFIELD)=5, 'hmmss', 'hhmmss')),'hh:mm:ss') as NewTime;

The expression checks the length of your timefield and formats accordingly.

So, 5 characters means 13001, or 'hmmss'.

6 characters indicates a 'hhmmss' format.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I've had my coffee. So how about:

=time(time#(right(0 & TIMEFIELD, 6),'hhmmss'))

-Rob

Not applicable

Rob,

you never got feedback from the OP, but I had the same question and can confirm your solution works, in case you were wondering.

Thanks!