Thread: Improve timestamp substraction to be DST-aware
Hi, I would like to implement item from TODO list: "Improve TIMESTAMP WITH TIME ZONE subtraction to be DST-aware". To compute interval properly, we need time zone. Currently in timestamp we don't have time zone information, so I would use actual session time zone for computations. This can cause problems in the future, when TZ info will be saved in DB (this is also on TODO list). In such case, user may suspect, that stored TZ will be used, and probably behavior of subtraction will change. Anyway, I think, that using session TZ now is better than not using it at all. Can I start with implementation? Best regards Piotr Marcinczyk
Piotr Marcinczyk <pmarcinc@gmail.com> writes:
> I would like to implement item from TODO list: "Improve TIMESTAMP WITH
> TIME ZONE subtraction to be DST-aware".
> To compute interval properly, we need time zone. Currently in timestamp
> we don't have time zone information, so I would use actual session time
> zone for computations. This can cause problems in the future, when TZ
> info will be saved in DB (this is also on TODO list). In such case, user
> may suspect, that stored TZ will be used, and probably behavior of
> subtraction will change. Anyway, I think, that using session TZ now is
> better than not using it at all.
TBH, that doesn't sound like an especially great idea from here. The
value of a timestamp difference is currently exact, but you're proposing
to make it fuzzy and context-dependent.
The alternative proposal that's been on the table for awhile (see the
preceding entry in the TODO list) is to remove the interval_justify_hours
call in timestamp_mi, which would also have the effect of fixing the
inconsistency that T1 + (T2 - T1) doesn't necessarily yield T2. And it
would do that a lot more straightforwardly, with less risk that there's
still corner cases that would misbehave.
If it's not the T1 + (T2 - T1) issue that's bothering you, perhaps
you should explain exactly what results you're hoping to get by changing
this behavior.
regards, tom lane
On Sun, 2013-12-01 at 17:40 -0500, Tom Lane wrote: > Piotr Marcinczyk <pmarcinc@gmail.com> writes: > > I would like to implement item from TODO list: "Improve TIMESTAMP WITH > > TIME ZONE subtraction to be DST-aware". > > > To compute interval properly, we need time zone. Currently in timestamp > > we don't have time zone information, so I would use actual session time > > zone for computations. This can cause problems in the future, when TZ > > info will be saved in DB (this is also on TODO list). In such case, user > > may suspect, that stored TZ will be used, and probably behavior of > > subtraction will change. Anyway, I think, that using session TZ now is > > better than not using it at all. > > TBH, that doesn't sound like an especially great idea from here. The > value of a timestamp difference is currently exact, but you're proposing > to make it fuzzy and context-dependent. I know that it is not perfect (I pointed risks), but I have no better idea. > > The alternative proposal that's been on the table for awhile (see the > preceding entry in the TODO list) is to remove the interval_justify_hours > call in timestamp_mi, which would also have the effect of fixing the > inconsistency that T1 + (T2 - T1) doesn't necessarily yield T2. And it > would do that a lot more straightforwardly, with less risk that there's > still corner cases that would misbehave. > > If it's not the T1 + (T2 - T1) issue that's bothering you, perhaps > you should explain exactly what results you're hoping to get by changing > this behavior. > In SQL99 "4.7 Datetimes and intervals" I read, that day-time intervals (I think, that our interval has this type) should have hours in range 0-23. I suggest to remove preceding entry from TODO list, and not treat this as alternative. Current behavior is OK. Regarding this, we have two options: use session TZ, or wait for implementation of TZ saved in timestamp field. In my opinion saving TZ in field doesn't give serious benefits, and it's probable that it will never be implemented. In this case, using session TZ is sufficient. Can You explain, why do You read this proposal as fuzzy? I believe that using session context is normal in many cases. Maybe I should consider saving TZ in timestamp once again? Best regards Piotrek Marcinczyk
On Mon, Dec 2, 2013 at 11:43:06PM +0100, Piotr Marcinczyk wrote: > > The alternative proposal that's been on the table for awhile (see the > > preceding entry in the TODO list) is to remove the interval_justify_hours > > call in timestamp_mi, which would also have the effect of fixing the > > inconsistency that T1 + (T2 - T1) doesn't necessarily yield T2. And it > > would do that a lot more straightforwardly, with less risk that there's > > still corner cases that would misbehave. > > > > If it's not the T1 + (T2 - T1) issue that's bothering you, perhaps > > you should explain exactly what results you're hoping to get by changing > > this behavior. > > > > In SQL99 "4.7 Datetimes and intervals" I read, that day-time intervals > (I think, that our interval has this type) should have hours in range > 0-23. I suggest to remove preceding entry from TODO list, and not treat > this as alternative. Current behavior is OK. > > Regarding this, we have two options: use session TZ, or wait for > implementation of TZ saved in timestamp field. In my opinion saving TZ > in field doesn't give serious benefits, and it's probable that it will > never be implemented. In this case, using session TZ is sufficient. > > Can You explain, why do You read this proposal as fuzzy? I believe that > using session context is normal in many cases. Maybe I should consider > saving TZ in timestamp once again? I have remove the TODO item and added an interval subtraction/addition section to the docs for PG 9.4: http://www.postgresql.org/docs/devel/static/functions-datetime.html The paragraphs being with "When adding an interval value to" and "Subtraction of dates and timestamps can also be complex.". Is there anything more to add there? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +