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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
