drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vitalii Diravka (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-1051) Casting timestamp as date gives wrong result for dates earlier than 1883
Date Fri, 18 Aug 2017 15:02:00 GMT

    [ 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)

Mime
View raw message