pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquestion using 'between' in a sql query
3+ messages / 2 participants
[nested] [flat]
* question using 'between' in a sql query
@ 2005-12-03 17:54 Theodore Petrosky <tedpet5@yahoo.com>
2005-12-03 18:15 ` Re: question using 'between' in a sql query Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Theodore Petrosky @ 2005-12-03 17:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: question using 'between' in a sql query
2005-12-03 17:54 question using 'between' in a sql query Theodore Petrosky <tedpet5@yahoo.com>
@ 2005-12-03 18:15 ` Michael Fuhr <mike@fuhr.org>
2005-12-04 16:28 ` Re: question using 'between' in a sql query Theodore Petrosky <tedpet5@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2005-12-03 18:15 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: question using 'between' in a sql query
2005-12-03 17:54 question using 'between' in a sql query Theodore Petrosky <tedpet5@yahoo.com>
2005-12-03 18:15 ` Re: question using 'between' in a sql query Michael Fuhr <mike@fuhr.org>
@ 2005-12-04 16:28 ` Theodore Petrosky <tedpet5@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Theodore Petrosky @ 2005-12-04 16:28 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-04 16:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-03 17:54 question using 'between' in a sql query Theodore Petrosky <tedpet5@yahoo.com>
2005-12-03 18:15 ` Michael Fuhr <mike@fuhr.org>
2005-12-04 16:28 ` Theodore Petrosky <tedpet5@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox