phoenix upsert date 类型数据

来源：互联网发布：日语考试知乎编辑：程序博客网时间：2024/06/03 14:33

在phoenix中upsert date类型数据如下sql:

upsert into "test_date_type" (ID,"birthday") values (102, '2013-09-01 09:00:02');

执行时会有如下错误信息：

com.salesforce.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type mismatch. DATE and VARCHAR for 2013-09-01 09:00:02    at com.salesforce.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:126)    at com.salesforce.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:108)    at com.salesforce.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:633)    at com.salesforce.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:609)    at com.salesforce.phoenix.parse.LiteralParseNode.accept(LiteralParseNode.java:70)    at com.salesforce.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:554)    at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:290)    at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.optimizePlan(PhoenixStatement.java:295)    at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.executeUpdate(PhoenixStatement.java:279)    at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.execute(PhoenixStatement.java:272)    at com.salesforce.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1014)>

通常在关系型数据库中我们可以用字符串来表示一个date类型，但是在phoenix中是不行的，会抛出TypeMismatchException: ERROR 203 (22005): Type mismatch. DATE and VARCHAR异常。

正确的做法是使用TO_DATE函数：

upsert into "test_date_type" (ID,"birthday") values (102, TO_DATE('2013-09-01 09:00:02'));

0 0