topic Re: Linux Date Conversion in QlikView

Linux Date Conversion

Mon, 15 Feb 2016 06:58:27 GMT

Hi Team,

I am using mongoDB using QVSource Connector.I am sucessfully getting all the data.But facing issue in Date field conversion.I am getting a timestamp column which has value like 1451411344260.I am trying to convert this date using the function date(floor(<startTime>/ 86400 + 25569)).But the result is 5/25/47963 which is meaning less.What am i missing out?

Re: Linux Date Conversion

petter — Mon, 15 Feb 2016 20:42:46 GMT

There are several threads treating dates in Unix. MongoDB is using the Unix epoch:

From MongoDB documentation:

Internally, Date objects are stored as a 64 bit integer representing the number of milliseconds since the Unix epoch (Jan 1, 1970), which results in a representable date range of about 290 millions years into the past and future.

Have a look at these threads:

Re: How to convert Unix Time Stamp

Re: Linux Date Conversion

petter — Mon, 15 Feb 2016 21:34:34 GMT

You calculation seems not to cater for the fact that Unix epoch time is based on milliseconds. So if you multiply the number 86400 by 1000 then you should be getting the calculation right I think - 86,400,000 is the constant you should be using. Since 25569 is the internal representation of the number of days between QlikView's zero reference and the Unix epoch time's zero reference.

Re: Linux Date Conversion

Tue, 16 Feb 2016 05:19:15 GMT

Thanks petter. I have tried both the solution given in the thread that u have shared.ConvertToLocalTime(Date(25569 + (<timestamp> / 86400)),'UTC') and ConvertToLocalTime(Date(MakeDate(1970, 1, 1) + (<timestamp> / 86400)),'UTC').Still i am getting value as 6/16/30168 1:59:00 AM and 1/5/5300374 2:10:59 AM respectively. when I am trying with this function date(floor(<timestamp>/ 86,400,000+ 25569)) i am getting 9/25/46274949. Obviously none of them seems to be correct.

where am i going wrong?.Please correct me.

Re: Linux Date Conversion

Peter_Cammaert — Tue, 16 Feb 2016 08:07:06 GMT

What is the correct datetime value for the large number you specified in your OP?

Re: Linux Date Conversion

petter — Tue, 16 Feb 2016 08:19:54 GMT

You can't use formatted numbers in an expression as you do - with commas as thousand separators:

Date( Floor( 1451411344260 / 86,400,000 + 25569 )) will give you some strange date,

depending on you number formatting settings...

Should be:

Date( Floor( 1451411344260 / 86400000 ) + 25569 ) will give you 12/29/2015

Then you will get a sensible calculation.

Re: Linux Date Conversion

jonathandienst — Tue, 16 Feb 2016 08:20:28 GMT

=Date(1451411344260 / 86400 / 1000 + 25569) ---> 2015/12/29

Re: Linux Date Conversion

Tue, 16 Feb 2016 09:30:32 GMT

Thank you petter and jonathan.

I am getting the proper date now.