You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "xueyu (JIRA)" <ji...@apache.org> on 2018/08/16 14:04:00 UTC
[jira] [Commented] (FLINK-10032) Rework SQL DATE_FORMAT function
[ https://issues.apache.org/jira/browse/FLINK-10032?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16582574#comment-16582574 ]
xueyu commented on FLINK-10032:
-------------------------------
whether or not we also support date_format with string type parameter such as date_format('2018-08-19 10:00:00', '%Y,%m,%d'), it maybe avoid time zone problem, just treat it like string format. This syntax is valid for mysql. [~twalthr]
And how about we support FROM_UNIXTIME and UNIX_TIMESTAMP function, such as from_unixtime(unix_timestamp('2018-08-19 10:10:10'), '%Y,%d,%m,%H,%i,%s'). Unix timestamp conversion is common used in hive and mysql, and could also replace date_format function sometimes.
> Rework SQL DATE_FORMAT function
> -------------------------------
>
> Key: FLINK-10032
> URL: https://issues.apache.org/jira/browse/FLINK-10032
> Project: Flink
> Issue Type: Bug
> Components: Table API & SQL
> Affects Versions: 1.5.2
> Environment: OS: Mac OSX.
> Flink: Latest Flink 1.5.2 SQL-CLI embedded.
> Reporter: Rann Tao
> Assignee: Timo Walther
> Priority: Major
> Attachments: image-2018-08-02-21-16-38-746.png
>
>
> As Follows, but it works in sql program, and input string is expected result.
>
> !image-2018-08-02-21-16-38-746.png!
> Logs:
> {code}
> org.apache.flink.table.client.gateway.SqlExecutionException: Invalid SQL query.
> at org.apache.flink.table.client.gateway.local.LocalExecutor.executeQueryInternal(LocalExecutor.java:396)
> at org.apache.flink.table.client.gateway.local.LocalExecutor.executeQuery(LocalExecutor.java:239)
> at org.apache.flink.table.client.cli.CliClient.callSelect(CliClient.java:376)
> at org.apache.flink.table.client.cli.CliClient.callCommand(CliClient.java:255)
> at java.util.Optional.ifPresent(Optional.java:159)
> at org.apache.flink.table.client.cli.CliClient.open(CliClient.java:184)
> at org.apache.flink.table.client.SqlClient.openCli(SqlClient.java:117)
> at org.apache.flink.table.client.SqlClient.start(SqlClient.java:101)
> at org.apache.flink.table.client.SqlClient.main(SqlClient.java:172)
> Caused by: java.lang.NumberFormatException: For input string: "2018,08,01 14:00:00"
> at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
> at java.lang.Long.parseLong(Long.java:589)
> at java.lang.Long.valueOf(Long.java:803)
> at ExpressionReducer$15.map(Unknown Source)
> at org.apache.flink.table.codegen.ExpressionReducer.reduce(ExpressionReducer.scala:108)
> at org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressionsInternal(ReduceExpressionsRule.java:620)
> at org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:540)
> at org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:288)
> at org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:317)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
> at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:415)
> at org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:252)
> at org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:127)
> at org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:211)
> at org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:198)
> at org.apache.flink.table.api.TableEnvironment.runHepPlanner(TableEnvironment.scala:258)
> at org.apache.flink.table.api.StreamTableEnvironment.optimize(StreamTableEnvironment.scala:827)
> at org.apache.flink.table.api.StreamTableEnvironment.translate(StreamTableEnvironment.scala:892)
> at org.apache.flink.table.api.StreamTableEnvironment.writeToSink(StreamTableEnvironment.scala:344)
> at org.apache.flink.table.api.Table.writeToSink(table.scala:862)
> at org.apache.flink.table.client.gateway.local.LocalExecutor.executeQueryInternal(LocalExecutor.java:389)
> ... 8 more
> {code}
> The reason for this exception is a wrong signature definition in {{org.apache.flink.table.functions.sql.ScalarSqlFunctions}}.
> While testing this 2 line fix I found an even bigger problem that makes DATE_FORMAT almost not usable. The output is time zone dependent. Although Java's SQL timestamp are timezone dependent usually we correct the offset such that the string output does not depend on a timezone.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)