drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joseph Blue <jb...@maprtech.com>
Subject Re: coaxing hour of day from a timestamp
Date Wed, 03 Aug 2016 02:17:29 GMT
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 <
aengelbrecht@maprtech.com> 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 <vgonzalez@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 <jblue@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 <
> >> aengelbrecht@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 <jblue@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 <jblue@maprtech.com
> >> <mailto:
> >>> jblue@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
> >>>> jblue@maprtech.com <mailto:jblue@maprtech.com> | www.mapr.com
<
> >>> http://www.mapr.com/>
> >>>> Mobile: 858-357-4926 <tel:858-357-4926>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> Joseph Blue
> >>>> Data Scientist
> >>>> jblue@maprtech.com <mailto:jblue@maprtech.com> | www.mapr.com
<
> >>> http://www.mapr.com/>
> >>>> Mobile: 858-357-4926
> >>>>
> >>>
> >>>
> >>
> >>
> >> --
> >> Joseph Blue
> >> Data Scientist
> >> jblue@maprtech.com | www.mapr.com
> >> *Mobile: 858-357-4926*
> >>
>
>


-- 
Joseph Blue
Data Scientist
jblue@maprtech.com | www.mapr.com
*Mobile: 858-357-4926*

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