From: | "John Velman" <velman(at)cox(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Comparing dates |
Date: | 2003-09-02 23:09:00 |
Message-ID: | pan.2003.09.02.23.09.00.534199@cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
My date setting is ISO with US conventions, and output from a select
is in the form yyyy-mm-dd (2002-01-18, for example.
When I do a select such as
SELECT * FROM table WHERE date BETWEEN '2001-12-28' AND '2002-01-28'
It misses the entry with date '2002-01-28' (which does exist!).
Likewise,
SELECT * FROM table WHERE date = '2001-12-28' ;
gives me '0 rows'.
The only way I've been able to handle this, so far, is
in this fashion:
SELECT * FROM table WHERE
date BETWEEN 20011228 - .001 AND 20020128 + .001 ;
and similarly instead of = using, I can use
date BETWEEN 20020128 - .001 AND 20020128 + .001
I must be missing something, but I can't find it.
What is the 'right' way to select for a date type = a particular
date, and for BETWEEN to work as advertised?
I have a copy of 'Practical Postgresql', but I can't find the
answer there, or in the online manual. Of course there are a
lot of places to look and I may have missed it.
Thanks,
John Velman
From | Date | Subject | |
---|---|---|---|
Next Message | Aaron | 2003-09-02 23:53:15 | Re: web hosting postgres |
Previous Message | Adam Kavan | 2003-09-02 22:30:47 | Re: pg_autovacuum |