[GitHub] [drill] paul-rogers commented on pull request #2299: DRILL-7989: Use the UTC formatter in the JSON reader

[GitBox <gi...@apache.org>]

paul-rogers commented on pull request #2299:
URL: https://github.com/apache/drill/pull/2299#issuecomment-913059912


   Hi @luocooong, thanks for your work on this one.
   
   You've uncovered another "tender point" in Drill. A quick check of the [documentation](http://drill.apache.org/docs/data-type-conversion/#cast) suggests that Drill does not specify how a `CAST` from a string to `TIMESTAMP` should be treated.  If I read your first example correctly, then it appears that we assume that the string is in local time. You said you set the current time to `2021-09-04 10:10:00`. Presumably this is the local time? You then ran the query and got an age of ~30 seconds, which suggests that the `CAST` string was treated as local time.
   
   Then you say "I think the CAST function read the value as UTC directly." In fact, I think `CAST` takes the string as local time, converts it as such, and stores that. Because both the string and value vector are in local time, no time zone conversion should be needed.
   
   An interesting (if confusing, and unrelated) question occurs if you use a string such as `2021-09-04 10:10:00Z`. Such a string is in UTC and should be converted to local time. But, since the docs don't say what we do, it is hard to know...
   
   In the second case, the JSON string contains `'2019-09-30T20:47:43.123Z'`. This *is* a UTC timestamp. You didn't mention the local time for the second case. Let's assume it is the same as the first, "`2021-09-04 10:10:00`, the timezone is GMT+8".
   
   Now comes the tricky part. If local time is GMT+8, then when we convert the JSON date to UTC, we have to add 8 hours, so we get `2019-09-31T04:47:43.123` local time. If we use `AGE()`, we should get a value of around 18 hours. But, you only got a few seconds.
   
   Do we still have some time math issues? Was the current time for your second test different? (Or, did I just get confused in doing the time conversion?)


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