The following bug has been logged online:
Bug reference: 5031
Logged by: Mark Douglas
Email address: mark(at)steelhousemedia(dot)com
PostgreSQL version: 8.4.0
Operating system: Ubunto Linux
Description: DATE_TRUNC returns the wrong value when specifying MONTH
Details:
The following use of DATE_TRUNC returns the wrong value. I called the
function on 2009-09-02. It should return '2009-09-01 00:00:00' for the
following usage:
SELECT DATE_TRUNC('MONTH', CURRENT_DATE);
It instead returns '2009-08-31 17:00:00.
Casting CURRENT_DATE to a TIMESTAMP causes it to return the correct value
but that shouldn't be required. Cast example:
SELECT DATE_TRUNC('MONTH', CAST(CURRENT_DATE AS TIMESTAMP));