TIME() issue

Database
Enthusiast

TIME() issue

Hi, i have a field float type "TimeFld"  and i have to calculate the difference between the "Logoff" and "Logon" time for every session, see the dbc.EventLog table.

I tryed to subtract the float and later cast it to time type but there is something wrong.

I know that with the function TIME() i can do the right difference:

sel (TIME 'hh:mi:ss.ss' - TIME 'hh:mi:ss.ss') hour to second(2);

Sel TIME 'hh:mi:ss.ss';

so i convert the field float type in to char(11) and i obtain a literal time string but when i pass it at the function TIME(), return an error:

"expected something between 'TIME' keyword and 'CASE' keyword"

i also tryed to concatenate:  '

at the beginning and at the end:

TIME ''''|| TimeLiteral ||''''

but it's wrong:  "Invalid TIME Literal".

This is the query:

sel

TimeFld as FloatTime,

TIME

''''||case characters( substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 8,2 ) )

when 0 then '00'

when 1 then '0'||substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 8,2 )

else substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 8,2 )

END

||':'||

case characters (substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 6,2 ) )

when 0 then '00'

when 1 then '0'||substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 6,2 )

else substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 6,2 )

END

||':'||

case characters( substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 4,2 ) )

when 0 then '00'

when 1 then '0'||substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 4,2 )

else substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 4,2 )

END

||

substr(cast(cast(TimeFld as decimal (9,2)) as char(9)),characters (trim(cast(cast(TimeFld as decimal (9,2)) as char(9)))) - 2 )

||''''

as CharTime1

sample 5

from dbc.EventLog ;

Some one can help me?

TNX

knowledge

8 REPLIES
Junior Contributor

Re: TIME() issue

A literal is a hard-coded value, you need a CAST.

And you probably need to include the datefld, too:

CAST (datefld AS TIMESTAMP(2)) + CAST(CAST(timefld AS CHAR(11)) AS INTERVAL HOUR TO SECOND)

Dieter

Enthusiast

Re: TIME() issue

Hi dnoeth

i need to calculate the time between the LogOn and LogOff about one SessionNo in the dbc.EventLog.

I think to use the difference with (TIME 'hh:mi:ss.ss' - TIME 'hh:mi:ss.ss') hour to second(2).

I use this query:

sel    A.SessionNo,sum(case    when A.Event= 'Logon'

then (-(TIME CAST (datefld AS TIMESTAMP(2)) + CAST(CAST(timefld AS CHAR(11)) AS INTERVAL HOUR TO SECOND)))

else    (TIME CAST (datefld AS TIMESTAMP(2)) + CAST(CAST(timefld AS CHAR(11)) AS INTERVAL HOUR TO SECOND))  end    )  as TotTime

from    

(

sel    SessionNo, DateFld,TimeFld ,

Event

from    dbc.EventLog

where        

DateFld =current_date and

Event in ('Logon','Logoff')

) as A

group    by 1;

but  there is an error:

expected something between the 'TIME' keyword and and the 'CAST' keyword.

how can i solve it?

tnx.

knowledge

Enthusiast

Re: TIME() issue

i tryed this, it's correct???

sel    A.SessionNo,sum(case    when A.Event= 'Logon'

then (-(cast( cast(A.TimeFld as integer)/3600 as interval hour(4)) +

cast( cast(A.TimeFld as integer) mod 3600 as interval second(4,0))

))

else    (cast( cast(A.TimeFld as integer)/3600 as interval hour(4)) +

cast( cast(A.TimeFld as integer) mod 3600 as interval second(4,0)))  end    )  as TotTime

from    

(

sel    SessionNo, TimeFld ,

Event

from    dbc.EventLog

where    DateFld = current_date     and    

Event in ('Logon','Logoff')   

) as A

group    by 1

Junior Contributor

Re: TIME() issue

eventlog.timefld just looks like a TIME, but it isn't. It's a FLOAT with a FORMAT hh:mi:ss.

You already have a TIMESTAMP when you use my calculation, there's no need to add that TIME in front of it. As i already wrote: TIME '....' is only for literals:

sel    A.SessionNo,
min(case when A.Event= 'Logoff'
then CAST (datefld AS TIMESTAMP(2)) + CAST(CAST(timefld AS CHAR(11)) AS INTERVAL HOUR TO SECOND) end)
-min(case when A.Event= 'Logon'
then CAST (datefld AS TIMESTAMP(2)) + CAST(CAST(timefld AS CHAR(11)) AS INTERVAL HOUR TO SECOND) end)
hour(4) to second
as TotTime
from
(
sel SessionNo, DateFld,TimeFld ,
Event
from dbc.EventLog
where
DateFld =current_date and
Event in ('Logon','Logoff')
) as A
group by 1;

Dieter

Enthusiast

Re: TIME() issue

hi Dieter,

i'm sorry for my confusion and inattention regard the TIME/TIMESTAMP...

what you did it's perfect!

thx a lot.

knowledge

Enthusiast

Re: TIME() issue

Hi Dieter,

aren't the "DateFld,TimeFld" fields evaluated at log-time?

Shouldn't it be correct to use the "LogonDate, LogonTime" fields from the EventLog table to get the logon time, instead of using "DateFld,TimeFld" while filtering for "Event='Logon'"?

If I am right, then the SQL could be rewritten like:

SEL    
SessionNo
,Event
,CAST (LogonDate AS TIMESTAMP(2)) + CAST(CAST(LogonTime AS CHAR(11)) AS INTERVAL HOUR TO SECOND) AS LogonTimeStamp
,CAST (datefld AS TIMESTAMP(2)) + CAST(CAST(timefld AS CHAR(11)) AS INTERVAL HOUR TO SECOND) AS LogoffTimeStamp
,LogoffTimeStamp-LogonTimeStamp HOUR(4) TO SECOND AS TotTime
FROM dbc.EventLog
WHERE DateFld ='2012-04-25'
AND Event IN ('Logoff','Forced Off ')

although sessions still open wouldn't pop-up... for which case, it may be good to wrap the above in a subquery like you did.

Junior Contributor

Re: TIME() issue

Hi Daniele,

i just did a copy&paste, of course you don't need the Derived Table and the aggregate, but there's a minor difference: When you compare LogonTime and timeFld for "Logon" events you'll see timeFld always a bit later than LogonTime. So your calculation is not exactly the same, but probably more correct :-)

Dieter

Enthusiast

Re: TIME() issue

hi Dieter and Daniele,

i'm very happy of your lesson, i have learned a lot and i changed this row:

,LogoffTimeStamp-LogonTimeStamp HOUR(4) TO SECOND AS TotTime

to:

,LogoffTimeStamp-LogonTimeStamp DAY(2) TO SECOND AS TotTime

some times some ETL USR take more that 1 day!  :D

thx for your time.

knowledge.