By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

Comparing dates

P: n/a
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
Nov 11 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Tue, Sep 02, 2003 at 04:09:00PM -0700, John Velman wrote:
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!).


So, your date column is actually a timestamp? Cast it to date first and
see if it works, e.g.

SELECT * FROM table WHERE date::date BETWEEN '2001-12-28' AND '2002-01-28'

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La principal característica humana es la tontería"
(Augusto Monterroso)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #2

P: n/a
"John Velman" <ve****@cox.net> writes:
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!).
Uh, what exactly is the datatype of the "date" column?
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 ;


I think the odds that this does what you think it does are vanishingly
small :-(. There's no date-vs-numeric comparison operator. It's
probably getting interpreted as some bizarre textual comparison.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.