Re: Date with time zone

On Mon, Nov 30, 2009 at 7:22 AM, Martijn van Oosterhout
<kleptog(at)svana(dot)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 <kleptog(at)svana(dot)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.