[jira] [Created] (DRILL-8101) Resolve the TIMESTAMP madness

["Paul Rogers (Jira)" <ji...@apache.org>]

Paul Rogers created DRILL-8101:
----------------------------------

             Summary: Resolve the TIMESTAMP madness
                 Key: DRILL-8101
                 URL: https://issues.apache.org/jira/browse/DRILL-8101
             Project: Apache Drill
          Issue Type: Bug
    Affects Versions: 1.19.0
            Reporter: Paul Rogers


Drill's TIMESAMP type tries to be two different things at the same time, causing incorrect results when the two interpretations collide.

Drill has the classic DATE and TIME data types. A DATE is just that: a day wherever you happen to be. Your birthday goes from midnight to midnight in the time zone where you find yourself. If you happen to travel around the world, you can make your birthday last almost 48 hours as midnight of your birthday starts at the international date line, circles the globe, followed by the midnight of the next day.

Similarly, a time is a time where you are. 12:00PM is noon (more-or-less) as determined by the sun. 12:00PM occurs once in every time zone every day. Since there are many time zones, there are many noons each day.

These are both examples of local time. Most databases combine these two ideas to get a DATETIME: a date and time wherever you are.

In our modern world, knowing something occurred on 2022-01-02 12:00:00 is not good enough. Did it occur at that time in my time zone or yours? If the event is a user login, or a network breach, then it occurred once, at a specific time, it did not occur many times: once in each time zone. Hence, machines often use UTC time to coordinate.

Unix-like systems also define the idea of a "timestamp", the number of seconds (or milliseconds or nanoseconds) since 1970-01-01 00:00:00. This is the time reported by Java in the {{System.currentTime()}} function. It is the time most often found in machine-generated logs. It may be as a number (ms since the epoch) or as an ISO-formatted string.

Thus, users of Drill would expect to find a "timestamp" type that represents a UTC timestamp in Unix format. The will be disappointed, however.

Drill's TIMESTAMP type is essentially a DATETIME type: it is a date/time in an unspecified timezone and that zone can be whatever you want it to be. UTC? Fine. Local? OK. Nairobi? Sure, why not.

This works fine as long as _all_ your data is in the same time zone, and you don't need a concept of "now". As described in DRILL-8099 and DRILL-8100, this is how the authors of CTAS thought of it: read Parquet data straight into Drill with no conversion, then write it back out to JSON with no conversion. Both work with UTC, so the result is fine: who cares that the 32-bit number, when in Drill, had no implied time zone? It is just a number we read then write. All good.

It is even possible to compute the difference of two DATETIMEs with unspecified time zone: that's what an INTERVAL does. As long as the times are actually in the same zone (UTC, say, or local, or Nairobi), then all is fine.

Everything collapses, however, when someone wants to know, "but how long ago was that event"? "Long enough ago that I need to raise the escalation level?" Drill has the INTERVAL type to give us the difference, but how do I get "now"? Drill has {{CURRENT_TIMESTAMP}}. But, how we have a problem, what timezone is that time in? UTC? My local timezone? Nairobi? And, what if my data is UTC but {{CURRENT_TIMESTAMP}} is local? Or visa-versa? The whole house of cards comes crashing down.

Over the years, this bug has appeared again and again. Sometimes people change the logic to assume TIMESTAMP is UTC. Sometimes things are changed to assume TIMESTAMP is local time (I've been guilty of this). Sometimes we just punt, and require that the machine (or test) run only in UTC, since that's the only place the two systems coincide.

But, in fact, I believe that the original designers of Drill meant TIMESTAMP to have _no_ timezone: two TIMESTAMP values could be in entirely different (unknown) timezones! One can see vestiges of this in the value vector code. It seems the original engineers imagined a "TIMESTAMP_WITH_ZONE" type, similar to Java's (or Joda's) {{ZonedDateTime}} type. Other bits of code (Parquet) refers to a never-built "TIMESTAMPZ" type for a UTC timestamp. When faced with the {{CURRENT_TIMESTAMP}} issue, fixes started down the path of saying that TIMESTAMP is local time, but this is probably a misunderstanding of the original design, forced upon us by the gaps in that original design.

Further, each time we make a change (such as DRILL-8099 and DRILL-8100), we change behavior, potentially breaking a kludge that someone found to kinda-sorta make things work.

Since computers can't deal with ambiguity the way humans can, we need a solution. It is not good enough for you to think "TIMESTAMP is UTC" and me to think "TIMESTAMP is local" and for Bob to think "TIMESTAMP is Java's {{LocalDateTime}}, it has no zone." The software needs to work one way. (Software is a pain that way.)

One way to solve the issue is to introduce two new types: DATETIME and UTC_TIMESTAMP. DATETIME has no timezone. It is not local time: it is an unknown, unspecified timezone, as in all other SQL tools. It is noon on your birthday, wherever you happen to be. UTC_TIMESTAMP is a Unix-like timestamp.

We then need functions to convert. Here, we can just follow Java's {{java.time}} package: to compare a UTC_TIMESTAMP with a DATETIME, you have to specify a timezone.

This will leave the current TIMESTAMP to settle down into a LOCAL_TIMESTAMP, which will turn out to be useless and will (we can hope) fade away.

All readers and writers that work with data that is known to be UTC (such as Parquet) will use UTC_TIMESTAMP. Where the zone is not known (JDBC DATETIME), then the data type will be DATETIME.

The functions that deal with the current time need new versions: "NOW" for "CURRENT_UTC_TIMESTAMP". The "AGE" function would use a UTC baseline for UTC_TIMESTAMP, perhaps local time for a DATETIME argument.

A design should spell out the details. Since time handing is HARD, the design should lean heavily on the {{java.time}} package and just follow the enormous work that went into that package.

After years of fighting this beast, I've come to the conclusion that only the two new types (and the deprecation and gradual fading away of the "local" TIMESTAMP type) can finally resolve our never-ending stream of bugs in this area.



--
This message was sent by Atlassian Jira
(v8.20.1#820001)