How to convert timestamp into Time and Hour

[#1]

All helps and tips I've found were complicated to understand (for me !). So here is a very easy example.

In my DB I have CDATETIME = 3671638531991000   ==> from a Firebird DB

In your Qlik upload Script :

     SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';  <=Default Qlik

     in a Load expression :

       Date(Timestamp(floor(CDATETIME / 86400000000))) as Datetmp,     ==> returns "05/05/2016  00:00:00"

       time(Frac(CDATETIME / 86400000000)) as Timetmp,                       ==> returns "19:35:32"

Hope this will help.

[vishsaggi]

What is that you want to display for Timetmp field?

May be this?

Timestamp(Frac(CDATETIME / 86400000000), 'hh:mm') AS Timetmp;  --> Returns 19:35

[#3]

Sorry, it wasn't a question but a tip.

[vishsaggi]

Oh ok. No problem.

[avinashelite]

Your using to different function that's why our seeing the change

frac returns the decimal part

frac(3671638531991000/86400000000) = 0.816342

floor eliminated the decimal part

floor(3671638531991000/86400000000) = 42495

because of which in the first case you get the time as 00 and get proper time in second case

Hope you got the answer