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*
>
