[
https://issues.apache.org/jira/browse/CALCITE-5180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17637538#comment-17637538
]
Julian Hyde commented on CALCITE-5180:
--------------------------------------
Regarding "extreme BQ compatibility mode". I invented the term in that comment.
I wanted to communicate that having a function called TIMESTAMP whose return
type is not called TIMESTAMP and/or the return value does not have ISO
TIMESTAMP semantics is disconcerting.
I think this mode is what you are trying to build, using some new functions and
some form of type-aliasing.
The mapping between ISO (or Oracle) TIMESTAMP and BQ TIMESTAMP is more
problematic than you think. Let's suppose I have a value in my DB and I want to
convert it to a {{java.sql.Timestamp}}. I call the
{{ResultSet.getTimestamp(int, Calendar)}} method. Everyone agrees that a
{{java.sql.Timestamp}} is an instant (although its toString displays in the
JVM's time zone) and its value is stored in millis since UTC epoch. The BQ
TIMESTAMP is also an instant, so its value is simply copied. But the ISO
TIMESTAMP has no zone, and therefore the calendar argument is used to provide
the time zone in which to interpret the value.
Every time you implicitly convert an instant to or from a local-time a time
zone has to come into play. That conversion occurs at many places in the stack,
often in code that is out of reach of your debugger (e.g. in another JVM that
has a different time zone). Diagnosing problems is incredibly hard. I know,
because I've been there.
So, no, it is not a 'perfect fit'. It is not 'very straightforward'. It is a
disastrous design choice.
> 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)
