topic Re: Convert Timestamp in QlikView

Convert Timestamp

manojkulkarni — Wed, 06 Jan 2016 09:34:38 GMT

Hi All,

I have below timestamp details in one of the column, How to convert the value into Date & time ?

Timestamp value is :

1435449600

Re: Convert Timestamp

Anonymous — Wed, 06 Jan 2016 09:38:39 GMT

Looks like a Unix timestamp, if so then try :

timestamp(1435449600/ 86400 + 25569)

Re: Convert Timestamp

sasikanth — Wed, 06 Jan 2016 09:39:10 GMT

Hi ,

Try some thing like

TimeStamp(MakeDate(1970) + (1435449600/ (1000 * 60 * 60 *24)), 'MM/DD/YYYY hh:mm:ss.fff')

Re: Convert Timestamp

manojkulkarni — Wed, 06 Jan 2016 09:41:06 GMT

pls can you explain me why 86400 & 25569 is used ?

Re: Convert Timestamp

tamilarasu — Wed, 06 Jan 2016 09:41:51 GMT

Hi Manoj,

Try,

=Timestamp(1435449600/86400+25569)

Timestamp(Fieldname/86400+25569) as DateTime

Re: Convert Timestamp

manojkulkarni — Wed, 06 Jan 2016 09:42:37 GMT

Nagaraj , what are these values ?

Re: Convert Timestamp

Anonymous — Wed, 06 Jan 2016 09:44:10 GMT

Search for "Unix Timestamp" on this forum or the internet and you'll find plenty of hit / explanations.

Re: Convert Timestamp

HirisH_V7 — Wed, 06 Jan 2016 09:49:52 GMT

Check this,

Its a unix time stamp, unixtimestamp.com

T:
LOAD * ,
timestamp((Field/ 86400 + 25569),'DD/MM/YYYY h:mm:ss[.fff] TT') as TS,
date(floor(Field/ 86400 + 25569)) as Date,
time(frac(Field/ 86400 + 25569)) as Time
INLINE [
    Field
    1435449600
];

Output:

PFA,

Hope this Helps,

Regards,

Hirish

Re: Convert Timestamp

tamilarasu — Wed, 06 Jan 2016 09:50:44 GMT

86400 is the number of seconds in a day. 25569 is the number of days between 1 January 1900 and 1 January 1970.

Re: Convert Timestamp

Peter_Cammaert — Wed, 06 Jan 2016 09:50:44 GMT

Unix time stamps are specified in seconds since epoch (a specific starting date that has a value of 0 seconds) The values are used to convert seconds into a QlikView Timestamp.

Re: Convert Timestamp

manojkulkarni — Wed, 06 Jan 2016 09:56:14 GMT

Thanks bill.markham

It is working..

Re: Convert Timestamp

wuming79 — Mon, 22 Oct 2018 12:22:02 GMT

Hi, does this works for Qlik sense too? I tried the above but table shows blank when I tried to display them.