Improve timestamp substraction to be DST-aware

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers