TIME, TIMESTAMP semantics roadmap

[findepi]

( This is a roadmap (epic) issue for the problem described in #7122. I post it separately, so that the roadmap items don't get buried among other comments )

• use reasonable temporal representation in tests (testability)
• feature config and session toggles
• use non-UTC zone as default in test and better default JVM zone (testability) (= Change time zone used in tests to better test date/time/zone/JVM-related corner cases  #10078, more details there)
• (TIME/TIMESTAMP W/O TIME ZONE semantics fix - continuation (v3) #10193) engine semantics fixes
• all casts from TIME* to TIMESTAMP* -- they fill date fields with current_date, not 1970-01-01 (see Remove support for political time zones from TIME WITH TIME ZONE #10103 (comment) & following)
• Remove support for political time zones from TIME WITH TIME ZONE (= Remove support for political time zones from TIME WITH TIME ZONE #10103, more details there)
• Hive changes: Hive TIMESTAMP should be matched to Presto TIMESTAMP WITH TIME ZONE
  • however https://issues.apache.org/jira/browse/HIVE-12191 & https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-TimestampstimestampTimestamps look as if Hive has changed / is going to change it's timestamp semantics, so the conversion might be hive's version dependent? References: https://issues.apache.org/jira/browse/HIVE-16614?focusedCommentId=16425288&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-16425288, https://issues.apache.org/jira/browse/HIVE-12192
• Hive changes in file handlers: ORC & Parquet (especially for predicate pushdown, e.g. Add support for DATE predicate pushdown with Parquet via min/max and … #10181 (comment)), others
• Raptor changes (...?)
• JDBC connectors changes: reading (e.g. StandardReadMappings#timestampReadMapping assumes "old" semantics), writing and predicate pushdown
  • this may need to be split into several bullets, as databases generally vary in their support for temporal types
• other connectors ................ (to be enumerated)
• Presto JDBC changes (Represent TIMESTAMP W/TZ as ZonedDateTime in JDBC #9951, etc.)
• Q: what is the complete set of implicit coercions we are going to have between TIME, TIME WITH TZ, TIMESTAMP, TIMESTAMP WITH TZ?
• Q: should from_unixtime, to_unixtime continue to work with TIMESTAMP?
• enable "new" correct semantics by default
• get rid of catch Exception for checking if input is parsable in DateTimeUtils

(Note: since GH doesn't send updates for edits, when adding a bullet please be sure sure to add a comment too.)

cc @martint @dain @haozhun @losipiuk

[dpat]

regarding this roadmap:

I cannot seem to determine which tasks serve to implement this functionality:
Selecting values from TIMESTAMP column should return same result set no matter what is the client's session timezone

is this still a goal in fixing issue #7122 ?

thanks