cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Alert timestamps in database off by 4 hours

I just upgraded to 11.5 (yeah I know...) and I am seeing odd behavior in the new alert database tables.  I seems they are not using the local server time for the timestamp of when an alert occurred.  Screenshots below of a volume alert that occurred shortly before 9:30 am today (3/23)

alert_time_Orion.jpg

alert_time_database.jpg

Both images show the same alert, but the "All Active Alerts" page in Orion seems to be automatically converting them back to the correct time.  I use many custom SQL queries to report out on alert status and time is a critical component of them.  As is stands, if an alert triggered at 11 pm tonight, my daily report of alerts triggered for today WOULD NOT show it since the database would stamp it as 3 am TOMORROW.

Is this by design, if so WHY? 

0 Kudos
7 Replies
Level 13

Hello,

We store time in DB in UTC.

You have to change your queries

I recommended use one of function from this page:

sql - Convert Datetime column from UTC to local time in select statement - Stack Overflow

After changing timezone you will only change one number in function.

I recommended use this one:

-Name: udfToLocalTime.sql
--Purpose: To convert UTC to local US time accounting for DST
--Author: Patrick Slesicki
--Date: 3/25/2014



Simple change from ALTER nameoffunction to CREATE nameoffuction.


Lada

Level 17

So your 4 hours off UTC?

0 Kudos

HEY!!! No beating me to the big reveal!!

Leon Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

Sorry, I wore my cape today!

0 Kudos

Yes, but the alert timestamps in previous versions were not on UTC, they were on local server time.

So now i have to manually update all of my SQL queries to subtract 4 hours from the timestamp?  And this is only for the next 6 month until we Fall Back and then i have to do it again to subtract 5 hours?  This was exactly why I all my server to Eastern time regardless of location.

0 Kudos
Level 18

Out of curiosity, what is your timezone and what is the timezone of the polling engine?

Leon Adato | Head Geek
------
"Measure what is measurable,
and make measurable what is not so." - Gallileo

0 Kudos

All have been standardized to Eastern.  All pollers and database servers are set the same.

0 Kudos