You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2023/03/20 15:48:00 UTC
[jira] [Comment Edited] (HIVE-27156) Wrong results when CAST timestamp literal with timezone to TIMESTAMP
[ https://issues.apache.org/jira/browse/HIVE-27156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17702790#comment-17702790 ]
Stamatis Zampetakis edited comment on HIVE-27156 at 3/20/23 3:47 PM:
---------------------------------------------------------------------
I did some small experiments in few other DBMS and here are the results. Note that the syntax is not entirely identical but I tried to find the most reasonable alternatives.
*postgres:12*
{noformat}
postgres=# select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp);
timestamp
----------------------------
2020-06-28 22:17:33.123456
(1 row)
postgres=# select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp);
ERROR: time zone "europe/amsterd" not recognized
LINE 1: select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as t...
^
postgres=# select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as timestamp);
ERROR: time zone "invalid/zone" not recognized
LINE 1: select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as tim...
^
{noformat}
*mysql:8.0.32*
{noformat}
mysql> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as datetime(6));
+--------------------------------------------------------------------+
| cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as datetime(6)) |
+--------------------------------------------------------------------+
| 2020-06-28 22:17:33.123456 |
+--------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as datetime(6));
+------------------------------------------------------------------+
| cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as datetime(6)) |
+------------------------------------------------------------------+
| 2020-06-28 22:17:33.123456 |
+------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as datetime(6));
+----------------------------------------------------------------+
| cast('2020-06-28 22:17:33.123456 Invalid/Zone' as datetime(6)) |
+----------------------------------------------------------------+
| 2020-06-28 22:17:33.123456 |
+----------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
{noformat}
*oracle:12.2.0.1-slim*
{noformat}
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI.SS.FF';
Session altered.
SQL> select cast('2020-06-28 22:17:33.123456' as timestamp) from dual;
CAST('2020-06-2822:17:33.123456'ASTIMESTAMP)
---------------------------------------------------------------------------
2020-06-28 22:17.33.123456
SQL> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp) from dual;
select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI.SS.FF TZR';
Session altered.
SQL> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp with time zone) from dual;
CAST('2020-06-2822:17:33.123456EUROPE/AMSTERDAM'ASTIMESTAMPWITHTIMEZONE)
---------------------------------------------------------------------------
2020-06-28 22:17.33.123456 EUROPE/AMSTERDAM
SQL> select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp with time zone) from dual;
select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp with time zone) from dual
*
ERROR at line 1:
ORA-01882: timezone region not found
{noformat}
Summing up:
||String literal||postgres:12||mysql:8.0.32||oracle:12.2.0.1-slim||Hive:4.0.0-alpha2||Hive:3.1.3||
|2020-06-28 22:17:33.123456 Invalid/Zone|ERROR|2020-06-28 22:17:33.123456|ERROR|2020-06-28 00:00:00|2020-06-28 00:00:00|
|2020-06-28 22:17:33.123456 Europe/Amsterdam|2020-06-28 22:17:33.123456|2020-06-28 22:17:33.123456|ERROR|2020-06-28 22:17:33.123456|2020-06-28 22:17:33.123456|
was (Author: zabetak):
I did some small experiments in few other DBMS and here are the results. Note that the syntax is not entirely identical but I tried to find the most reasonable alternatives.
*postgres:12*
{noformat}
postgres=# select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp);
timestamp
----------------------------
2020-06-28 22:17:33.123456
(1 row)
postgres=# select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp);
ERROR: time zone "europe/amsterd" not recognized
LINE 1: select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as t...
^
postgres=# select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as timestamp);
ERROR: time zone "invalid/zone" not recognized
LINE 1: select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as tim...
^
{noformat}
*mysql:8.0.32*
{noformat}
mysql> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as datetime(6));
+--------------------------------------------------------------------+
| cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as datetime(6)) |
+--------------------------------------------------------------------+
| 2020-06-28 22:17:33.123456 |
+--------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as datetime(6));
+------------------------------------------------------------------+
| cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as datetime(6)) |
+------------------------------------------------------------------+
| 2020-06-28 22:17:33.123456 |
+------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as datetime(6));
+----------------------------------------------------------------+
| cast('2020-06-28 22:17:33.123456 Invalid/Zone' as datetime(6)) |
+----------------------------------------------------------------+
| 2020-06-28 22:17:33.123456 |
+----------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
{noformat}
*oracle:12.2.0.1-slim*
{noformat}
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI.SS.FF';
Session altered.
SQL> select cast('2020-06-28 22:17:33.123456' as timestamp) from dual;
CAST('2020-06-2822:17:33.123456'ASTIMESTAMP)
---------------------------------------------------------------------------
2020-06-28 22:17.33.123456
SQL> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp) from dual;
select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI.SS.FF TZR';
Session altered.
SQL> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp with time zone) from dual;
CAST('2020-06-2822:17:33.123456EUROPE/AMSTERDAM'ASTIMESTAMPWITHTIMEZONE)
---------------------------------------------------------------------------
2020-06-28 22:17.33.123456 EUROPE/AMSTERDAM
SQL> select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp with time zone) from dual;
select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp with time zone) from dual
*
ERROR at line 1:
ORA-01882: timezone region not found
{noformat}
Summing up:
||String literal||postgres:12||mysql:8.0.32||oracle:12.2.0.1-slim||Hive:4.0.0-alpha2||
|2020-06-28 22:17:33.123456 Invalid/Zone|ERROR|2020-06-28 22:17:33.123456|ERROR|2020-06-28 00:00:00|
|2020-06-28 22:17:33.123456 Europe/Amsterdam|2020-06-28 22:17:33.123456|2020-06-28 22:17:33.123456|ERROR|2020-06-28 22:17:33.123456|
> Wrong results when CAST timestamp literal with timezone to TIMESTAMP
> --------------------------------------------------------------------
>
> Key: HIVE-27156
> URL: https://issues.apache.org/jira/browse/HIVE-27156
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
> Affects Versions: 4.0.0-alpha-2
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
>
> Casting a timestamp literal with an invalid timezone to the TIMESTAMP datatype results into a timestamp with the time part truncated to midnight (00:00:00).
> *Case I*
> {code:sql}
> select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp);
> {code}
> +Actual+
> |2020-06-28 00:00:00|
> +Expected+
> |NULL/ERROR/2020-06-28 22:17:33.123456|
> *Case II*
> {code:sql}
> select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as timestamp);
> {code}
> +Actual+
> |2020-06-28 00:00:00|
> +Expected+
> |NULL/ERROR/2020-06-28 22:17:33.123456|
> The existing documentation does not cover what should be the output in the cases above:
> * https://cwiki.apache.org/confluence/display/hive/languagemanual+types#LanguageManualTypes-TimestampstimestampTimestamps
> * https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types
> *Case III*
> Another subtle but important case is the following where the timestamp literal has a valid timezone but we are attempting a cast to a datatype that does not store the timezone.
> {code:sql}
> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp);
> {code}
> +Actual+
> |2020-06-28 22:17:33.123456|
> The correctness of the last result is debatable since someone would expect a NULL or ERROR.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)