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 <[email protected]> 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 <[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*
>>
