You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Jacques Nadeau <ja...@dremio.com> on 2015/07/31 18:47:08 UTC
Re: querying a specific time period
I would think you could cast to time and then provide a time boundary.
I don't remember the exact syntax but something like WHERE CAST(`time` as
TIME) > TIME '18:00:00' and CAST(`time` as TIME) < TIME '23:00:00'
--
Jacques Nadeau
CTO and Co-Founder, Dremio
On Fri, Jul 31, 2015 at 9:29 AM, James Sun <js...@maprtech.com> wrote:
>
> Hi,
>
> I have a week worth of data in a view and there is already a date column:
> select `time` from dfs.views.`mytbl` limit 5;
> +------------------------+
> | time |
> +------------------------+
> | 2011-04-24 22:21:19.0 |
> | 2011-04-24 22:21:24.0 |
> | 2011-04-24 22:21:28.0 |
> | 2011-04-24 22:21:33.0 |
> | 2011-04-24 22:21:38.0 |
> +------------------------+
> 5 rows selected (0.256 seconds)
>
> Now if I want to query from time 18:00:00 to 23:00:00 on every day, what
> would be a good way to do it?
>
> Thanks
>
> -James
Re: querying a specific time period
Posted by James Sun <js...@maprtech.com>.
Cool, Thanks Andries. I have tried both methods and they worked great.
-James
> On Jul 31, 2015, at 10:46, Andries Engelbrecht <ae...@maprtech.com> wrote:
>
> Very true :-)
>
> a key point to note is that between will be inclusive of the comparison values, where < and > will exclude the comparison values.
>
> —Andries
>
>> On Jul 31, 2015, at 10:42 AM, Jacques Nadeau <ja...@dremio.com> wrote:
>>
>> Oh yeah, between... That is much nicer than mine.
>>
>> A tool isn't useful until you can do something at least three ways :)
>>
>> --
>> Jacques Nadeau
>> CTO and Co-Founder, Dremio
>>
>> On Fri, Jul 31, 2015 at 10:40 AM, Andries Engelbrecht <
>> aengelbrecht@maprtech.com> wrote:
>>
>>> James,
>>>
>>> you can also use
>>> where cast(`time` as time) between time '18:00:00' and time '23:00:00’
>>>
>>>
>>> As a side note, it is not good to have a column named time or most of the
>>> common reserved keywords in SQL.
>>>
>>>
>>> —Andries
>>>
>>>
>>>> On Jul 31, 2015, at 9:49 AM, James Sun <js...@maprtech.com> wrote:
>>>>
>>>> Thanks Jacques !
>>>>
>>>> -James
>>>>
>>>>
>>>>> On Jul 31, 2015, at 09:48, Jason Altekruse <al...@gmail.com>
>>> wrote:
>>>>>
>>>>> You also could use the date-part function.
>>>>>
>>>>>
>>> http://drill.apache.org/docs/date-time-functions-and-arithmetic/#date_part-syntax
>>>>>
>>>>> On Fri, Jul 31, 2015 at 9:47 AM, Jacques Nadeau <ja...@dremio.com>
>>> wrote:
>>>>>
>>>>>> I would think you could cast to time and then provide a time boundary.
>>>>>>
>>>>>> I don't remember the exact syntax but something like WHERE CAST(`time`
>>> as
>>>>>> TIME) > TIME '18:00:00' and CAST(`time` as TIME) < TIME '23:00:00'
>>>>>>
>>>>>> --
>>>>>> Jacques Nadeau
>>>>>> CTO and Co-Founder, Dremio
>>>>>>
>>>>>> On Fri, Jul 31, 2015 at 9:29 AM, James Sun <js...@maprtech.com> wrote:
>>>>>>
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I have a week worth of data in a view and there is already a date
>>>>>> column:
>>>>>>> select `time` from dfs.views.`mytbl` limit 5;
>>>>>>> +------------------------+
>>>>>>> | time |
>>>>>>> +------------------------+
>>>>>>> | 2011-04-24 22:21:19.0 |
>>>>>>> | 2011-04-24 22:21:24.0 |
>>>>>>> | 2011-04-24 22:21:28.0 |
>>>>>>> | 2011-04-24 22:21:33.0 |
>>>>>>> | 2011-04-24 22:21:38.0 |
>>>>>>> +------------------------+
>>>>>>> 5 rows selected (0.256 seconds)
>>>>>>>
>>>>>>> Now if I want to query from time 18:00:00 to 23:00:00 on every day,
>>> what
>>>>>>> would be a good way to do it?
>>>>>>>
>>>>>>> Thanks
>>>>>>>
>>>>>>> -James
>>>>>>
>>>>
>>>
>>>
>
Re: querying a specific time period
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Very true :-)
a key point to note is that between will be inclusive of the comparison values, where < and > will exclude the comparison values.
—Andries
> On Jul 31, 2015, at 10:42 AM, Jacques Nadeau <ja...@dremio.com> wrote:
>
> Oh yeah, between... That is much nicer than mine.
>
> A tool isn't useful until you can do something at least three ways :)
>
> --
> Jacques Nadeau
> CTO and Co-Founder, Dremio
>
> On Fri, Jul 31, 2015 at 10:40 AM, Andries Engelbrecht <
> aengelbrecht@maprtech.com> wrote:
>
>> James,
>>
>> you can also use
>> where cast(`time` as time) between time '18:00:00' and time '23:00:00’
>>
>>
>> As a side note, it is not good to have a column named time or most of the
>> common reserved keywords in SQL.
>>
>>
>> —Andries
>>
>>
>>> On Jul 31, 2015, at 9:49 AM, James Sun <js...@maprtech.com> wrote:
>>>
>>> Thanks Jacques !
>>>
>>> -James
>>>
>>>
>>>> On Jul 31, 2015, at 09:48, Jason Altekruse <al...@gmail.com>
>> wrote:
>>>>
>>>> You also could use the date-part function.
>>>>
>>>>
>> http://drill.apache.org/docs/date-time-functions-and-arithmetic/#date_part-syntax
>>>>
>>>> On Fri, Jul 31, 2015 at 9:47 AM, Jacques Nadeau <ja...@dremio.com>
>> wrote:
>>>>
>>>>> I would think you could cast to time and then provide a time boundary.
>>>>>
>>>>> I don't remember the exact syntax but something like WHERE CAST(`time`
>> as
>>>>> TIME) > TIME '18:00:00' and CAST(`time` as TIME) < TIME '23:00:00'
>>>>>
>>>>> --
>>>>> Jacques Nadeau
>>>>> CTO and Co-Founder, Dremio
>>>>>
>>>>> On Fri, Jul 31, 2015 at 9:29 AM, James Sun <js...@maprtech.com> wrote:
>>>>>
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I have a week worth of data in a view and there is already a date
>>>>> column:
>>>>>> select `time` from dfs.views.`mytbl` limit 5;
>>>>>> +------------------------+
>>>>>> | time |
>>>>>> +------------------------+
>>>>>> | 2011-04-24 22:21:19.0 |
>>>>>> | 2011-04-24 22:21:24.0 |
>>>>>> | 2011-04-24 22:21:28.0 |
>>>>>> | 2011-04-24 22:21:33.0 |
>>>>>> | 2011-04-24 22:21:38.0 |
>>>>>> +------------------------+
>>>>>> 5 rows selected (0.256 seconds)
>>>>>>
>>>>>> Now if I want to query from time 18:00:00 to 23:00:00 on every day,
>> what
>>>>>> would be a good way to do it?
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> -James
>>>>>
>>>
>>
>>
Re: querying a specific time period
Posted by Jacques Nadeau <ja...@dremio.com>.
Oh yeah, between... That is much nicer than mine.
A tool isn't useful until you can do something at least three ways :)
--
Jacques Nadeau
CTO and Co-Founder, Dremio
On Fri, Jul 31, 2015 at 10:40 AM, Andries Engelbrecht <
aengelbrecht@maprtech.com> wrote:
> James,
>
> you can also use
> where cast(`time` as time) between time '18:00:00' and time '23:00:00’
>
>
> As a side note, it is not good to have a column named time or most of the
> common reserved keywords in SQL.
>
>
> —Andries
>
>
> > On Jul 31, 2015, at 9:49 AM, James Sun <js...@maprtech.com> wrote:
> >
> > Thanks Jacques !
> >
> > -James
> >
> >
> >> On Jul 31, 2015, at 09:48, Jason Altekruse <al...@gmail.com>
> wrote:
> >>
> >> You also could use the date-part function.
> >>
> >>
> http://drill.apache.org/docs/date-time-functions-and-arithmetic/#date_part-syntax
> >>
> >> On Fri, Jul 31, 2015 at 9:47 AM, Jacques Nadeau <ja...@dremio.com>
> wrote:
> >>
> >>> I would think you could cast to time and then provide a time boundary.
> >>>
> >>> I don't remember the exact syntax but something like WHERE CAST(`time`
> as
> >>> TIME) > TIME '18:00:00' and CAST(`time` as TIME) < TIME '23:00:00'
> >>>
> >>> --
> >>> Jacques Nadeau
> >>> CTO and Co-Founder, Dremio
> >>>
> >>> On Fri, Jul 31, 2015 at 9:29 AM, James Sun <js...@maprtech.com> wrote:
> >>>
> >>>>
> >>>> Hi,
> >>>>
> >>>> I have a week worth of data in a view and there is already a date
> >>> column:
> >>>> select `time` from dfs.views.`mytbl` limit 5;
> >>>> +------------------------+
> >>>> | time |
> >>>> +------------------------+
> >>>> | 2011-04-24 22:21:19.0 |
> >>>> | 2011-04-24 22:21:24.0 |
> >>>> | 2011-04-24 22:21:28.0 |
> >>>> | 2011-04-24 22:21:33.0 |
> >>>> | 2011-04-24 22:21:38.0 |
> >>>> +------------------------+
> >>>> 5 rows selected (0.256 seconds)
> >>>>
> >>>> Now if I want to query from time 18:00:00 to 23:00:00 on every day,
> what
> >>>> would be a good way to do it?
> >>>>
> >>>> Thanks
> >>>>
> >>>> -James
> >>>
> >
>
>
Re: querying a specific time period
Posted by Andries Engelbrecht <ae...@maprtech.com>.
James,
you can also use
where cast(`time` as time) between time '18:00:00' and time '23:00:00’
As a side note, it is not good to have a column named time or most of the common reserved keywords in SQL.
—Andries
> On Jul 31, 2015, at 9:49 AM, James Sun <js...@maprtech.com> wrote:
>
> Thanks Jacques !
>
> -James
>
>
>> On Jul 31, 2015, at 09:48, Jason Altekruse <al...@gmail.com> wrote:
>>
>> You also could use the date-part function.
>>
>> http://drill.apache.org/docs/date-time-functions-and-arithmetic/#date_part-syntax
>>
>> On Fri, Jul 31, 2015 at 9:47 AM, Jacques Nadeau <ja...@dremio.com> wrote:
>>
>>> I would think you could cast to time and then provide a time boundary.
>>>
>>> I don't remember the exact syntax but something like WHERE CAST(`time` as
>>> TIME) > TIME '18:00:00' and CAST(`time` as TIME) < TIME '23:00:00'
>>>
>>> --
>>> Jacques Nadeau
>>> CTO and Co-Founder, Dremio
>>>
>>> On Fri, Jul 31, 2015 at 9:29 AM, James Sun <js...@maprtech.com> wrote:
>>>
>>>>
>>>> Hi,
>>>>
>>>> I have a week worth of data in a view and there is already a date
>>> column:
>>>> select `time` from dfs.views.`mytbl` limit 5;
>>>> +------------------------+
>>>> | time |
>>>> +------------------------+
>>>> | 2011-04-24 22:21:19.0 |
>>>> | 2011-04-24 22:21:24.0 |
>>>> | 2011-04-24 22:21:28.0 |
>>>> | 2011-04-24 22:21:33.0 |
>>>> | 2011-04-24 22:21:38.0 |
>>>> +------------------------+
>>>> 5 rows selected (0.256 seconds)
>>>>
>>>> Now if I want to query from time 18:00:00 to 23:00:00 on every day, what
>>>> would be a good way to do it?
>>>>
>>>> Thanks
>>>>
>>>> -James
>>>
>
Re: querying a specific time period
Posted by James Sun <js...@maprtech.com>.
Thanks Jacques !
-James
> On Jul 31, 2015, at 09:48, Jason Altekruse <al...@gmail.com> wrote:
>
> You also could use the date-part function.
>
> http://drill.apache.org/docs/date-time-functions-and-arithmetic/#date_part-syntax
>
> On Fri, Jul 31, 2015 at 9:47 AM, Jacques Nadeau <ja...@dremio.com> wrote:
>
>> I would think you could cast to time and then provide a time boundary.
>>
>> I don't remember the exact syntax but something like WHERE CAST(`time` as
>> TIME) > TIME '18:00:00' and CAST(`time` as TIME) < TIME '23:00:00'
>>
>> --
>> Jacques Nadeau
>> CTO and Co-Founder, Dremio
>>
>> On Fri, Jul 31, 2015 at 9:29 AM, James Sun <js...@maprtech.com> wrote:
>>
>>>
>>> Hi,
>>>
>>> I have a week worth of data in a view and there is already a date
>> column:
>>> select `time` from dfs.views.`mytbl` limit 5;
>>> +------------------------+
>>> | time |
>>> +------------------------+
>>> | 2011-04-24 22:21:19.0 |
>>> | 2011-04-24 22:21:24.0 |
>>> | 2011-04-24 22:21:28.0 |
>>> | 2011-04-24 22:21:33.0 |
>>> | 2011-04-24 22:21:38.0 |
>>> +------------------------+
>>> 5 rows selected (0.256 seconds)
>>>
>>> Now if I want to query from time 18:00:00 to 23:00:00 on every day, what
>>> would be a good way to do it?
>>>
>>> Thanks
>>>
>>> -James
>>
Re: querying a specific time period
Posted by Jason Altekruse <al...@gmail.com>.
You also could use the date-part function.
http://drill.apache.org/docs/date-time-functions-and-arithmetic/#date_part-syntax
On Fri, Jul 31, 2015 at 9:47 AM, Jacques Nadeau <ja...@dremio.com> wrote:
> I would think you could cast to time and then provide a time boundary.
>
> I don't remember the exact syntax but something like WHERE CAST(`time` as
> TIME) > TIME '18:00:00' and CAST(`time` as TIME) < TIME '23:00:00'
>
> --
> Jacques Nadeau
> CTO and Co-Founder, Dremio
>
> On Fri, Jul 31, 2015 at 9:29 AM, James Sun <js...@maprtech.com> wrote:
>
> >
> > Hi,
> >
> > I have a week worth of data in a view and there is already a date
> column:
> > select `time` from dfs.views.`mytbl` limit 5;
> > +------------------------+
> > | time |
> > +------------------------+
> > | 2011-04-24 22:21:19.0 |
> > | 2011-04-24 22:21:24.0 |
> > | 2011-04-24 22:21:28.0 |
> > | 2011-04-24 22:21:33.0 |
> > | 2011-04-24 22:21:38.0 |
> > +------------------------+
> > 5 rows selected (0.256 seconds)
> >
> > Now if I want to query from time 18:00:00 to 23:00:00 on every day, what
> > would be a good way to do it?
> >
> > Thanks
> >
> > -James
>