[Design Discussion] TIMESTAMP and TIME ZONE behavior

[agavra]

Hello everyone, I'm creating an issue to track the design discussion of the desired behavior for a date time type that contains time zone information. I conducted a survey of Postgres, MySQL, Trino and SQL Server and noticed that there is no standardized behavior across different SQL systems: https://docs.google.com/document/d/1Dvz7NJ2eliIFm6O1Ra4Azs_WFWR98kHOyDCznIwaXow/edit#

We can essentially choose one of three options:

1. standardize datetime behavior against an existing system
2. pick-and-choose behaviors from systems that we think makes the most sense
3. come up with our own desired behaviors

I believe (1) is likely the best option, or we risk backing ourselves up into inconsistent corners. That option will also allow us to move fastest when implementing additional date time types.

The suggestion on the google doc is to follow Postgres' lead. Please share your thoughts here and on the document!

c.f. https://github.com/apache/pinot/issues/9575 https://github.com/apache/pinot/issues/8045

[walterddr]

CC @nizarhejazi @siddharthteotia

[siddharthteotia]

Thanks for starting this.

@walterddr @agavra - Do we also want to use this opportunity to get some consensus on what is our end state w.r.t everything about date/datetime/timestamp related support as first class types.

Example - @vvivekiyer also created issue regarding INTERVAL / TIMESPAN support. Are we considering this as part of discussion ?

I am not saying fold everything into one but may be useful to have discussion at the same time with all the necessary context to see what are the missing ones, what we want to add support for, what we don't want to etc

https://github.com/apache/pinot/issues/9577

[Jackie-Jiang]

@siddharthteotia @vvivekiyer Yes, we want to use this opportunity to discuss the overall handling of all date/time related types. Once we all agree on the high level direction (the convention to follow), the remaining tasks are just implementing each of them.

[siddharthteotia]

@siddharthteotia @vvivekiyer Yes, we want to use this opportunity to discuss the overall handling of all date/time related types. Once we all agree on the high level direction (the convention to follow), the remaining tasks are just implementing each of them.

Perfect. Thanks cc @vvivekiyer

[gortiz]

As usual, following the Postgres design looks like a good idea

[kishoreg]

+1000 on moving towards postgres.