Processing Time Zone
In Space Management → Space Properties there is an option "Processing Time Zone". If you try to change it, you get this info:
"The Space Properties Time Zone is used to populate LoadDate and is also referenced for NOW() and NOWDATE() functions."
What I need is exactly to tune NOW and NOWDATE to the required Time Zone (sometimes I need it to be CEST, sometimes UTC).
I have played with this option and found out that, no matter how I set it, in Query Admin the result is always UTC (with no specification of that, which is confusing).
In Prepare, instead, if I create a Prepared Source which outputs NOW and preview it, the result does depend on the "Processing Time Zone" parameter. I see that Daylight Saving Time is included. There is no specific option for "UTC", all time zones refer to some specific city or place in the world, and due to the Daylight Saving Time even the UTC+00:00 options do not always correspond to UTC.
There is on top an option which reads "Server Local Time", and this is also the default. In my case the server farm is in Frankfurt, so I'd expect the Server Local Time to be UTC + 2 hours (where I have included the Daylight Saving Time) - however, the result is UTC.
Which is the best option to get UTC times (and dates)? Is Server Local Time always UTC?
Answers
-
Hi Enrico!
I'm discussing this with the product development team, and will get back to you soon 😊
1 -
Hi Riina,
Thanks, that's very kind of you! 🙂
0 -
I got an answer, short and, hopefully, sweet 😄
If we disable pushdown , NOW and NOWDATE would come in processing timezone , If enabled , the query being pushed to db , which returns UTC.
Does that answer your question?
0 -
Partially. I will test in Query Admin to see what happens with and without the pushdown. However, this does not answer my main question:
What is the best option (for the "Processing Time Zone" parameter in Space Management → Space Properties) to get UTC times (and dates)? Is Server Local Time always UTC?
I am referring here to times (and dates) in ETL (ADR) scripts, which apparently do not depend on the pushdown parameter (just tested).
0 -
I have done some tests about the pushdown. In Visualizer, it works fine. In Query Admin it doesn't. In my original post I said that Query Admin is always showing UTC. That is wrong. I have discovered that after changing the "Processing Time Zone", in order for that to reflect into Query Admin I have to (at least) switch Space and go back to the original Space (re-login, basically). After this is done, the time zone is correctly taken into account by Query Admin.
However, the result in Query Admin to the query:
SELECT NOWDATE FROM [All]
does not depend on the "Pushdown" parameter in Space Properties. I have also tried the query:
pushdown:SELECT NOWDATE FROM [All]
and the result is still the same, including time zone shift.
Incidentally, talking now about ETL (ADR) Prepared Sources, I have mentioned that in Preview the time zone is always considered. I had tried also an "Execute" before the Save, including a PRINT NOW in the Source - in the print log, the time is always UTC!
I guess you (Infor) want to fix all these discrepancies and provide users with a homogeneous behavior across the system…
(Thanks for your time and patience, Riina! 😊)
0 -
Thank you for the detailed explanation, Enrico! I have forwarded it to the product development team. They mentioned that there have been discussions about the topic, but no decision have yet been made. One option would be to introduce completely separate alternatives to now and nowdate, and those alternatives would be driven by the processing timezone. So, stay tuned! 🤓
0 -
… also this one will be marked as an answer, but first please:
… With the current system, what should I use to get UTC? Server Local Time or something else?
0
