On Mon, Nov 30, 2009 at 01:51:33AM -0300, Eduardo Piombino wrote:The major problem with timezone support in SQL is that they basically
> 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.
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.
Bzzt. +00 is not "London Time", it's UTC. London time is sometimes +01.
> 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).
My bad.
Frankly, I think it's easier and clearer to say the interval from
> 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).
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.
Me too. I was just hoping to be able to come up with another totally unambiguous way of expressing the same interval in a more human readable form, like "24/12/2009+00", that would denote the same exact interval that you mentioned: "1261612800 to 1261699200 seconds after 1970-01-01 00:00:00 UTC".
That's your definition, but hardly the only useful one.
> A day in this context meant midnight to midnight.
I agree. What I just wanted to explain is that in my original sentence/context, it meant from midnight to midnight.
Indeed, the question is invalid. Long experience has taught me that
> 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.
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.
Everything seems to point to something like that, yes.
Have a ncie day,
--
Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
iD8DBQFLE5zJIB7bNG8LQkwRAjnJAJ96UZjaAy13METKCHN87mT65TVf5ACcCamb
OFS1DdzDfZIWy9AGW5Gspv8=
=ZdrH
-----END PGP SIGNATURE-----
You too, and thank you all guys for your comments, specially Adrian, they are really appreciated.
