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 <[email protected]> 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 < > [email protected]> 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 <[email protected]> wrote: >>> >>> Thanks Jacques ! >>> >>> -James >>> >>> >>>> On Jul 31, 2015, at 09:48, Jason Altekruse <[email protected]> >> 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 <[email protected]> >> 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 <[email protected]> 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 >>>>> >>> >> >>
