On 01/21/2013 07:40 PM, Gavan Schneider wrote:
...
The points raised by Adrain have prompted some more research on my
part and I am intrigued to learn that on one day of the year in many
countries (e.g., Brazil) where daylight conversion happens over
midnight the local-time version of midnight as start of day does not
exist. Basically the last day of unadjusted time ends at midnight and
rolls directly into 01:00:00 the next day (i.e., time 00:00:00 never
happens on this one day). So the current date-> date+time system must
already have some added complexity/overhead to check for this rare
special case. (If not, there's a bug needs fixing!)
Basically midnight is not safe as a target entity once timezones and
daylight saving get involved. Midday, on the other hand, is a very
solid proposition, no checks required, 12:00:00 will happen in all
time zones on every day of the year! Basically nobody messes with
their clocks in the middle of the day.
So restating:
'2013-10-20'::timestamp ==> 2013-10-20 12:00:00 can never be
wrong; but,
'2013-10-20'::timestamp ==> 2013-10-20 00:00:00 is wrong in some
places.
"Wrong" times occur in every time zone that changes offsets at various
points of the year. Here in California, 02:00:00-02:59:59 March 10, 2013
are "wrong" but PostgreSQL uses a reasonable interpretation to yield a
point-in-time:
select '2013-03-10 0230'::timestamptz;
timestamptz
------------------------
2013-03-10 03:30:00-07
And it does the exact same thing in Brazil:
set timezone to 'Brazil/West';
select '1993-10-17 00:00'::timestamptz;
timestamptz
------------------------
1993-10-17 01:00:00-03
select '1993-10-17'::timestamptz;
timestamptz
------------------------
1993-10-17 01:00:00-03
Note, too, that in both zones when the input is interpreted in the local
zone and displayed in the local zone the date-portion of the
point-in-time is the same as the input date. (While I suppose some
politician somewhere could decide that "fall-back" could cross date
boundaries, I am unaware of any place that has ever done something so
pathological as to have the same date occur in two non-contiguous pieces
once every year.)
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
