TimeStamps in database do not match the real execution time

[Mylene Chalut]

Hello !

I created a VARA.SQLI to query the database and was very suprised to see the the TimeStamp2 (Start Date) and TimeStamp4 (End Date) do not match with what I have in the Process Monitoring screen.  Any idea why, and how can this be fixe ?

Process Monitoring:

VARA:

(images also in attachment)

Thank you :-)

------------------------------
Programmer-analyst
Statistics Canada
------------------------------

[Frank Muffke]

Hi

Timestams in DB are stored in UTC.
You can ues script function CONV_TIMESTAMP to convert it.

cheers, Wolfgang

------------------------------
Support Info:
if you are using one of the latest version of UC4 / AWA / One Automation please get in contact with Support to open a ticket.
Otherwise update/upgrade your system and check if the problem still exists.
------------------------------

[Mylene Chalut]

Thank you for the fast reply !!  However, ​​I don't think I can use that function in my sql statement nor when I created my Vara to Chart Analytics report...  am I right ?

Declare @stDate datetime

Declare @hours int = Cast(? as int)

 

-- Get current date

Declare @theDate datetime = GETDATE();

 

-- Set Start Date 24hr prior

SET @stDate = dateadd(hour,-@hours,@theDate)

 

select ah_Name

     , ah_Idnr

     , ah_Otype

     , ah_Stype

     , ah_status

     , ah_timestamp2 as StartTime

     , ah_timestamp4 as EndTime

     , ah_RunTime

from ah

where ah_client = ?

and ah_otype in ('JOBS','JOBP','EVNT','JSCH')

and ah_timestamp2 >= @stDate

and ah_timestamp4 <= @theDate

order by ah_timestamp2 desc

------------------------------
Programmer-analyst
Statistics Canada
------------------------------

[Frank Muffke]

Hi

no, within SQL you need to convert/change it with means of SQL/ORA....

If you use it Automic Script, you can use the script function I mentioned above.

cheers, Wolfgang

------------------------------
Support Info:
if you are using one of the latest version of UC4 / AWA / One Automation please get in contact with Support to open a ticket.
Otherwise update/upgrade your system and check if the problem still exists.
------------------------------

[Leonard Olteanu]

Hi Mylene,

If you are on SQL Server 2016 or later, you can convert dates to UTC like this:
select CONVERT(datetime, @stDate AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC')

------------------------------
Cheers,
Leonard
------------------------------

[Mylene Chalut]

Thank you @Leonard Olteanu !!​

------------------------------
Programmer-analyst
Statistics Canada
------------------------------

[Pete Wirfs]

different ways to skin the cat.
I use this SQLServer technique;

     , dateadd(hour, datediff(hour, getutcdate(), getdate()), ah_timestamp2) as start_time
     , dateadd(hour, datediff(hour, getutcdate(), getdate()), ah_timestamp4) as End_time​

EDIT: 
The solution I originally posted ignores DST time shifts!
The "AT TIME ZONE" solution takes that into account.

	 , convert(datetime, ah_timestamp2 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as Start_Time
	 , convert(datetime, ah_timestamp4 AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time') as End_Time

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Leonard Olteanu]

Pete, you might want to be careful with your feline paring method, or you might end up excoriated too :)
Your method works for any date in the current DST period, but it will give you the wrong result if the date is outside the current DST period. The SQL below shows the difference between your method (method 2) and the recommended method (method 1) for today's date (which is, obviously, in the current DST period) and a date 35 weeks ago which is guaranteed (sigh... I was typing "quarantined" initially) to be outside of the current DST period because the DST period in North America is 34 weeks long. You might want to replace 'Eastern Standard Time' with 'Pacific Standard Time' to get the right results for your time zone.

select
getdate() as LocalTimeRightNow
, CONVERT(datetime, getdate() AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC') as UTCRightNow_Method1
, dateadd(hour, - datediff(hour, getutcdate(), getdate()), getdate()) as UTCRightNow_Method2
, datediff(hour, CONVERT(datetime, getdate() AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC'), dateadd(hour, - datediff(hour, getutcdate(), getdate()), getdate())) as ErrorDiffRightNow
, dateadd(week, -35, getdate()) as LocalTime35WeeksAgo
, CONVERT(datetime, dateadd(week, -35, getdate()) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC') as UTC35WeeksAgo_Method1
, dateadd(hour, - datediff(hour, getutcdate(), getdate()), dateadd(week, -35, getdate())) as UTC35WeeksAgo_Method2
, datediff(hour, CONVERT(datetime, dateadd(week, -35, getdate()) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC'), dateadd(hour, - datediff(hour, getutcdate(), getdate()), dateadd(week, -35, getdate()))) as ErrorDiff35WeeksAgo

------------------------------
Cheers,
Leonard
------------------------------

[Pete Wirfs]

Valid point.   The business I work for only operates inside of a single timezone, so we've never had to worry about such things.  This reminds me of the mistakes we made that created the Y2K bug.... everything worked fine, until it didn't. (I was an application developer in the 1980's and the 1990's, before SQL was a thing.)

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Leonard Olteanu]

Just to clarify, it's not about different time zones. It is about the different offset between your time zone and UTC when the date is in the DST period (summer) vs. when the date is outside of DST period (winter). You will have to apply a different offset to get the right UTC time in these cases.

------------------------------
Cheers,
Leonard
------------------------------

[Pete Wirfs]

I see.  Thank you.

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------