drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jim Bates <jba...@maprtech.com>
Subject Issue with to_timestamp using mapr-drill-0.6.0.28642.r2-1.noarch
Date Tue, 25 Nov 2014 01:10:08 GMT
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