Question
· Oct 18, 2016

Total Timestamp Confusion, please help.

This tells me that there is no timezone offset on this table/field:

Select TOP 1 GETDATE() as Now, TimeCreated FROM ens.messageheader ORDER BY TimeCreated DESC;
Now                 TimeCreated         
------------------- ------------------- 
2016-10-18 16:16:49 2016-10-18 16:16:31 

 

So why is TimeCreated in this resultset 4 hours less than OneMinuteAgo when I'm clearly requesting only records with a TimeCreated greater than OneMinuteAgo?

Select TOP 1 DATEADD(mi, -1, GETDATE()) as OneMinuteAgo, TimeCreated FROM ens.messageheader WHERE TimeCreated > DATEADD(mi, -1, GETDATE()) ORDER BY TimeCreated ASC;
OneMinuteAgo        TimeCreated         
------------------- ------------------- 
2016-10-18 16:17:51 2016-10-18 11:17:56 
Discussion (13)0
Log in or sign up to continue

Another text from DocBook, that will help you.

GETDATE returns the current local date and time as a TIMESTAMP. All Caché SQL timestamp, date, and time functions except GETUTCDATE are specific to the local time zone setting. GETUTCDATE returns the current UTC (universal) date and time as a TIMESTAMP. You can also use the Caché ObjectScript $ZTIMESTAMP special variable to get a current timestamp that is universal (independent of time zone).

This might better explain what I'm seeing or not understanding:

SELECT DATEADD(hh, -1, GETDATE()) as OnlyAfterThisDate, MIN(TimeCreated) as FirstDate, MAX(TimeCreated) as LastDate FROM ens.messageheader WHERE TimeCreated > DATEADD(hh, -1, GETDATE());

SELECT DATEADD(hh, -1, GETUTCDATE()) as OnlyAfterThisDate, MIN(TimeCreated) as FirstDate, MAX(TimeCreated) as LastDate FROM ens.messageheader WHERE TimeCreated > DATEADD(hh, -1, GETUTCDATE());

Wait, I think I understand now.  In the second query, >8:01 is what I actually want since it was currently 9:01.  FirstDate is stored in UTC but my client is automatically adjusting, so it works when I compare it to UTC.  The local value for "OnlyAfterThisDate" isn't adjusted when displayed.

I'm not sure I'm verbalizing it correctly, but I think I do understand now.