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 <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