[
https://issues.apache.org/jira/browse/CALCITE-5180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17637515#comment-17637515
]
Will Noble commented on CALCITE-5180:
-------------------------------------
This is the first I'm hearing of extreme BQ compatibility. Grepping
case-insensitively for {{EBQC}} and {{'extreme.*compatibility'}} both turned up
nothing in Calcite. Is this an existing setting?
I'm assuming you're going with Oracle's definitions of these types, in which
case they have a handy guide to [picking which type to
use|https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG244].
Here's my take:
* {{TIMESTAMP WITH TIME ZONE}} is clearly not appropriate because BigQuery
timestamps have no time zone.
* {{TIMESTAMP WITH LOCAL TIME ZONE}} "is appropriate when the original time
zone is of no interest, but the relative times of events are important and
daylight saving adjustment does not have to be accurate. The time zone
conversion that this data type performs to and from the database time zone is
asymmetrical, due to the daylight saving adjustment. Because this data type
does not preserve the time zone information, it does not distinguish values
near the adjustment in fall, whether they are daylight saving time or standard
time. This confusion between distinct instants can cause an application to
behave unexpectedly, especially if the adjustment takes place during the normal
working hours of a user." -- this also doesn't seem appropriate to me.
* {{TIMESTAMP}} seems most appropriate to me. In Oracle, this is "the time of
an event without the time zone", and according to the [BigQuery
docs|https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timezone_definitions],
a {{TIMESTAMP}} "represents an absolute point in time, independent of any time
zone". Seems like a perfect fit. It's also very straightforward.
I believe we should add a new datatype to represent {{DATETIME}}. Neither it,
not BQ's {{TIMESTAMP}}, map naturally to any of the time zone- related datetime
types in the Oracle docs.
> Implement BigQuery functions for DATE, TIME, TIMESTAMP, DATETIME
> ----------------------------------------------------------------
>
> Key: CALCITE-5180
> URL: https://issues.apache.org/jira/browse/CALCITE-5180
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Will Noble
> Priority: Major
> Labels: pull-request-available
> Time Spent: 3h 20m
> Remaining Estimate: 0h
>
> Implement missing BigQuery functions for DATE, TIME, TIMESTAMP, DATETIME.
> Functions include:
> * CURRENT_DATE, CURRENT_DATETIME, CURRENT_TIME with timezone;
> * EXTRACT with time unit values WEEK(weekday), DATE, TIME;
> * various DATE, TIME, TIMESTAMP, DATETIME constructor functions;
> * DATE_ADD, DATETIME_ADD, TIME_ADD, TIMESTAMP_ADD;
> * DATE_SUB, DATETIME_SUB, TIME_SUB, TIMESTAMP_SUB;
> * DATE_DIFF, DATETIME_DIFF, TIME_DIFF, TIMESTAMP_DIFF;
> * DATE_TRUNC, DATETIME_TRUNC, TIME_TRUNC, TIMESTAMP_TRUNC;
> * LAST_DAY;
> * FORMAT_DATE, FORMAT_DATETIME, FORMAT_TIME, FORMAT_TIMESTAMP;
> * PARSE_DATE, PARSE_DATETIME, PARSE_TIME, PARSE_TIMESTAMP.
> The following functions are already implemented: CURRENT_DATETME,
> DATE(string), DATE_FROM_UNIX_DATE, TIMESTAMP_MICROS, TIMESTAMP_MILLIS,
> TIMESTAMP_SECONDS, UNIX_MICROS, UNIX_MILLIS, UNIX_SECONDS, UNIX_DATE.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
