drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andries Engelbrecht <aengelbre...@maprtech.com>
Subject Re: coaxing hour of day from a timestamp
Date Wed, 03 Aug 2016 00:25:23 GMT
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*
>> 


Mime
View raw message