Linux Date Conversion

[#1]

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?

[petter]

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

[petter]

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.

[#4]

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.

[Peter_Cammaert]

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

[petter · Accepted solution]

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.

[jonathandienst]

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

[#8]

Thank you petter and jonathan.

I am getting the proper date now.