QueryBuilder System Date is off by 4 hours

Forum|Forum|3 years ago
March 17, 2023
2 replies
3432 views

I have a report that I am implementing a look back parameter, show me how many documents were released in the past X number of hours. I am using the database function System Date in my query builder but it seems to be reporting the system time 4 hours off. If its 1pm, the time displays as 9am, the timezone is correct as EDT. I can confirm that the reported time from the OS and Database using straight SQL queries is correct as is the reported timestamps of objects. This obviously throws off calculations in hours back since it is using this incorrect time in timestamp subtractions. I have seen previous posts related to this but its almost as if Windchill is applying the timezone offset twice. I've accounted for this by adding 4 hours to that time in my calculations but I should not have to do this. Thoughts?

Reporting

Best answer by d_graham

CURRENT_TIME macro is definitely OOTB.

That and CURRENT_USER macro have been around since forever.

D

d_graham

18-Opal

@avillanueva

Timestamps are always stored in the Windchill database as GMT.

There is 4 hours diff between EDT and GMT.

I think that’s the culprit.

You can certainly pass the database Timestamp to a “Java Method” to convert and display time in EDT.

avillanueva

Author

23-Emerald I

Yes, I read that. But it was four hours in the wrong direction. So as I write this, it is 8:53 pm in CT, EDT timezone. GMT is 12:53am. When I ran System Time and displayed it in query builder, it showed 4:53 pm, even including the same EDT timezone stamp. Its almost like it did it twice.

D

d_graham

18-Opal

For testing, I would try adding another column in the Select portion of the query.

The new column would be a “Java Method”.

The method would simply convert a Timestamp from GMT to EDT (or whatever) and see if the result in the new column is correct or is different than what you are currently seeing.