drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jim Bates <jba...@maprtech.com>
Subject Re: Issue with to_timestamp using mapr-drill-0.6.0.28642.r2-1.noarch
Date Tue, 25 Nov 2014 03:35:55 GMT
That did it. Thanks!
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-11-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"} |
+------------+---------------+------------+

On Mon, Nov 24, 2014 at 7:53 PM, Mehant Baid <baid.mehant@gmail.com> wrote:

> 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