"Najib Abi Fadel" <nabifadel@usj.edu.lb> writes:
>> You didn't answer the question though: what timezone are you using?
> Asia/Beirut
Okay, with that I can reproduce it. That zone is one of the ones where
the DST transitions occur just at midnight. So there really isn't any
"midnight local time" on that date; the first valid local time is 1AM.
The reason to_char() is showing this behavior is there is no
to_char(date) function, only to_char(timestamp). If you look at what
the implied promotion is doing, you see
regression=> set TimeZone TO 'Asia/Beirut';
SET
regression=> SELECT '2005-03-27'::date::timestamptz;
timestamptz
------------------------
2005-03-26 23:00:00+02
(1 row)
Presented with the invalid local time '2005-03-27 00:00:00', the
timestamp converter chooses to treat it as midnight in the local
daylight-savings time, which is more conventionally written as 11PM
standard time. And then of course your to_char() format only shows
the date part of that.
7.4 and later are more consistent about what is done with "invalid"
local times: they always treat an invalid or ambiguous time as being
local standard time. So in 7.4 and later your example works as
desired:
regression=# set TimeZone TO 'Asia/Beirut';
SET
regression=# SELECT '2005-03-27'::date::timestamptz;
timestamptz
------------------------
2005-03-27 01:00:00+03
(1 row)
"Midnight standard time" is more conventionally 1AM daylight time,
and then you get the right result when looking only at the date part.
Of course this just shifts the locus of pain: if there were any
timezones that switched at 11PM, they'd have funny behavior instead.
But AFAIK there aren't any.
Bottom line: update to 7.4. You could hack around it in 7.3 by
explicitly promoting the date to timestamp without time zone
(or better, make a function to_char(date) to do it for you)
but I think your time would be better spent on an update.
regards, tom lane
