On Mon, Nov 30, 2009 at 7:22 AM, Martijn van Oosterhout <klep...@svana.org>wrote:
> 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. > My bad. > > > 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. > 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". > > > A day in this context meant midnight to midnight. > > That's your definition, but hardly the only useful one. > I agree. What I just wanted to explain is that in my original sentence/context, it meant from midnight to midnight. > > > 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. > Everything seems to point to something like that, yes. > > Have a ncie day, > -- > Martijn van Oosterhout <klep...@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. > > -----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.
