Re: Select gives the wrong results

> Crystle Numan wrote:
>> Dear all:
>>
>> I am fairly knowledgeable about PostgreSQL but this behaviour is
>> stumping me. Any help would be wonderful. If you think it is a bug,
>> let me now and I'll file one.
>>
>> (select values in DB (date stamps) between Jan 1, 2000 and Jan 1,
>> 2005, no results)
>>
>> db_name=# SELECT * from person_detail WHERE field='2' AND
>> value>'946702800' AND value<'1104555600';
>> id | person | field | value
>> ----+--------+-------+-------
>> (0 rows)
>>
>> (select values in DB (date stamps) between Jan 1, 2003 and Jan 1,
>> 2005, 4 results (!))
>>
>> db_name=# SELECT * from person_detail WHERE field='2' AND
>> value>'1041397200' AND value<'1104555600';
>> id | person | field | value
>> ------+--------+-------+------------
>> 1187 | 454 | 2 | 1051156800
>> 1188 | 460 | 2 | 1053316800
>> 1219 | 472 | 2 | 1057723200
>> 1181 | 441 | 2 | 1042520400
>> (4 rows)
>>
>> The first select should have those 4 results plus any more. We tried
>> putting quotes (") around the word 'value' to see if that made a
>> difference, and no it didn't. We tried reversing the two clauses and
>> that made no difference.
>>
>> Here's another funny one. Not the one that doesn't belong.
>>
>> db_name=# SELECT * from person_detail WHERE field='2' AND
>> value>='1000000001' AND value<='1104555600';
>> id | person | field | value
>> ------+--------+-------+------------
>> 3 | 218 | 2 | 1017464400
>> 253 | 295 | 2 | 1002340800
>> 514 | 323 | 2 | 100155600
>> 1126 | 405 | 2 | 1006750800
>> 1179 | 439 | 2 | 1035172800
>> 1187 | 454 | 2 | 1051156800
>> 1188 | 460 | 2 | 1053316800
>> 1219 | 472 | 2 | 1057723200
>> 1181 | 441 | 2 | 1042520400
>> 1152 | 434 | 2 | 1032321600
>> 1129 | 410 | 2 | 1024027200
>> (11 rows)
>>
>> Anyone see what's going on here?
>>
>> Thanks!
>> Crystle
>>
>>
>>
> http://archives.postgresql.org

Assuming that value is epoch date,

$ psql test -c "select date(1051156800)"
date
------------
2003-04-24
(1 row)

$ psql test -c "select date(1053316800)"
date
------------
2003-05-19
(1 row)

$ psql test -c "select date(0)"
date
------------
1969-12-31
(1 row)

$ psql test -c "select date(86400)"
date
------------
1970-01-01
(1 row)

Then wouldn't something along the lines of:
SELECT * from person_detail WHERE field='2' AND value between
date(1000000001) and date(1104555600); work....

reid