[GitHub] [drill] dzamo edited a comment on pull request #2268: DRILL-7926: Age function fix

[GitBox <gi...@apache.org>]

dzamo edited a comment on pull request #2268:
URL: https://github.com/apache/drill/pull/2268#issuecomment-885702320


   For my part, I agree we're now okay fetching the query start time from ContextInfo like you're doing @oleg-zinovev .  The existing utility method `DateTypeFunctions.getQueryStartDate` follows the same route.
   
   Next, I will share what I've found out trying to clarify your time zone questions.  In Drill, `DATE` and `TIMESTAMP` are zoneless (a.k.a. "naive") times that are implicitly regarded as in whatever timezone is set by the parameter `user.timezone`, and which defaults to the time zone of the local machine.  You can set `user.timezone=UTC` and then everything does happen in UTC.
   
   But Drill's _internal representation_ of these zoneless times, which you're encountering, is a different story.  From what I can see it was decided to use UNIX times internally, and doing that requires the specification of absolute time, not just an ambiguous naive time.  So the zoneless times coming in from the outside world, e.g. when I do `select cast('2021-07-23 01:23:45' as timestamp)` had to be coerced to _some_ timezone and it didn't matter which one, provided the choice was consitent throughout the codebase including using same time zone when they did the reverse operation and go from UNIX time back to a zoneless `TIMESTAMP`.
   
   So here we are.  You're using the UTC timezone when you work with `left.value` and `right.value` and I think that's right.  But I think that, as @paul-rogers says, the query start time you get from the ContextInfo should be assumed to be in the local time zone.  You can test this one quite easily if we agree that the following query should return an interval of 0s, including when run on Drillbits that are not in UTC...
   
   ```sql
   select age(current_date);
   ```
   I ran it with your branch now and I got a number of hours equal to my timezone offset (2).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org