Thread: question using 'between' in a sql query

My table

create table mytable (
employeecode text,
info text,
somestuff text,
insertdate timestamp with time zone);

I have an interface where the user can query for all
employeecode between two dates. I assumed from the
docs that:

SELECT employeecode FROM mytable WHERE insertdate
BETWEEN '11/20/2005' AND '11/20/2005'

would yield an inclusive result set. I am being lazy
and I didn't want to check if the user was looking for
just one day.... (it looks like I will have to not be
lazy).

Is this correct behavior? if 'between' is inclusive of
the start and end dates why doesn't this query result
in all the rows on '11/20/2005'?

BTW this is 8.1 on OS X 10.4.3

Ted

    
__________________________________________ 
Yahoo! DSL – Something to write home about. 
Just $16.99/mo. or less. 
dsl.yahoo.com 

On Sat, Dec 03, 2005 at 09:54:16AM -0800, Theodore Petrosky wrote:
> if 'between' is inclusive of the start and end dates why doesn't
> this query result in all the rows on '11/20/2005'?

EXPLAIN shows what happens when you compare a date against a timestamp:

Filter: ((insertdate >= '2005-11-20 00:00:00-08'::timestamp with time zone)    AND (insertdate <= '2005-11-20
00:00:00-08'::timestampwith time zone))
 

One way around this is to cast the timestamp column to date:

SELECT employeecode
FROM mytable
WHERE insertdate::date BETWEEN '11/20/2005' AND '11/20/2005';

-- 
Michael Fuhr

thanks... as is so often the case I discovered 

date_trunc('day', insertdate) between '11/20/2005' AND
'11/20/2005' also works....

It wasn't until I pressed the send button that I
thought of the solution. I have thought of creating a
faux 'send' button for my email software... yours is a
little easier to read (human)..

Thanks again,

Ted

--- Michael Fuhr <mike@fuhr.org> wrote:

> On Sat, Dec 03, 2005 at 09:54:16AM -0800, Theodore
> Petrosky wrote:
> > if 'between' is inclusive of the start and end
> dates why doesn't
> > this query result in all the rows on '11/20/2005'?
> 
> EXPLAIN shows what happens when you compare a date
> against a timestamp:
> 
> Filter: ((insertdate >= '2005-11-20
> 00:00:00-08'::timestamp with time zone)
>      AND (insertdate <= '2005-11-20
> 00:00:00-08'::timestamp with time zone))
> 
> One way around this is to cast the timestamp column
> to date:
> 
> SELECT employeecode
> FROM mytable
> WHERE insertdate::date BETWEEN '11/20/2005' AND
> '11/20/2005';
> 
> -- 
> Michael Fuhr
> 


    
__________________________________ 
Start your day with Yahoo! - Make it your home page! 
http://www.yahoo.com/r/hs