New post
Question
· Oct 18, 2016

Total Timestamp Confusion, please help.

#SQL #Caché

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
Tirthankar Bachhar · Oct 19, 2016

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).

0 0
Scott Beeson · Oct 20, 2016

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());

0 0