[
https://issues.apache.org/jira/browse/IMPALA-4018?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16763896#comment-16763896
]
Greg Rahn commented on IMPALA-4018:
-----------------------------------
Our goal with this is to follow the SQL standard and make it as RDBMS
compatible as possible – not necessary mirror 100% of the functionality from
SimpleDate formats, but I think the SQL masks should handle most if not all
cases of SimpleDate.
Digging into those points:
- For string to date conversion, SQL always uses the "full precision" of the
mask. Eg: you still use 'yyyy-mm-dd' to convert '300-1-1' to a date, you do not
use 'yyy-m-d'. For date to string, using less than the full precision of the
mask leaves out data starting from the left. Eg: 'yyy' mask for '2019-01-31'
returns '019'
- For name of month, there are widely adopted extensions to the standard
(assuming English locale here for the moment to keep non-numeric date parts
simple – and why this is not part of the ISO standard as names change with
locale):
-- 'month' returns 'february'
-- 'Month' returns 'February'
-- 'MONTH' returns 'FEBRUARY'
-- 'mon, 'Mon', 'MON ' returns 'feb', 'Feb', 'FEB'
- I think sticking to the standard works here, but I'm open to extending it.
The standard is only saying that as long as the delimiter is one of "datetime
template delimiter", it need not be explicitly provided. For example, if the
string is '2019,01,31' , CAST('2019,01,31' AS DATE) should work (no mask
needed), but if the string is '2019~01~31', one may be required to use
CAST('2019~01~31' AS DATE FORMAT 'yyyy~mm~dd') since tilde is not in the
"datetime template delimiter"
As part of this effort, I think we should implement as many of the common masks
as possible to broaden compatibility. Where there is question, let's discuss.
For reference:
- [IBM
Db2|https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0007107.html]
-
[PostgreSQL|https://www.postgresql.org/docs/7.4/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE]
-
[Vertica|https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TemplatePatternsForDateTimeFormatting.htm]
-
[Oracle|https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Format-Models.html#GUID-EAB212CF-C525-4ED8-9D3F-C76D08EEBC7A]
> Add support for SQL:2016 datetime templates/patterns/masks to CAST(... AS ...
> FORMAT <template>)
> ------------------------------------------------------------------------------------------------
>
> Key: IMPALA-4018
> URL: https://issues.apache.org/jira/browse/IMPALA-4018
> Project: IMPALA
> Issue Type: New Feature
> Components: Frontend
> Affects Versions: Impala 2.2.4
> Reporter: Greg Rahn
> Assignee: Gabor Kaszab
> Priority: Critical
> Labels: ansi-sql, compatibility, sql-language
>
> *Summary*
> The format masks/templates for currently are implemented using the [Java
> SimpleDateFormat
> patterns|http://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html],
> and although this is what Hive has implemented, it is not what most standard
> SQL systems implement. For example see
> [Vertica|https://my.vertica.com/docs/7.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TemplatePatternsForDateTimeFormatting.htm],
>
> [Netezza|http://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_extns_templ_patterns_date_time_conv.html],
>
> [Oracle|https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212],
> and
> [PostgreSQL|https://www.postgresql.org/docs/9.5/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE].
>
> *Examples of incompatibilities*
> {noformat}
> -- PostgreSQL/Netezza/Vertica/Oracle
> select to_timestamp('May 15, 2015 12:00:00', 'mon dd, yyyy hh:mi:ss');
> -- Impala
> select to_timestamp('May 15, 2015 12:00:00', 'MMM dd, yyyy HH:mm:ss');
> -- PostgreSQL/Netezza/Vertica/Oracle
> select to_timestamp('2015-02-14 20:19:07','yyyy-mm-dd hh24:mi:ss');
> -- Impala
> select to_timestamp('2015-02-14 20:19:07','yyyy-MM-dd HH:mm:ss');
> -- Vertica/Oracle
> select to_timestamp('2015-02-14 20:19:07.123456','yyyy-mm-dd hh24:mi:ss.ff');
> -- Impala
> select to_timestamp('2015-02-14 20:19:07.123456','yyyy-MM-dd
> HH:mm:ss.SSSSSS');
> {noformat}
> *Considerations*
> Because this is a change in default behavior for to_timestamp(), if possible,
> having a feature flag to revert to the legacy Java SimpleDateFormat patterns
> should be strongly considered. This would allow users to chose the behavior
> they desire and scope it to a session if need be.
> SQL:2016 defines the following datetime templates
> {noformat}
> <datetime template> ::=
> { <datetime template part> }...
> <datetime template part> ::=
> <datetime template field>
> | <datetime template delimiter>
> <datetime template field> ::=
> <datetime template year>
> | <datetime template rounded year>
> | <datetime template month>
> | <datetime template day of month>
> | <datetime template day of year>
> | <datetime template 12-hour>
> | <datetime template 24-hour>
> | <datetime template minute>
> | <datetime template second of minute>
> | <datetime template second of day>
> | <datetime template fraction>
> | <datetime template am/pm>
> | <datetime template time zone hour>
> | <datetime template time zone minute>
> <datetime template delimiter> ::=
> <minus sign>
> | <period>
> | <solidus>
> | <comma>
> | <apostrophe>
> | <semicolon>
> | <colon>
> | <space>
> <datetime template year> ::=
> YYYY | YYY | YY | Y
> <datetime template rounded year> ::=
> RRRR | RR
> <datetime template month> ::=
> MM
> <datetime template day of month> ::=
> DD
> <datetime template day of year> ::=
> DDD
> <datetime template 12-hour> ::=
> HH | HH12
> <datetime template 24-hour> ::=
> HH24
> <datetime template minute> ::=
> MI
> <datetime template second of minute> ::=
> SS
> <datetime template second of day> ::=
> SSSSS
> <datetime template fraction> ::=
> FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
> <datetime template am/pm> ::=
> A.M. | P.M.
> <datetime template time zone hour> ::=
> TZH
> <datetime template time zone minute> ::=
> TZM
> {noformat}
> SQL:2016 also introduced the FORMAT clause for CAST which is the standard way
> to do string <> datetime conversions
> {noformat}
> <cast specification> ::=
> CAST <left paren>
> <cast operand> AS <cast target>
> [ FORMAT <cast template> ]
> <right paren>
> <cast operand> ::=
> <value expression>
> | <implicitly typed value specification>
> <cast target> ::=
> <domain name>
> | <data type>
> <cast template> ::=
> <character string literal>
> {noformat}
> For example:
> {noformat}
> CAST(<datetime> AS <char string type> [FORMAT <template>])
> CAST(<char string> AS <datetime type> [FORMAT <template>])
> cast(dt as string format 'DD-MM-YYYY')
> cast('01-05-2017' as date format 'DD-MM-YYYY')
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
