Convert Timestamp

[manojkulkarni]

Hi All,

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

Timestamp value is :

1435449600

[Anonymous · Accepted solution]

Looks like a Unix timestamp, if so then try :

     timestamp(1435449600/ 86400 + 25569)

[sasikanth]

Hi ,

Try some thing like

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

[manojkulkarni]

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

[tamilarasu]

Hi Manoj,

Try,

=Timestamp(1435449600/86400+25569)

Timestamp(Fieldname/86400+25569) as DateTime

[manojkulkarni]

Nagaraj , what are these values ?

[Anonymous]

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

[HirisH_V7]

Hi

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

[tamilarasu]

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

[Peter_Cammaert]

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.