Time function converts large 6 digit numbers

[venkatkp]

I have a qlikview time function where i have the following lines of code. 
 
text(time(time#(TRANSMIT_TIME,'hhmmss'),'hh:mm:ss')) as TRANSMIT_TIME,
 
where the Transmit_time could be values like 870800 and this returns 15:08:00 , also values likes 218400 returns 22:24:00
can i get to know a bit more on how this time works? we are working on a similar sql function or write a stored proc if there is a calculation behind it.
 

can any one help me understand

[marcus_sommer]

What is your expected output to your input respectively what's wrong with the current results?

- Marcus

[venkatkp]

Hi Marcus 

There is nothing wrong with the time function in qlikview.

We are trying to workout whats the logic behind converting the 6 digit number in qlik. 

for ex : if you give 093513 to the field, we convert this to a number using time# and then to time with the format hh:mm:ss so the output would be 9:35:13. but i can see qlik converting  numbers like  870800 and this returns 15:08:00.  The same number when i try in SQL errors since the limit is only 23:59:59. 

i am trying to understand whats the time function logic behind converting this number  870800, i believe some calculation is applied. 

[MarcoWedel]

I think the more interesting question is not about QlikView interpreting 870800 as a time value but rather about your data source generating these values ...

[venkatkp]

True. i probably need to go back and ask the mainframe teams on why such numbers are sent. 

but further digging i see that after the 23:59:59 when it turns to 240000, the time starts with 00:00:00, and again when it turns to 480000 and so on...

TransmitTime:
LOAD * INLINE [
transmit_time, City
870800, Melbourne
235959, Sydney

095711, Singapore

240000, Minneapolis

720000, Barcelona
];

TestLoad:
LOAD
transmit_time,
text(time(time#(transmit_time,'hhmmss'),'hh:mm:ss')) as ActualTime,
City
Resident TransmitTime;

[marcus_sommer · Accepted solution]

Behind the converting is an interval() logic which doesn't stop by 24 hours else adding them to days + time. In your case of:  870800 returns 15:08:00 it means 87 / 24 = 3 (days) with a remain of 15 hours. The value itself remained here a time and not a timestamp like interval() would do - just replace time() with num() to see the numeric value behind the formatting.

Beside this I would tend to remove the outer text() because with a string you couldn't calculate ...

- Marcus

[venkatkp]

Thanks Marcus for the response. it helped understanding a bit. but since my requirement is to re-write, i tried my best to find a function in sql but not much luck.

At times we really go hard way to get this... this is what i wrote below

DECLARE @Time1 int = 730011 ;
--SELECT left(@Time1,1),@Time1,LEN(@Time1)
--select cast(substring(cast(@Time1 AS varchar),3,2) as int)
--Select case when len(@Time1) = 5 then STUFF(@Time1,2,2,77) else @Time1 end
select case when len(T1.hh) = 1 then '0'+T1.hh else T1.hh end as HH,case when len(T1.mm) = 1 then '0'+T1.mm else T1.mm end as MM ,T1.ss as SS from
(
select
CASE WHEN LEN(@Time1)>5 THEN
case when cast(substring(cast(@Time1 AS varchar),3,2) as int) > 59
then
( ((case when (left(@Time1,2) < 24 ) then left(@Time1,2)
when (left(@Time1,2) > 23 and left(@Time1,2) < 48 ) then left(@Time1,2) % 24
when (left(@Time1,2) >= 48 and left(@Time1,2) < 72 ) then left(@Time1,2) % 48
when (left(@Time1,2) >= 72 and left(@Time1,2) < 96 ) then left(@Time1,2) % 72
when (left(@Time1,2) >= 96 and left(@Time1,2) < 120 ) then left(@Time1,2) % 96
End))+1

)

when cast(substring(cast(@Time1 AS varchar),3,2) as int) < 60
then
( ((case when (left(@Time1,2) < 24 ) then left(@Time1,2)
when (left(@Time1,2) > 23 and left(@Time1,2) < 48 ) then left(@Time1,2) % 24
when (left(@Time1,2) >= 48 and left(@Time1,2) < 72 ) then left(@Time1,2) % 48
when (left(@Time1,2) >= 72 and left(@Time1,2) < 96 ) then left(@Time1,2) % 72
when (left(@Time1,2) >= 96 and left(@Time1,2) < 120 ) then left(@Time1,2) % 96
End))

)

end
WHEN LEN(@Time1)<6 THEN
case when cast(substring(cast(@Time1 AS varchar),2,2) as int) > 59
then (left(@Time1,1)) +1
else left(@Time1,1) end
END
as hh

,(CASE WHEN LEN(@Time1)>5 then case
when cast(substring(cast(@Time1 AS varchar),3,2) as int) > 59 then cast(substring(cast(@Time1 AS varchar),3,2) as int) - 60 else substring(cast(@Time1 AS varchar),3,2)
End
WHEN LEN(@Time1)= 5 then case
when cast(substring(cast(@Time1 AS varchar),2,2) as int) > 59 then cast(substring(cast(@Time1 AS varchar),2,2) as int) - 60 else substring(cast(@Time1 AS varchar),2,2)
End END )
as mm

,case when right(@Time1,2) = 60 then 59 else right(@Time1,2) end as ss) T1

[marcus_sommer]

I think this is much more complex as needed. I would try to use a function like maketime() which isn't only within Qlik available else databases like MySQL as well as MS Office provide it. Therefore I could imagine that most databases have similar functions.

Even if not I wouldn't go with such large if-loops else solving it more mathematically. Here just a few ideas to extract some parts (with Qlik functions but similar ones should be also within most of the databases available):

floor(floor(870800/10000)/24) = days
mod(floor(870800/10000),24) = hours
mid(870800, len(870800)-3,2) = minutes
right(870800, 2) = seconds

and then the single parts could be added again, like:

(hours / 24) + (minutes/24/60) + (seconds/24/60/60)

to get a real numeric time-value which you could then format like you want.

- Marcus

[venkatkp]

The complex in my sql query is introduced since there are conditions that we may have to handle if the minutes digits are more than 59 (ex: 876380). so here we add an hour and then reduce the minute to 3 and again add a minute and reduce sceonds to 20 (16:04:20).

[marcus_sommer]

My example above will fetch this case, too. Just give it a trial with minute/seconds-values greater as 60, means:

(hours / 24) + (minutes/24/60) + (seconds/24/60/60)

with

time((15/ 24) + (63/24/60) + (80/24/60/60))

- Marcus