You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues-all@impala.apache.org by "Gabor Kaszab (JIRA)" <ji...@apache.org> on 2019/04/26 14:14:00 UTC
[jira] [Commented] (IMPALA-4018) Add support for SQL:2016 datetime
templates/patterns/masks to CAST(... AS ... FORMAT )
[ https://issues.apache.org/jira/browse/IMPALA-4018?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16826993#comment-16826993 ]
Gabor Kaszab commented on IMPALA-4018:
--------------------------------------
Hey [~grahn],
Meanwhile, I have reached out to the Spark community and [~maze] did an initial run through on the proposal doc. His initial opinion was that it feels unnatural to have the existing functionality toggle between the old and new patterns while CAST(..FORMAT..) uses the new patterns only. We agreed that this introduces a source of confusion on the user's side and would be much cleaner to implement the toggling mechanism for CAST(..FORMAT..) as well. This way all the functions within Impala would use the same pattern matching and the flag would change all of them and the users won't experience that e.g. to_timestamp() uses the old patterns while CAST(..FORMAT..) uses the new ones: Depending on the value of the flag all of them would use either the old or the new functionality.
What do you think? Can we revive the conversation we had around this?
Cheers,
Gabor
> 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: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org