paul-rogers commented on pull request #2268: URL: https://github.com/apache/drill/pull/2268#issuecomment-885875711
@dzamo, let's think a bit about your observation: > You're using the UTC timezone when you work with left.value and right.value and I think that's right. As explained elsewhere, Drill defines its TIMESTAMP column as local time, specifically, the number of ms. since 1970-01-01'T'00:00:00 *local time*. That is, it is a ms offset, from a specific calendar date, but from midnight on that date local time. This *looks* like UTC, but isn't. UTC is defined as ms. since the epoch, and the epoch is UTC. Yes, I agree, this has long been *very* confusing. I find that writing words just gets folks further wrapped around the axle. Tests, however, are unambiguous. If baby Fred's birthday is 2020-07-23, then Fred's Age is 1 year (exactly) in any timezone, during the period from midnight to midnight on 2021-07-23. Fred's age (as customarily stated) should not include units finer than a day. Drill's AGE function does return a finer grain: the number of ms: > Returns the interval between two timestamps or subtracts a timestamp from midnight of the current date. This makes sense: it is the number of ms since midnight local time (since we don't know, and wouldn't care about, the timezone in which Fred was born.) And, the reason that the "midnight local time" makes sense is that even TIMESTAMP are in local time. Now, I could be wrong (we've seen cases where the code does one thing and the documentation says another, or where two smart engineers strenuously disagree about how Drill's time support is supposed to work.) So, *caveat emptor*. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
