I think you need to use to_timestamp() instead.. Your conversion includes a timezone (-05:00)..
from_unixtime() looks like is producing a non-utc timestamp. -----Original Message----- From: Divya Gehlot [mailto:divya.htco...@gmail.com] Sent: Tuesday, July 03, 2018 2:06 AM To: user@drill.apache.org Subject: Re: unable to read timestamp when create CSV file wheras parquet can read [EXTERNAL EMAIL] Has anybody this issue between CSV and parquet files ? Really appreciate the help ! On Mon, 2 Jul 2018 at 17:51, Divya Gehlot <divya.htco...@gmail.com> wrote: > Hi, > I am getting error when I creating the CSV files The part of the query > throwing error is as below, the input data comes in unix time format > CAST(FROM_UNIXTIME(t.`timestamp`/1000) AS TIMESTAMP) AS `datetime` > Apache Drill > org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: > IllegalArgumentException: Invalid format: "2018-06-24T12:00:00.000-05:00" > is malformed at "T12:00:00.000-05:00" Fragment 1:0 [Error Id: > e43e9948-bc80-4485-b436-62bc57b1327f on > usazprdmapr-dn2.am.jllnet.com:31010] > > whereas with the same query when I create parquet files , I am able to > read and no error being thrown : > > CAST(FROM_UNIXTIME(t.`timestamp`/1000) AS TIMESTAMP) AS `datetime` > > I can view the results too : > > Appreciate the help ! > > Thanks, > Divya > > This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers for further information. Please refer to http://www.blackrock.com/corporate/en-us/compliance/privacy-policy for more information about BlackRock’s Privacy Policy. For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations. © 2018 BlackRock, Inc. All rights reserved.
