Re: Date with time zone - Mailing list pgsql-general

On Mon, Nov 30, 2009 at 01:51:33AM -0300, Eduardo Piombino wrote:
> Analysis of the extra complications added by DST's does not add anything,
> yet, to the point I'm trying to make, regardless the lack of such cases in
> practice.

The major problem with timezone support in SQL is that they basically
punt on DST altogether, making it somewhat useless for general use.
(Which is why the timetz type as it is defined by SQL doesn't actually
do what you want.) Saying that you're going to ignore DST in the first
round is ignoring the elephant in the room: you *have* to deal with it.

While your example of 6pm London Time is good, I'm having a hard time
imagining you'd want to store such a value in a database.

> From a technical point of view, that time, 6PM London Time, can be easily
> defined by a "time with time zone" data type, contrary to any other setup
> based on assumptions (such as assigning the default local time zone of where
> the server is to the "time without time zone", or keeping track of the time
> zone on a different data field), with a simple "18:00:00+00" (+00 stands for
> London Time).

Bzzt. +00 is not "London Time", it's UTC. London time is sometimes +01.

> Wouldn't it be nice/elegant to be able to specify that specific day in a
> "date with time zone" format?
> Something like "24/12/2009+00", that would be like adding an offset to both
> start and end time.
> That way, the date itself knows where in the world its being placed
> (London), as an instance of an abstract definition of a date (December
> 24th/2009).

Frankly, I think it's easier and clearer to say the interval from
1261612800 to 1261699200 seconds after 1970-01-01 00:00:00 UTC. That's
at least totally unambiguous, now and into the future. And everybody
can trivially convert it to whatever view they want.

> A day in this context meant midnight to midnight.

That's your definition, but hardly the only useful one.

> Answer me this question then:
> What day is it now?
> You can't answer me Monday, November 30th.
> You should instead ask me: -Where?
> Because the current day will depend on the location, aka, time zone.

Indeed, the question is invalid. Long experience has taught me that
when dealing with times you must strictly seperate the concept of "an
instant in time" and "what your clock says". An instant in time is what
is represented by the "timestamptz" type and is (barring relativity)
universal. What your clock says is what the "timestamp" type gives and
any time I've seen it used to store data it causes grief in the end.
Mainly due to the fact that even with timezone information it's
ambiguous.

If your argument is that what we actually need is an "interval with
time zone" type, then I could possibly agree with you there.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.