incubator-drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mehant Baid <baid.meh...@gmail.com>
Subject Re: Issue with to_timestamp using mapr-drill-0.6.0.28642.r2-1.noarch
Date Tue, 25 Nov 2014 01:53:46 GMT
Adding to what Steven mentioned, you only need to perform to_timestamp.

We use JODA library to parse the timestamp string and our format 
specifiers in SQL have to match those of JODA. 
http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 
contains the list of all format specifiers.

If you change the to_timestamp format specifier to the following: 
to_timestamp(executiontime, '*YYYY-MM-dd hh:mm:ss a*') it should work. 
In your query you had a 'D' as the format specifier which as per JODA 
means day of the year instead of day of the month. Changing it to 'd' 
should give correct result.

Thanks
Mehant


On 11/24/14, 5:10 PM, Jim Bates wrote:
> I was trying to convert from a string to timestamp and the conversion
> completes as expected... It just has the wrong date and time when done
>
>
> *No conversion:*
> select * FROM (select `dir0` as `city`, `executionTime`,
> flatten(`stationBeanList`) as `stations` FROM
>   `data`.`all_bikes`.`../bikes/chicago/bikestations/1416875401.json` limit
> 1) a;
> +------------+---------------+------------+
> |    city    | executionTime |  stations  |
> +------------+---------------+------------+
> | null       | 2014-11-24 06:29:01 PM | {"id":5,"stationName":"State St &
> Harrison St","availableDocks":12,"totalDocks":19,"latitude":41.8739580629,"longitude":-87.6277394859,"statusValue":"In
> Service","statusKey":1,"availableBikes":7,"stAddress1":"State St & Harrison
> St","stAddress2":"","city":"","postalCode":"","location":"620 S. State
> St.","altitude":"","testStation":false,"landMark":"030"} |
> +------------+---------------+------------+
>
>
> *Casting directly throws an error:*
> select * FROM (select `dir0` as `city`, cast(`executionTime` as timestamp)
> as `executionTime`, flatten(`stationBeanList`) as `stations` FROM
>   `data`.`all_bikes`.`../bikes/chicago/bikestations/1416875401.json` limit
> 1) a;
> +------------+---------------+------------+
> |    city    | executionTime |  stations  |
> +------------+---------------+------------+
> Query failed: Failure while running fragment., Invalid format: "2014-11-24
> 06:29:01 PM" is malformed at " PM" [ aa09b6ca-f380-429d-b0c6-b43720d0f3a3
> on jbates4:31010 ]
>
>
> java.lang.RuntimeException: java.sql.SQLException: Failure while executing
> query.
> at sqlline.SqlLine$IncrementalRows.hasNext(SqlLine.java:2514)
> at sqlline.SqlLine$TableOutputFormat.print(SqlLine.java:2148)
> at sqlline.SqlLine.print(SqlLine.java:1809)
> at sqlline.SqlLine$Commands.execute(SqlLine.java:3766)
> at sqlline.SqlLine$Commands.sql(SqlLine.java:3663)
> at sqlline.SqlLine.dispatch(SqlLine.java:889)
> at sqlline.SqlLine.begin(SqlLine.java:763)
> at sqlline.SqlLine.start(SqlLine.java:498)
> at sqlline.SqlLine.main(SqlLine.java:460)
>
> *Using to_timestamp conversion:*
> select * FROM (select `dir0` as `city`, cast(to_timestamp(
> `executionTime`,'YYYY-MM-DD hh:mm:ss a') as timestamp) as `executionTime`,
> flatten(`stationBeanList`) as `stations` FROM
>   `data`.`all_bikes`.`../bikes/chicago/bikestations/1416875401.json` limit
> 1) a;
> +------------+---------------+------------+
> |    city    | executionTime |  stations  |
> +------------+---------------+------------+
> | null       | 2014-01-24 18:29:01.0 | {"id":5,"stationName":"State St &
> Harrison St","availableDocks":12,"totalDocks":19,"latitude":41.8739580629,"longitude":-87.6277394859,"statusValue":"In
> Service","statusKey":1,"availableBikes":7,"stAddress1":"State St & Harrison
> St","stAddress2":"","city":"","postalCode":"","location":"620 S. State
> St.","altitude":"","testStation":false,"landMark":"030"} |
> +------------+---------------+------------+
>
> Am I doing this one with teh correct syntax on the "to_timestamp" or is
> this one a bug?
>
> I didn't find anything open.
>


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message