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;------------------- -------------------
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;------------------- -------------------
2016-10-18 16:17:51 2016-10-18 11:17:56
Comments
How are the timestamps stored in globals?
I'm not exactly sure what you're asking, but this is the definition in the ens.messageheaderbase class:
Can you change from display mode to logical mode. You should get the correct one. 
I don't see this option in DBVisualizer.
Try running the same from system management portal, if you have access. I'll try to find the similar option for DBVis.
Another text from DocBook, that will help you.
Run below query from DBVis. You may use GETUTCDATE rather than GETDATE.
Select DATEADD(mi, -1, GETDATE()) As CurrentDateLess1Min, GETDATE() As CurrentDate, DATEADD(mi, -1, GETUTCDATE()) As CurrentUTCDateLess1Min, GETUTCDATE() As CurrentUTCDate
CurrentDateLess1Min 2016-10-19 10:50:43
CurrentDate 2016-10-19 10:51:43
CurrentUTCDateLess1Min 2016-10-19 15:50:43
CurrentUTCDate 2016-10-19 15:51:43
Do you still have confusion? Does this solve your issue? If yes please let me know.
Changing my queries to GETUTCDATE() did seem to fix the issue but I still don't understand why. ¯\_(ツ)_/¯
Added a new top level comment since this thread is getting tight. I'd love to see your thoughts.
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.