QueryBuilder System Date is off by 4 hours

[avillanueva]

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?

[d_graham]

@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]

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_graham]

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.

[avillanueva]

Not sure Java Method is way to got, complicated too, you have to add methods to setup XML file to get them to show up. Not something I can do in Production right now. So, here is my test. I added System Date function to my output. 

Here is the results from 3:19 PM just a short time ago:

You can see that the release times of the documents show up AFTER the current system time. I have verified from SQL and from the OS level that the time is correct. This makes no sense.

I will also note I have a timezone specified in my preferences.

[d_graham]

Tony,

“Java Method”is not complicated.

Quite simple in fact, but would require restart to read the xml you register the method.

So, maybe you send me your query. Might help to take a look at that.

As a last resort, get management to agree to move everything to the UK.😂

Also, the time on your servers is correct.  True?

Might want to double check that.

[rleir]

We have the same problem.  

As for time zones, SYSTEM DATE is a database function and we have Oracle on a different server so I suspect we might have two servers on different timezones. or perhaps Oracle assumes GMT though the server is actually running EST.

[d_graham]

@rleir I suggest checking the time on the servers.

I just did some work for a company where the Oracle sever was EST and there Windchill server was set to PST. Obviously not good, so I contacted the admin and told him about it. He asked me to correct it for him 😂

@avillanueva What’s the deal with the time/time zones on the servers? They good or not?

[avillanueva]

PDMLink server:

Database Server:

I ran these sql queries against databsae:

select created,(select dbtimezone from dual) "dbtimezone"from v$database;

SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

I can see the -4 and -5 a difference between EST and EDT (Daylight Savings Time). Not enough to explain what is occurring here.

[d_graham]

That's for sure.  Something is up.

I would still suggest testing using "Java Method" and see if results are the same.

We are talking 10 minutes to write the code to pass a Timestamp to a method and convert from GMT to the Time Zone specified in your preferences and return it to your query report.

At this point, I'd say this is a logical step in the investigation and maybe a workaround.

[joe_morton]

Instead of the database function, use a Constant, data type: date. Select macro CURRENT_TIME. I'm pretty sure that was out of the box on our system.

[d_graham · Accepted solution]

CURRENT_TIME macro is definitely OOTB.

That and CURRENT_USER macro have been around since forever.

[avillanueva]

Thanks, this is a work around but I will pursue a call to PTC since there is definitely something off. Constant macro shows correct.

[rleir]

We have a support call open too. 

[rleir]

PTC support wanted us to run :

xconfmanager -d wt.method.timezone

We don't seem to have this setting in our xconfs. According to the article, the default is GMT but we don't see anything. Is that normal? 

Google found these related articles:

https://www.ptc.com/en/support/article/CS36228

https://www.ptc.com/en/support/article/CS134908

community:

https://community.ptc.com/t5/Windchill/The-same-java-code-returns-a-different-result-when-run-in-a/td-p/335697

[DmitryC]

You can also use CURRENT_TIME macro in conjunction with "Time Difference" database function and it will work correctly as well:

You can even accept user input for this in the report template and make a report for something that happened in the last X hours/days, etc.

Regards,

Dmitry