[jira] [Commented] (DRILL-1051) Casting timestamp as date gives wrong result for dates earlier than 1883
[ https://issues.apache.org/jira/browse/DRILL-1051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16173837#comment-16173837 ] Chun Chang commented on DRILL-1051: --- Automation added. > 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 > Labels: ready-to-commit > Fix For: 1.12.0 > > > #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)
[jira] [Commented] (DRILL-1051) Casting timestamp as date gives wrong result for dates earlier than 1883
[ https://issues.apache.org/jira/browse/DRILL-1051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16151682#comment-16151682 ] ASF GitHub Bot commented on DRILL-1051: --- Github user asfgit closed the pull request at: https://github.com/apache/drill/pull/915 > 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 > Labels: ready-to-commit > Fix For: 1.12.0 > > > #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)
[jira] [Commented] (DRILL-1051) Casting timestamp as date gives wrong result for dates earlier than 1883
[ https://issues.apache.org/jira/browse/DRILL-1051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16147131#comment-16147131 ] ASF GitHub Bot commented on DRILL-1051: --- Github user vdiravka commented on the issue: https://github.com/apache/drill/pull/915 @arina-ielchiieva The test failures are expected and should be corrected. Thank you. > 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 > Labels: ready-to-commit > Fix For: 1.12.0 > > > #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)
[jira] [Commented] (DRILL-1051) Casting timestamp as date gives wrong result for dates earlier than 1883
[ https://issues.apache.org/jira/browse/DRILL-1051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16141634#comment-16141634 ] ASF GitHub Bot commented on DRILL-1051: --- Github user arina-ielchiieva commented on the issue: https://github.com/apache/drill/pull/915 Two functional unit tests are failing after the changes: ``` /root/drillAutomation/drill-test-framework/framework/resources/Functional/datetime/datetime189.q Query: select min(cast(c_timestamp as timestamp) + interval '1 2:30:45.100' day to second) from basic Expected number of rows: 1 Actual number of rows from Drill: 1 Number of matching rows: 0 Number of rows missing: 1 Number of rows unexpected: 1 These rows are not expected (first 10): 0097-02-17 20:02:46.1 /root/drillAutomation/drill-test-framework/framework/resources/Functional/datetime/datetime201.q Query: select c_timestamp + c_interval_year from `interval_data.parquet` Expected number of rows: 9 Actual number of rows from Drill: 9 Number of matching rows: 8 Number of rows missing: 1 Number of rows unexpected: 1 These rows are not expected (first 10): 1862-07-30 08:03:11.0 These rows are missing (first 10): 1862-07-30 07:56:09.0 (1 time(s)) ``` It might be that tests should be corrected. @vdiravka could you please check? > 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 > Labels: ready-to-commit > Fix For: 1.12.0 > > > #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)
[jira] [Commented] (DRILL-1051) Casting timestamp as date gives wrong result for dates earlier than 1883
[
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',
'-MM-dd HH:mm:ss') as date) as `DATE`, to_timestamp('1581-12-01 23:32:01',
'-MM-dd HH:mm:ss') as `TIMESTAMP`, cast(to_timestamp('1581-12-01 23:32:01',
'-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',
'-MM-dd HH:mm:ss') as date) as `DATE`, to_timestamp('1883-11-16 01:32:01',
'-MM-dd HH:mm:ss') as `TIMESTAMP`, cast(to_timestamp('1883-11-16 01:32:01',
'-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',
'-MM-dd HH:mm:ss') as date) as `DATE`, to_timestamp('1581-12-01 23:32:01',
'-MM-dd HH:mm:ss') as `TIMESTAMP`, cast(to_timestamp('1581-12-01 23:32:01',
'-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',
'-MM-dd HH:mm:ss') as date) as `DATE`, to_timestamp('1883-11-16 01:32:01',
'-MM-dd HH:mm:ss') as `TIMESTAMP`, cast(to_timestamp('1883-11-16 01:32:01',
'-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
[jira] [Commented] (DRILL-1051) Casting timestamp as date gives wrong result for dates earlier than 1883
[ https://issues.apache.org/jira/browse/DRILL-1051?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16133042#comment-16133042 ] ASF GitHub Bot commented on DRILL-1051: --- GitHub user vdiravka opened a pull request: https://github.com/apache/drill/pull/915 DRILL-1051: Casting timestamp as date gives wrong result for dates ea… …rlier than 1883 - Fix DateAccessor's, TimestampAccessor's and TimeAccessor's converting joda time to java.sql You can merge this pull request into a Git repository by running: $ git pull https://github.com/vdiravka/drill DRILL-1051 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/drill/pull/915.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #915 commit b3cee2294b04e35e75e7b471df62a9233483c183 Author: Vitalii Diravka Date: 2017-08-15T17:51:10Z DRILL-1051: Casting timestamp as date gives wrong result for dates earlier than 1883 - Fix DateAccessor's, TimestampAccessor's and TimeAccessor's converting joda time to java.sql > 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)
