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 <vgonza...@mapr.com> 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 <jb...@maprtech.com> 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 < >> aengelbre...@maprtech.com> 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 <jb...@maprtech.com> 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 <jb...@maprtech.com >> <mailto: >>> jb...@maprtech.com>> 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 >>>> jb...@maprtech.com <mailto:jb...@maprtech.com> | www.mapr.com < >>> http://www.mapr.com/> >>>> Mobile: 858-357-4926 <tel:858-357-4926> >>>> >>>> >>>> >>>> >>>> >>>> >>>> -- >>>> Joseph Blue >>>> Data Scientist >>>> jb...@maprtech.com <mailto:jb...@maprtech.com> | www.mapr.com < >>> http://www.mapr.com/> >>>> Mobile: 858-357-4926 >>>> >>> >>> >> >> >> -- >> Joseph Blue >> Data Scientist >> jb...@maprtech.com | www.mapr.com >> *Mobile: 858-357-4926* >>