Timestamp difference between the AX GUI and the SQL table "batchhistory"

(0) Share

Report

Posted on by Community Member

I noticed today that the start time and end time of a batch job is 4 hours ahead in the SQL table batchhistory vs. looking at the batch history in the AX GUI. AX GUI is showing the correct time. I checked all the servers and the AX application and the time zone is (GMT-05:00) Eastern Time (US & Canada) in ALL. This difference may not be critical but I need to figure out why this is happening in case there is an underlying issue that needs to get corrected.

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Verified answer

Martin Dráb 236,289 Most Valuable Professional on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

DateTime fields store UTC time in database. AX GUI, on the other hand, applies user's timezone (which may be, unlike the UTC value, different for different users. If it doesn't explain your observation, what exactly is the problem? Do you see something else than UTC in database?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

Thanks for your reply Martin, your answer make sense if UTC time is four hours ahead of the EST. The job is running under Aaxadmin user whose time zone is EST. Again the issue is that AX GUI shows that the job started at 1 pm (correct time) but the database table shows the job started at 5 pm.

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

Hi Farokh,

this is something confusing.

in your last respond, there is a difference of 3 hours. In your question you spoke of 4 hours.

first of all: this aren't timestamp fields, but simple datetime fields.

If you create a datetime Field in Ax, in the underlaying sql table TWO fields are created. The Datetime fields plus a integer field named <DateTimeField>TZID. This second field represents the Timezone coded. Within the real Datetime Field always the UTC time is stored.

What are your current daylight saving?

Is your current UTC expressed in 'virtual' timezone? GMT-5 or GMT-4 ?

(Yes I know: The question isn't really correct, but i hope you know what I mean)

The Ax client uses the current timezone of the client to represent this strored utc time as a local time..

I don't understand the problem at the moment.

Are the values in database represents the correct UTC? yes or no? Or is the missing some hour(s)

regards

Douglas

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 236,289 Most Valuable Professional on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

I'm a bit confused by your numbers (4 pm - 1 pm isn't four hours), but if you wonder why it's four hours and not five, I think you're not taking into account the daylight saving time)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

Douglas, First of all let me apologize for the typo - I corrected it. There is a 4 hour time difference between the GUI and the SQL table for the Startdatetime and Enddatetime in the batchhistory. We are in Virginia, USA so our time zone is EST. I don't know the answer to your second question. The answer to your last question is that it appears that the database is using UTC which is EST + 4.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

Sorry it was a typo and I corrected it. the correct pair is 1pm and 5pm.

Was this reply helpful? Yes No
Martin Dráb 236,289 Most Valuable Professional on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

I'm no expect on Virginia, but internet tells me that the current time zone in Virginia is Eastern Daylight Time (EDT) -0400 UTC.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

That's correct. I think it all make sense now UTC is ahead of Eastern time zone and the daylight time saving does apply to VA.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

You should always be clear on the Time zones. EDT and EST both apply at different times of the year as EST is Eastern Standard Time and EDT is Eastern Daylight Time. So from March through October (I think), Virginia (and most of the Eastern time zone) is in EDT. The rest of the year it is in EST. UTC/GMT does not change with DST.

Was this reply helpful? Yes No
Douglas Noel 3,905 on at

Like (0)

Report

RE: Timestamp difference between the AX GUI and the SQL table "batchhistory"

Hi Robert,

you are right. UTC/GMT does not really change with DST.

This was the reason for me to use the notation "virtual GMT"

In reality the TimeZone change. But the expression GMT-5 or GMT-4 is a better known description for expressing what you mean.

If you have EDT at the moment, everything seems to be all right.

regards

Douglas

Was this reply helpful? Yes No