You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Jim Bates <jb...@maprtech.com> on 2014/11/25 02:10:08 UTC
Issue with to_timestamp using mapr-drill-0.6.0.28642.r2-1.noarch
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.
Re: Issue with to_timestamp using mapr-drill-0.6.0.28642.r2-1.noarch
Posted by Jim Bates <jb...@maprtech.com>.
Steven, If I don't cast it then it never comes out as a timestamp after
using the "to_timestamp" function and my bi tools don't like it.
*No cast:*
[image: Inline image 1]
*With cast:*
[image: Inline image 2]
On Mon, Nov 24, 2014 at 7:26 PM, Steven Phillips <sp...@maprtech.com>
wrote:
> 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 <jb...@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
>
Re: Issue with to_timestamp using mapr-drill-0.6.0.28642.r2-1.noarch
Posted by Steven Phillips <sp...@maprtech.com>.
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 <jb...@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
Re: Issue with to_timestamp using mapr-drill-0.6.0.28642.r2-1.noarch
Posted by Jim Bates <jb...@maprtech.com>.
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 <ba...@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.
>>
>>
>
Re: Issue with to_timestamp using mapr-drill-0.6.0.28642.r2-1.noarch
Posted by Mehant Baid <ba...@gmail.com>.
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.
>