Re: Yet Another Timestamp Question: Time Defaults

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