coaxing hour of day from a timestamp

[Joseph Blue]

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
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 | www.mapr.com
*Mobile: 858-357-4926*

Re: coaxing hour of day from a timestamp

[Joseph Blue]

My bad on the formatting. Here is a screen shot of the query. Note bad
m/d/y and hour=min=sec=0.0
[image: Inline image 1]

On Tue, Aug 2, 2016 at 1:46 PM, Joseph Blue  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
> 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 | www.mapr.com
> *Mobile: 858-357-4926 <858-357-4926>*
>
>
>
>


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

Re: coaxing hour of day from a timestamp

[Andries Engelbrecht]

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  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  > 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 
> 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  | www.mapr.com 
> 
> Mobile: 858-357-4926 
> 
> 
> 
> 
> 
> 
> -- 
> Joseph Blue
> Data Scientist
> jb...@maprtech.com  | www.mapr.com 
> 
> Mobile: 858-357-4926
> 

Re: coaxing hour of day from a timestamp

[Joseph Blue]

*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 00  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  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>> 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 
> > 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  | www.mapr.com <
> http://www.mapr.com/>
> > Mobile: 858-357-4926 
> >
> >
> >
> >
> >
> >
> > --
> > Joseph Blue
> > Data Scientist
> > 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*

Re: coaxing hour of day from a timestamp

[Vince Gonzalez]

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  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 00  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  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>> 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 
> > > 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  | www.mapr.com <
> > http://www.mapr.com/>
> > > Mobile: 858-357-4926 
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Joseph Blue
> > > Data Scientist
> > > 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*
>

Re: coaxing hour of day from a timestamp

[Andries Engelbrecht]

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  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  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 00  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  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>> 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 
 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  | www.mapr.com <
>>> http://www.mapr.com/>
 Mobile: 858-357-4926 
 
 
 
 
 
 
 --
 Joseph Blue
 Data Scientist
 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*
>> 

Re: coaxing hour of day from a timestamp

[Joseph Blue]

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


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