drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Steven Phillips <sphill...@maprtech.com>
Subject Re: Issue with to_timestamp using mapr-drill-0.6.0.28642.r2-1.noarch
Date Tue, 25 Nov 2014 01:26:53 GMT
It looks like a bug to me. Question: Why do you need to use both cast and
to_timestamp function? Doesn't to_timestamp work by itself?

On Mon, Nov 24, 2014 at 5:10 PM, Jim Bates <jbates@maprtech.com> 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.
>



-- 
 Steven Phillips
 Software Engineer

 mapr.com

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