Connection time zone concept

[Gian Merlino <gi...@imply.io>]

Hi Calcites,

In Druid SQL we have a connection time zone concept; see the mentions of
"sqlTimeZone" here: http://druid.io/docs/latest/querying/sql.html. It
affects the time zone of any operation involving timestamps, including the
interpretation of literals, the behavior of functions like FLOOR and
EXTRACT, and the interpretation of Druid's built-in time column (which is
stored as 64-bit millisecond-resolution epoch time but is interpreted as a
timestamp type in SQL).

This is all well and good, but the issue I'm having now is that Calcite's
"SqlLiteral.value" function translates INTERVAL '1' DAY and similar
literals into millisecond values. This works fine in UTC but doesn't work
in any time zone that has daylight savings time -- since in those zones,
not all days are the same length. I don't see a way to work around this
without modifying Calcite, since really, INTERVAL '1' DAY and INTERVAL '24'
HOUR shouldn't be treated the same, but they are.

Fwiw, PostgreSQL has a similar session time zone concept, and it does
potentially treat 1 day and 24 hours differently: https://www.
postgresql.org/docs/9.2/static/functions-datetime.html

So I have a couple of questions.

1) Does it make sense to modify Calcite to support this use case?

2) If "yes" then how? The simplest thing I can think of is
changing INTERVAL_DAY_TIME literals to store their values as Joda Periods (
http://joda-time.sourceforge.net/apidocs/org/joda/time/Period.html) or
similar, rather than as a number of milliseconds, and updating downstream
code appropriately. This wouldn't be backwards compatible; is that ok? If
it's not ok, maybe it should be behind an option?

Thanks as always.

Gian

Re: Connection time zone concept

[Julian Hyde <jh...@apache.org>]

I don’t know the answer but I would start off with a solid set of data types, each with obvious semantics and defined by a standard:

* An instant (per Joda) represents a moment in time (you can think of it as an offset from UTC epoch). Adding a day to an instant is the same as adding 24 hours. Given an instant you could format it in a particular time zone.

* A SQL TIMESTAMP value has no time zone. It is just a string such as ‘1978-01-02 23:45:56’. Again, adding 24 hours is the same as adding a day.

* A SQL TIMESTAMP WITH TIME ZONE value putatively contains an instant plus a time zone identifier. Could adding an hour cause it to shift into a different time zone? I don’t know: some time zones shift, and some are fixed: Pacific Daylight Time is always UTC -7, Pacific Standard Time is always UTC -8, whereas the US/Pacific time zone region shifts from PDT to PST in November each year, and back in April.

* We recently introduced TIMESTAMP WITH LOCAL TIME ZONE; the goal is to simplify integration with Druid, but the type is based upon Oracle’s type of the same name.

As I said, I don’t know the answer, and I don’t even want to spend the time required to become an expert on this matter. I am more inclined to follow Oracle’s lead than Postgres. Oracle documentation [1] states "Oracle performs all timestamp arithmetic in UTC time” and “ ERROR_ON_OVERLAP_TIME is a session parameter that determines how Oracle handles an ambiguous datetime boundary value … When ERROR_ON_OVERLAP_TIME is FALSE, then ambiguous datetime values are assumed to be the standard time representation for the region”. Postgres lets in through the door slippery concepts such as 25 hour days, and that can only end badly.

I know that applications still need to do things like adding intervals across daylight savings time boundaries. Can we accomplish this using new functions, rather than by changing core data types and their semantics?

Julian

[1] https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm <https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm>


> On Nov 20, 2017, at 9:52 AM, Gian Merlino <gi...@imply.io> wrote:
> 
> Hi Calcites,
> 
> In Druid SQL we have a connection time zone concept; see the mentions of
> "sqlTimeZone" here: http://druid.io/docs/latest/querying/sql.html. It
> affects the time zone of any operation involving timestamps, including the
> interpretation of literals, the behavior of functions like FLOOR and
> EXTRACT, and the interpretation of Druid's built-in time column (which is
> stored as 64-bit millisecond-resolution epoch time but is interpreted as a
> timestamp type in SQL).
> 
> This is all well and good, but the issue I'm having now is that Calcite's
> "SqlLiteral.value" function translates INTERVAL '1' DAY and similar
> literals into millisecond values. This works fine in UTC but doesn't work
> in any time zone that has daylight savings time -- since in those zones,
> not all days are the same length. I don't see a way to work around this
> without modifying Calcite, since really, INTERVAL '1' DAY and INTERVAL '24'
> HOUR shouldn't be treated the same, but they are.
> 
> Fwiw, PostgreSQL has a similar session time zone concept, and it does
> potentially treat 1 day and 24 hours differently: https://www.
> postgresql.org/docs/9.2/static/functions-datetime.html
> 
> So I have a couple of questions.
> 
> 1) Does it make sense to modify Calcite to support this use case?
> 
> 2) If "yes" then how? The simplest thing I can think of is
> changing INTERVAL_DAY_TIME literals to store their values as Joda Periods (
> http://joda-time.sourceforge.net/apidocs/org/joda/time/Period.html) or
> similar, rather than as a number of milliseconds, and updating downstream
> code appropriately. This wouldn't be backwards compatible; is that ok? If
> it's not ok, maybe it should be behind an option?
> 
> Thanks as always.
> 
> Gian