[jira] [Commented] (DRILL-7989) Use the UTC formatter in the JSON reader

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-7989?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17409984#comment-17409984 ] 

ASF GitHub Bot commented on DRILL-7989:
---------------------------------------

luocooong commented on pull request #2299:
URL: https://github.com/apache/drill/pull/2299#issuecomment-912984109


   @paul-rogers Thanks for the support, there is a new check result.
   
   Use the AGE() function in the web console :
   1. Setting the current date time to `2021-09-04 10:10:00`, the timezone is GMT+8.
   2. Run and check the result :
   ```sql
   SELECT AGE(CURRENT_TIMESTAMP, CAST('2021-09-04 10:10:00' AS TIMESTAMP)) FROM (VALUES(1))
   ```
   ```sql
   EXPR$0
   PT31.859S
   ```
   3. As the above, I think the CAST function read the value as UTC directly.
   
   Add the AGE() in the `testDateJsonInput()` :
   1. Update the code (the json value is '2019-09-30T20:47:43.123Z') :
   ```java
   @Test
   public void testDateJsonInput() throws Exception {
     test("select `date`, AGE(`date`, CAST('2019-09-30 20:47:43' as timestamp)) from cp.`jsoninput/input2.json` limit 1 ");
   }
   ```
   2. Run the test and check the result :
   ```
   date<TIMESTAMP(OPTIONAL)>  EXPR$1<INTERVAL(OPTIONAL)>
   2019-09-30T20:47:43.123  PT0.123S
   ```
   
   Actually, In the `writeTimestamp(boolean isNull)` function, the revision code will get the date time value with the UTC formatter, at the final, will convert to local time (not the UTC) and store the epoch value to vector.
   
   Did I miss anything?
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


> Use the UTC formatter in the JSON reader
> ----------------------------------------
>
>                 Key: DRILL-7989
>                 URL: https://issues.apache.org/jira/browse/DRILL-7989
>             Project: Apache Drill
>          Issue Type: Improvement
>            Reporter: Cong Luo
>            Assignee: Cong Luo
>            Priority: Major
>             Fix For: 1.20.0
>
>
> MongoDB use the UTC format to specify the date value by default. But the JSON reader (old version) use the fixed date formatter : "yyyy-MM-dd'T'HH:mm:ss.SSSXX". Need to change to the "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'" format.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)