[Design Discussion] TIMESTAMP and TIME ZONE behavior

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