Timestamps in History table - Community

smp (34 posts)

February 11, 2014 08:35 AM

Accepted Answer

I executed the following query against the adTempus database to calcutate the amount of CPU time each job has consumed each month. Today I executed this query and noticed something odd in the results:

select j.name as JobName,count(executionstart) as TotalInstances from job j left join executionhistoryitem ehi on j.oid=ehi.joboid and ehi.executionstart >= '2014-02-01 00:00:00' and ehi.executionfinish <= '2014-02-11 08:10:36' group by j.name

I have one particular job that should have shown up in this report, but did not. After further research, I found that the history table reports the executionFinish was 2014-02-11 10:00:07.017. However the admin console shows the job finished 2/11/2014 4:00:07AM (which is correct, as far as I am concerned). I am in the Central time zone. Is this expected? Is there a way to have adTempus record the history in the same time as the console reports it?

Bill Staff (599 posts)

February 11, 2014 09:07 AM

Accepted Answer

Sorry--I should have thought of this when I reviewed your database query for you the other day in your support request.

In adTempus 3, the timestamps were stored in local time.

Beginning with adTempus 4 they are stored in UTC (GMT), so what you are seeing is correct. For each timestamp column there is a corresponding column (same name but with "TZ" suffix) that stores the UTC offset (in minutes) for the value. To get the local time, you add the UTC offset value to the UTC timestamp.

So if you are looking at ExecutionStart, you will see a value of -360 for the ExecutionStartTZ column. You add -360 minutes to (i.e., subtract 6 hours from) the ExecutionStart time of 10:00 UTC to get the local time of 04:00 Central time.

To do your selection based on local time, change it to use

DateAdd("n",ExecutionStartTZ,ExecutionStart)>='2014-02-01 00:00:00' and DateAdd("n",ExecutionFinishTZ,ExecutionFinish)<='2014-02-11 08:10:36'

The change to storing timestamps in UTC was done partly to make it easier to sort history data when you are using Distributed Scheduling with a Master in one time zone and agents in different time zones.

The offset stored in the TZ column will be the offset in effect when the time was recorded, so when you are in Daylight Saving Time you will see offsets of -300 instead of -360.

The adTempus 4 database upgrade process converted the timestamps for all the history tables, so you will get consistent results if you query data that was brought forward from version 3.

(In version 3 we also had the UTC offset column, but the calculation worked the other way, to get from the local time stored in the database to UTC.)

The following columns have corresponding UTC offset columns

ExecutionHistoryItem table: ExecutionStart, ExecutionFinish
ExecutionHistoryStep table: ExecutionStart, ExecutionFinish
JobStatus table: ExecutionStart, ExecutionFinish, NextStart, NextMirrorStart
JobAgentJoin table: ExecutionStart, ExecutionFinish, NextStart
LogMessage table: MessageTimestamp
ObjectChangeLog table: Timestamp

smp (34 posts)

February 11, 2014 09:28 AM

Accepted Answer

Thank you Bill, using the DateAdd functions you referenced did produce the results I was expecting. This allows me to continue forward.

Thanks again.