Re: Yet Another Timestamp Question: Time Defaults

On 01/21/2013 08:56 PM, Gavan Schneider wrote:
> On Monday, January 21, 2013 at 10:53, Steve Crawford wrote:
>
>> On 01/21/2013 02:48 PM, Gavan Schneider wrote:
>>> ....
>>> Taking another tangent I would much prefer the default time to be
>>> 12:00:00 for the conversion of a date to timestamp(+/-timezone).
>>>
>>> Propose: '2013-12-25'::timestamp ==> 2013-12-25 12:00:00
>>>
>>> The benefit of the midday point is that the actual date will not
>>> change when going through the timezone conversion.
>>
>> Just like it doesn't change now? (I just checked against all of the
>> more than 1,100 zones in PG without seeing a problem.)
>>
> I find this result strange to say the least... our conversation is
> straddling Monday(you)/Tuesday(me). We shared the time point
> 2013-01-22 01:30 UTC, but called it different things, viz., 2013-01-22
> 12:30 and 2013-01-21 17:30.

We can call it all sorts of things but it is, in fact, the same point in
time. What you have done is omitted a critical piece of information
necessary for a "fully qualified" point-in-time - the time zone.

Now if I tell my wife I'll be home by 6 she says, "OK, see you then,"
not "do you mean AM or PM? Er, Pacific time? Today?" In other words she
makes reasonable assumptions about what point-in-time I am conveying.

(Aside...It reminds me of the joke about the father admonishing his
daughter's date to have her back by ten-fifteen to which he responds,
"Mid-October? Cool!")

A date alone can be interpreted as any of a number of points-in-time
covering a roughly two-day range:

select '2013-01-22 23:59 posix/Pacific/Midway'::timestamptz -
'2013-01-22 00:00 Pacific/Kiritimati'::timestamptz;

1 day 01:00:00

So in order to calculate a single point-in-time, PostgreSQL, like my
wife, has to make certain assumptions regarding the missing information
(and fortunately PostgreSQL follows the SQL spec in this regard). The
assumptions it makes are:

1) Interpret the date in local time not the date somewhere else in the
world.

2) Interpret the missing time portion as 00:00:00.

You now have a point-in-time, not a date. You can display that
point-in-time in whatever timezone you wish and some will have the same
date as your local date while others will not. Assuming that the time is
12:00:00 rather than 00:00:00 does not change that fact:

--localtime is US Pacific
select '2013-01-22 12:00'::timestamptz at time zone 'Pacific/Kiritimati';

2013-01-23 10:00:00

Cheers,
Steve