Opscenter Report is not showing times with Daylight savings

[thebtm]

I have generated a report in opscener using sql to generate a report and it gets the start time and end time off by 1 hour. The time would be correct if we weren't in daylight savings right now and it will be over in a week or so. I do have the user settings set to Canada Mountain and still wont correct the times. Any ideas to correct the time without changing the timeszone settings to an incorrect setting?

--this sql statement gets the client, start time, end tiem, schedule type, status and how long it took in hours
select
   domain_JobArchive.clientName as "Client",
   UTCBigIntToNomTime(domain_JobArchive.startTime) as "Start Time",
   UTCBigIntToNomTime(domain_JobArchive.endTime) as "End Time",
   lookup_ScheduleType.name as "Schedule Type",
   lookup_JobStatusCode.id as "Status",
   ( sum (NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.endTime)) / (60*60) ) as "Duration in Hours"
from
   domain_JobArchive,
   lookup_ScheduleType,
   lookup_JobStatusCode
where ( ( (domain_JobArchive.id = domain_JobArchive.parentJobId)
      OR (domain_JobArchive.parentJobId = 0) ) AND (domain_JobArchive.isValid = '1') )
   and (domain_JobArchive.clientName SIMILAR TO 'client')
   and (DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=(24*14))
   and (domain_JobArchive.scheduleType = lookup_ScheduleType.id)
   and (domain_JobArchive.statusCode = lookup_JobStatusCode.id)
group by
   domain_JobArchive.startTime,
   domain_JobArchive.endTime,
   lookup_ScheduleType.name,
   lookup_JobStatusCode.id,
   domain_JobArchive.clientName
order by domain_JobArchive.startTime desc

[GeForce123]

Is the time off in the admin console as well or just in the reports you are creating in OpsCenter?

If they are off in the admin console as well, I'd go to File->Adjust Time Zone and check the configuration there. Maybe a custom time zone has been created or maybe 'use daylight savings time' is not checked.

[thebtm]

Time is correct and Time zone is correct but the report is off by one hour

[GeForce123]

Have you checked the time settings on the server itself, not in OpsCenter?

If it's a Windows server, adjust date/time in the task bar.

I'm thinking UTCBigintToNOMTime converts the BIGINT time to the database server timezone and if the server itself has a different time than OpsCenter, you could see these discrepancies.

If that wasn't the issue, another thought is instead of using UTCBigIntToNomTime what if you used adjust_timestamp and set your own offset. So something like this. My offset would be -18000000 milliseconds because I am EST

This might break the NOM_DateDiff line though.

select
   domain_JobArchive.clientName as "Client",
   adjust_timestamp(domain_JobArchive.startTime, -18000000) as "Start Time",
   adjust_timestamp(domain_JobArchive.endTime, -18000000) as "End Time",
   lookup_ScheduleType.name as "Schedule Type",
   lookup_JobStatusCode.id as "Status",
   ( sum (NOM_DateDiff(domain_JobArchive.startTime, domain_JobArchive.endTime)) / (60*60) ) as "Duration in Hours"
from
   domain_JobArchive,
   lookup_ScheduleType,
   lookup_JobStatusCode
where ( ( (domain_JobArchive.id = domain_JobArchive.parentJobId)
      OR (domain_JobArchive.parentJobId = 0) ) AND (domain_JobArchive.isValid = '1') )
   and (domain_JobArchive.clientName SIMILAR TO 'client')
   and (DATEDIFF(hour,adjust_timestamp(domain_JobArchive.endTime, 18000000), GETDATE()) <=(24*14))
   and (domain_JobArchive.scheduleType = lookup_ScheduleType.id)
   and (domain_JobArchive.statusCode = lookup_JobStatusCode.id)
group by
   domain_JobArchive.startTime,
   domain_JobArchive.endTime,
   lookup_ScheduleType.name,
   lookup_JobStatusCode.id,
   domain_JobArchive.clientName
order by domain_JobArchive.startTime desc

[thebtm]

After the Actual Time ahead happened for us in canada, the reports have corrected it's self. I wonder if the opscenter is out of date for handling date. We are running 7.6.0.4

[D_Flood · Accepted solution]

I've seen this happen a lot in Java based applications that use their own version of Java rather than the globally installed one.  Java has an internal "when does the time change" table and the internal version may be old enough to not have the correct dates for the changes.

Even if you have no plans to update your NetBackup, you can always run the latest OpsCenter and have it talk to the earlier NetBackup versions on your Masters.  That doesn't fix everything but would get you a newer version of internal Java.

[thebtm]

Thank You, I will look into that.