Thread

Hi,

In order to import some data in another system, I must read the simple_commerce_purchases and especially the date. I see that this is an int(12) in phpmyadmin. How is the date stored in this column (my first guess was for ticks but this does not work) and if someone has an instant answer for converting it to a .net DateTime…

Oh, and is it UTC ? Server Local ?

Update:
I’m very close by doing this:

DateTime dateTime = new System.DateTime(1970, 1, 1, 0, 0, 0, 0);
dateTime = dateTime.AddSeconds(orderdate /* int read from the database */);

But this gives me this:
Paypal gives an order date of Nov 18, 2009 12:45:20 PST
The EE admin panel shows 2009-11-18 03:45 PM (which seems correct since I’m hosted at enginehosting which I guess is PST+3 i.e. EAST time)
My localization setting in EE is UTC-5 (east time in Montreal)
My code above gives 11/19/2009 2:45:28 AM

Thank you

Nicolas

It’s a so-called UNIX timestamp, basically the seconds sind 1/1/1970 0:00 UTC.

Thanks Ingmar. But if you read my update, this is already what I figured out. However, it does not give me the right time in .net so I need to understand why it gives the right time in the EE CP and not in my .net attempt. As you can see when I take 1/1/1970 and adds the value as seconds, in my example I get a date almost 12 hours later than what is expected.

I am afraid we can’t exactly assist you with .NET programming… what you could try is to turn on debugging so EE will show you the precise SQL queries it uses. Makes sense? Moving to Howto.

I have asked the question on StackOverflow (http://stackoverflow.com/questions/1766208/unix-timestamp-to-net-datetime) and it seems the stored data is wrong (i.e. the data that EE stored in response to paypal IPN is wrong).

May I suppose something weird: let’s say the IPN function stores a wrong date. When it displays it, again it uses a wrong algorithm (the inverse of the previous bug let’s say) and displays the correct datetime. When I take the raw data, I get the erroneous value and won’t be able to convert to a correct datetime… Crazy ?

Update: someone on the SO site, made the conversion throught the http://www.onlineconversion.com/unix_time.htm web site. The unix timestamp 1258598728 produces the date Thu, 19 Nov 2009 02:45:28 GMT. So why is paypal reporting the order at Nov 18, 2009 12:45:20 PST and the EE CP reports it also at this time (2009-11-18 03:45 PM)? This is beyond my understanding.