By using this website, you agree to our Terms of Use (click here)
Notifications
Clear all
Acumatica OData with Microsoft Excel and Power BI
3
Posts
2
Users
1
Reactions
3,086
Views
August 26, 2018 7:33 pm
Tim and all,
My understanding is that datetime fields are all posted based on UTC at Acumatica's cloud database level.
When I import data via OData into MS SQL, the datetime stamps are converted to the time zone of the target SQL instance.
For instance, a datetime of Aug 5, 2018 00:00:01 UTC (1 min past midnight) in Acumatica database is imported as Aug 4, 2018 17:00:01 PDT into a SQL in CA.
Now, suppose a company ABC is on the EST. A rep in NY posts a sale at 11:59pm EST on Dec 31, and then a rep in LA posts another sale at 11:59pm PST on Dec 31.
From a reporting standpoint, the LA sale shows up at 2:59am EST on Jan 1st, therefore it's a next year's sales record. Am I missing something?
Thanks,
Joni
2 Replies
August 27, 2018 7:21 am
Hey Joni,
It's been a while since I've looked at this but, from what I can remember, the system tracking times for things like login tracking and audit history are all stored in UTC.
The actual Transactions (the stuff that you care about) default to the user's business date which is set based on the timezone that they are in.
So, in your situation, when you pull those two transactions, I believe they should both show Dec 31.
The question then becomes whether or not the transaction stores the timezone on it which I don't think it does. Maybe you could guess it based on the timezone of the user who created the transaction?
August 30, 2018 4:59 pm
Tim,
It seems like you are correct!
Fields like CreateDateTime and LastModifiedDateTime are posted in UTC. While regular date fields are posted without a time stamp and are based on the user's time zone's date.
For instance, invoices issued on Aug 30, 2018 will populate the Acumatica database invoice date with 08/30/2018 12:00:00 UTC (that's at zero hour of Aug 30 UTC).
Now, when importing all of these dates to MS SQL via OData, all of their values are converted to the time zone of the server where MS SQL is.
For instance, invoices posted as 08/30/2018 12:00:00 UTC will be converted to 08/29/2018 17:00:00 in a SQL Server hosted in PDT (and 16:00:00 when in PST).
Therefore, one needs to be mindful of these conversions and properly handle them before using dates in reports or other data manipulation processes.
Thanks,
Joni