Thanks, everybody. I think I got it working now. Confusion arose from being able to treat the string as though it were already a timestamp without having transformed it.
On Tue, Aug 2, 2016 at 5:25 PM, Andries Engelbrecht < [email protected]> wrote: > To simplify Vince's query > > 0: jdbc:drill:> select date_part('hour', to_timestamp('28/04/16 2:00', > 'dd/MM/yy HH:mm')) from (values(1)); > +---------+ > | EXPR$0 | > +---------+ > | 2 | > +---------+ > > But basically to_timestamp allows you to specify the format. > > --Andries > > > > On Aug 2, 2016, at 5:14 PM, Vince Gonzalez <[email protected]> wrote: > > > > How about this? > > > > 0: jdbc:drill:> select date_part('hour', t.ts) from (select > > to_timestamp('28/04/16 2:00', 'dd/MM/yy HH:mm') ts from sys.version) t; > > +---------+ > > | EXPR$0 | > > +---------+ > > | 2 | > > +---------+ > > 1 row selected (0.442 seconds) > > > > > > ---- > > Vince Gonzalez > > Systems Engineer > > 212.694.3879 > > > > mapr.com > > > > On Tue, Aug 2, 2016 at 5:11 PM, Joseph Blue <[email protected]> wrote: > > > >> *Query:* > >> select > >> Datatime_start, > >> date_part('day',Datatime_Start) `day`, > >> date_part('month',Datatime_Start) `month`, > >> date_part('year',Datatime_Start) `year`, > >> date_part('hour',Datatime_Start) `hour`, > >> date_part('minute',Datatime_Start) `minute` > >> from dfs.tmp.tv > >> limit 1 > >> > >> The question is = how do I get hour = 2? > >> > >> *Output:* > >> Datatime_start day month year hour minute second > >> 28/04/16 2:00 16 4 2028 0 0 0 > >> > >> On Tue, Aug 2, 2016 at 2:08 PM, Andries Engelbrecht < > >> [email protected]> wrote: > >> > >>> Attachments do not show on the mail list, perhaps just type out an > >> example. > >>> > >>> --Andries > >>> > >>>> On Aug 2, 2016, at 1:56 PM, Joseph Blue <[email protected]> wrote: > >>>> > >>>> My bad on the formatting. Here is a screen shot of the query. Note bad > >>> m/d/y and hour=min=sec=0.0 > >>>> > >>>> > >>>> On Tue, Aug 2, 2016 at 1:46 PM, Joseph Blue <[email protected] > >> <mailto: > >>> [email protected]>> wrote: > >>>> The field I have is a timestamp. The date is obviously in wrong order > >> in > >>> the time stamp (I can break it up and reassemble to get a good date, so > >> no > >>> problem there). > >>>> I do not seem to be able to get the hour of the day using the > >>> date_parts, so that data seems obscured. > >>>> Any ideas how to get the 2 o'clock time out of this field? > >>>> > >>>> Query... > >>>> select > >>>> Datatime_start, > >>>> date_part('day',Datatime_Start) `day`, > >>>> date_part('month',Datatime_Start) `month`, > >>>> date_part('year',Datatime_Start) `year`, > >>>> date_part('hour',Datatime_Start) `hour`, > >>>> date_part('minute',Datatime_Start) `minute` > >>>> from dfs.tmp.tv <http://dfs.tmp.tv/> > >>>> limit 5 > >>>> > >>>> Results.... > >>>> > >>>> Datatime_start > >>>> day > >>>> month > >>>> year > >>>> hour > >>>> minute > >>>> Datatime_start > >>>> day > >>>> month > >>>> year > >>>> hour > >>>> minute > >>>> 28/04/16 2:00 16 4 2028 0 0 28/04/16 2:00 16 4 2028 0 0 28/04/16 2:00 > >> 16 > >>> 4 2028 0 0 28/04/16 2:00 16 4 2028 0 0 > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> -- > >>>> Joseph Blue > >>>> Data Scientist > >>>> [email protected] <mailto:[email protected]> | www.mapr.com < > >>> http://www.mapr.com/> > >>>> Mobile: 858-357-4926 <tel:858-357-4926> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> -- > >>>> Joseph Blue > >>>> Data Scientist > >>>> [email protected] <mailto:[email protected]> | www.mapr.com < > >>> http://www.mapr.com/> > >>>> Mobile: 858-357-4926 > >>>> > >>> > >>> > >> > >> > >> -- > >> Joseph Blue > >> Data Scientist > >> [email protected] | www.mapr.com > >> *Mobile: 858-357-4926* > >> > > -- Joseph Blue Data Scientist [email protected] | www.mapr.com *Mobile: 858-357-4926*
