Total Timestamp Confusion, please help.

Question

Question

Scott Beeson · Oct 18, 2016

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

score 0 · Answer 1 · 2016-10-19T09:38:09-04:00

Eduard Lebedyuk · Oct 19, 2016

How are the timestamps stored in globals?

0 0

score 0 · Answer 2 · 2016-10-19T09:46:29-04:00

I'm not exactly sure what you're asking, but this is the definition in the ens.messageheaderbase class:

Property TimeCreated As Ens.DataType.UTC

score 0 · Answer 3 · 2016-10-19T09:38:27-04:00

Tirthankar Bachhar · Oct 19, 2016

Can you change from display mode to logical mode. You should get the correct one.

0 0

score 0 · Answer 4 · 2016-10-19T09:47:04-04:00

Scott Beeson · Oct 19, 2016

I don't see this option in DBVisualizer.

0 0

score 0 · Answer 5 · 2016-10-19T09:49:44-04:00

Try running the same from system management portal, if you have access. I'll try to find the similar option for DBVis.

score 0 · Answer 6 · 2016-10-19T10:05:48-04:00

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

score 0 · Answer 7 · 2016-10-19T11:53:38-04:00

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

score 0 · Answer 8 · 2016-10-20T06:51:06-04:00

Tirthankar Bachhar · Oct 20, 2016

Do you still have confusion? Does this solve your issue? If yes please let me know.

0 0

score 0 · Answer 9 · 2016-10-20T09:04:41-04:00

Changing my queries to GETUTCDATE() did seem to fix the issue but I still don't understand why. ¯\_(ツ)_/¯

score 0 · Answer 10 · 2016-10-20T10:06:45-04:00

Scott Beeson · Oct 20, 2016

Added a new top level comment since this thread is getting tight. I'd love to see your thoughts.

0 0

score 0 · Answer 11 · 2016-10-20T10:05:52-04:00

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

score 0 · Answer 12 · 2016-10-20T10:09:28-04:00

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.