[
https://issues.apache.org/jira/browse/DRILL-6242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16454833#comment-16454833
]
ASF GitHub Bot commented on DRILL-6242:
---------------------------------------
Github user jiang-wu commented on the issue:
https://github.com/apache/drill/pull/1184
Actually, JDBC representation is not he hard problem here. I ran into most
of the problems dealing with the timezones surrounding the data|time|timestamp.
java.sql.Struct and Array are interfaces and not actual classes. So the
Current JDBC returning Map|List object for complex values are fine. You can
just declare JsonStringHashMap implements Struct, and JsonStringArryaList
implements Array.
Now the harder issue. The semantics of "date", "time" are tricker
comparing to "timestamp". Timestamp is understood to be an instant in time
(java/joda Instant class). Timestamp is a single point in time and not
impacted by time zones.
date and time can have two uses:
1) logical date and time, which is not fixed point or range in time. e.g.
2018-01-01 is the new year day and this day happens for a different 24 hour
window depending on where your time zone is. So this is a logical date, and we
don't celebrate the start of the New Year day at the same time.
2) date and time with offset/timezone -- This refers to a specific point or
range in time. This type of date/time is absolute. e.g. NYSE opens on "7:30
am EST". Regardless of the timezone you are at, this time is the same for
everyone.
joda/java8 time package have the proper handling for 1) logical date, time
(using local date time classes); 2) absolute date, time (using offset date time
classes); and 3) timestamp (instant class) various method exists for you to
apply conversions between these based on the heuristics you want to apply.
I feel for Drill, we need to first decide what behavior do we want to
support first. Then go from there.
> Output format for nested date, time, timestamp values in an object hierarchy
> ----------------------------------------------------------------------------
>
> Key: DRILL-6242
> URL: https://issues.apache.org/jira/browse/DRILL-6242
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Data Types
> Affects Versions: 1.12.0
> Reporter: Jiang Wu
> Assignee: Jiang Wu
> Priority: Major
> Fix For: 1.14.0
>
>
> Some storages (mapr db, mongo db, etc.) have hierarchical objects that
> contain nested fields of date, time, timestamp types. When a query returns
> these objects, the output format for the nested date, time, timestamp, are
> showing the internal object (org.joda.time.DateTime), rather than the logical
> data value.
> For example. Suppose in MongoDB, we have a single object that looks like
> this:
> {code:java}
> > db.test.findOne();
> {
> "_id" : ObjectId("5aa8487d470dd39a635a12f5"),
> "name" : "orange",
> "context" : {
> "date" : ISODate("2018-03-13T21:52:54.940Z"),
> "user" : "jack"
> }
> }
> {code}
> Then connect Drill to the above MongoDB storage, and run the following query
> within Drill:
> {code:java}
> > select t.context.`date`, t.context from test t;
> +--------+---------+
> | EXPR$0 | context |
> +--------+---------+
> | 2018-03-13 |
> {"date":{"dayOfYear":72,"year":2018,"dayOfMonth":13,"dayOfWeek":2,"era":1,"millisOfDay":78774940,"weekOfWeekyear":11,"weekyear":2018,"monthOfYear":3,"yearOfEra":2018,"yearOfCentury":18,"centuryOfEra":20,"millisOfSecond":940,"secondOfMinute":54,"secondOfDay":78774,"minuteOfHour":52,"minuteOfDay":1312,"hourOfDay":21,"zone":{"fixed":true,"id":"UTC"},"millis":1520977974940,"chronology":{"zone":{"fixed":true,"id":"UTC"}},"afterNow":false,"beforeNow":true,"equalNow":false},"user":"jack"}
> |
> {code}
> We can see that from the above output, when the date field is retrieved as a
> top level column, Drill outputs a logical date value. But when the same
> field is within an object hierarchy, Drill outputs the internal object used
> to hold the date value.
> The expected output is the same display for whether the date field is shown
> as a top level column or when it is within an object hierarchy:
> {code:java}
> > select t.context.`date`, t.context from test t;
> +--------+---------+
> | EXPR$0 | context |
> +--------+---------+
> | 2018-03-13 | {"date":"2018-03-13","user":"jack"} |
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
