[
https://issues.apache.org/jira/browse/DRILL-1051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16133080#comment-16133080
]
Vitalii Diravka commented on DRILL-1051:
----------------------------------------
The issue is connected to the JDBC and converting of Joda DateTime instances to
java.sql Date, Timestamp and Time instances for old dates:
1. For dates earlier than Gregorian cutover date (depends from timezone,
usually 1582 year)
https://www.timeanddate.com/calendar/julian-gregorian-switch.html
2. For LMT (Local Mean Time) dates (depends from timezone, usually earlier than
1883 year)
https://www.timeanddate.com/time/change/usa/los-angeles?year=1883
Old behaviour:
{code}
0: jdbc:drill:zk=local> select cast(to_timestamp('1581-12-01 23:32:01',
'yyyy-MM-dd HH:mm:ss') as date) as `DATE`, to_timestamp('1581-12-01 23:32:01',
'yyyy-MM-dd HH:mm:ss') as `TIMESTAMP`, cast(to_timestamp('1581-12-01 23:32:01',
'yyyy-MM-dd HH:mm:ss') as time) as `TIME`, timeofday() from (VALUES(1));
+-------------+------------------------+-----------+----------------------------------------------+
| DATE | TIMESTAMP | TIME | EXPR$3
|
+-------------+------------------------+-----------+----------------------------------------------+
| 1581-11-20 | 1581-11-21 23:24:59.0 | 23:32:01 | 2017-08-18 06:10:36.542
America/Los_Angeles |
+-------------+------------------------+-----------+----------------------------------------------+
0: jdbc:drill:zk=local> select cast(to_timestamp('1883-11-16 01:32:01',
'yyyy-MM-dd HH:mm:ss') as date) as `DATE`, to_timestamp('1883-11-16 01:32:01',
'yyyy-MM-dd HH:mm:ss') as `TIMESTAMP`, cast(to_timestamp('1883-11-16 01:32:01',
'yyyy-MM-dd HH:mm:ss') as time) as `TIME`, timeofday() from (VALUES(1));
+-------------+------------------------+-----------+----------------------------------------------+
| DATE | TIMESTAMP | TIME | EXPR$3
|
+-------------+------------------------+-----------+----------------------------------------------+
| 1883-11-15 | 1883-11-16 01:24:59.0 | 01:32:01 | 2017-08-18 06:04:33.512
America/Los_Angeles |
+-------------+------------------------+-----------+----------------------------------------------+
1 row selected (0.249 seconds)
{code}
After fix:
{code}
0: jdbc:drill:zk=local> select cast(to_timestamp('1581-12-01 23:32:01',
'yyyy-MM-dd HH:mm:ss') as date) as `DATE`, to_timestamp('1581-12-01 23:32:01',
'yyyy-MM-dd HH:mm:ss') as `TIMESTAMP`, cast(to_timestamp('1581-12-01 23:32:01',
'yyyy-MM-dd HH:mm:ss') as time) as `TIME`, timeofday() from (VALUES(1));
+-------------+------------------------+-----------+----------------------------------------------+
| DATE | TIMESTAMP | TIME | EXPR$3
|
+-------------+------------------------+-----------+----------------------------------------------+
| 1581-12-01 | 1581-12-01 23:32:01.0 | 23:32:01 | 2017-08-18 06:12:30.837
America/Los_Angeles |
+-------------+------------------------+-----------+----------------------------------------------+
0: jdbc:drill:zk=local> select cast(to_timestamp('1883-11-16 01:32:01',
'yyyy-MM-dd HH:mm:ss') as date) as `DATE`, to_timestamp('1883-11-16 01:32:01',
'yyyy-MM-dd HH:mm:ss') as `TIMESTAMP`, cast(to_timestamp('1883-11-16 01:32:01',
'yyyy-MM-dd HH:mm:ss') as time) as `TIME`, timeofday() from (VALUES(1));
+-------------+------------------------+-----------+----------------------------------------------+
| DATE | TIMESTAMP | TIME | EXPR$3
|
+-------------+------------------------+-----------+----------------------------------------------+
| 1883-11-16 | 1883-11-16 01:32:01.0 | 01:32:01 | 2017-08-18 06:08:59.944
America/Los_Angeles |
+-------------+------------------------+-----------+----------------------------------------------+
{code}
> Casting timestamp as date gives wrong result for dates earlier than 1883
> ------------------------------------------------------------------------
>
> Key: DRILL-1051
> URL: https://issues.apache.org/jira/browse/DRILL-1051
> Project: Apache Drill
> Issue Type: Bug
> Components: Functions - Drill
> Reporter: Chun Chang
> Assignee: Vitalii Diravka
> Priority: Minor
> Fix For: Future
>
>
> #Wed Jun 18 10:27:23 PDT 2014
> git.commit.id.abbrev=894037a
> It appears casting dates earlier than year 1797 gives wrong result:
> 0: jdbc:drill:schema=dfs> select cast(c_timestamp as varchar(20)),
> cast(c_timestamp as date) from data where c_row <> 12;
> +------------+------------+
> | EXPR$0 | EXPR$1 |
> +------------+------------+
> | 1997-01-02 03:04:05 | 1997-01-02 |
> | 1997-01-02 00:00:00 | 1997-01-02 |
> | 2001-09-22 18:19:20 | 2001-09-22 |
> | 1997-02-10 17:32:01 | 1997-02-10 |
> | 1997-02-10 17:32:00 | 1997-02-10 |
> | 1997-02-11 17:32:01 | 1997-02-11 |
> | 1997-02-12 17:32:01 | 1997-02-12 |
> | 1997-02-13 17:32:01 | 1997-02-13 |
> | 1997-02-14 17:32:01 | 1997-02-14 |
> | 1997-02-15 17:32:01 | 1997-02-15 |
> | 1997-02-16 17:32:01 | 1997-02-16 |
> | 0097-02-16 17:32:01 | 0097-02-17 |
> | 0597-02-16 17:32:01 | 0597-02-13 |
> | 1097-02-16 17:32:01 | 1097-02-09 |
> | 1697-02-16 17:32:01 | 1697-02-15 |
> | 1797-02-16 17:32:01 | 1797-02-15 |
> | 1897-02-16 17:32:01 | 1897-02-16 |
> | 1997-02-16 17:32:01 | 1997-02-16 |
> | 2097-02-16 17:32:01 | 2097-02-16 |
> | 1996-02-28 17:32:01 | 1996-02-28 |
> | 1996-02-29 17:32:01 | 1996-02-29 |
> | 1996-03-01 17:32:01 | 1996-03-01 |
> +------------+------------+
> 22 rows selected (0.201 seconds)
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
