Thread: To many records returned

Have a select statement with a where clause using datestamp with
timezone column compared to a date

Select * from sometable where DateStampColumn > '2008-03-31'

this returns records that are equal 2008-03-31

but when the query includes casting to date
Select * from sometable where DateStampColumn::date > '2008-03-31'
The result is correct

What is causing the difference in the results

My guess is '2008-03-31' is being cast into a datestamp value of
2008-03-31 00:00:00.00-00  which would explain why one query returns
more records.

Justin escribió:
> Have a select statement with a where clause using datestamp with
> timezone column compared to a date
> Select * from sometable where DateStampColumn > '2008-03-31'
> this returns records that are equal 2008-03-31
>
> but when the query includes casting to date
> Select * from sometable where DateStampColumn::date > '2008-03-31' The
> result is correct
>
> What is causing the difference in the results
>
> My guess is '2008-03-31' is being cast into a datestamp value of
> 2008-03-31 00:00:00.00-00  which would explain why one query returns
> more records.
>
I am almost sure your problem is that casting is done to text....so

2008-03-31 00:00:01 is greater than 2008-03-31 so that date is included....

Explicit casting is the right thing to do (your second query)

Justin <justin@emproshunts.com> writes:
> Have a select statement with a where clause using datestamp with
> timezone column compared to a date

> Select * from sometable where DateStampColumn > '2008-03-31'

> this returns records that are equal 2008-03-31

There's no such thing as "timestamp compared to date".  The date
promotes to a timestamp at midnight; what you wrote is equivalent
to

Select * from sometable where DateStampColumn > '2008-03-31 00:00:00'

> but when the query includes casting to date
> Select * from sometable where DateStampColumn::date > '2008-03-31'
> The result is correct

You threw away the intraday part of the timestamp...

            regards, tom lane

On Apr 3, 9:02 pm, jus...@emproshunts.com (Justin) wrote:
> Have a select statement with a where clause using datestamp with
> timezone column compared to a date
>
> Select * from sometable where DateStampColumn > '2008-03-31'
>
> this returns records that are equal 2008-03-31
>
> but when the query includes casting to date
> Select * from sometable where DateStampColumn::date > '2008-03-31'
> The result is correct
>
> What is causing the difference in the results
>
> My guess is '2008-03-31' is being cast into a datestamp value of
> 2008-03-31 00:00:00.00-00  which would explain why one query returns
> more records.
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

I believe you are correct in your explanation of the problem.  The
solution is to use your second query or one such as this :

Select * from sometable where DateStampColumn >= '2008-04-01'