On Wed, 26 Oct 2005 00:44:50 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> John R Pierce <pierce@hogranch.com> writes:
> > the whole DST thing falls apart when you deal with places that don't
> > respect it... arizona (except the navajo nation), for instance....
>
> > it would be impossible to calculate the 'correct' answer without knowing
> > the exact location...
>
> No, rather say "without knowing the correct timezone". All of this is
> about doing the calculations properly according to the rules of the
> current TimeZone setting. It's irrelevant whether the calculations are
> correct with respect to some other timezone rules; obviously they won't
> be.

I think this is what I was getting at. In my timezone 'Australia/NSW',
we have daylight savings. Is that used any way when the calculation
happens or the result is displayed?

In the examples we've been using, does anything change if the -05 and
-04 are changed to timezones (EDT/PST/...)?

klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Klint Gore <kg@kgb.une.edu.au> writes:
> I think this is what I was getting at. In my timezone 'Australia/NSW',
> we have daylight savings. Is that used any way when the calculation
> happens or the result is displayed?

Absolutely. The examples Bruce and I have been throwing around assume
US Eastern timezone, because that's where we live, but the code should
adapt to your local zone rules wherever you are.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

John R Pierce <pierce@hogranch.com> writes:
> heh. as an aside... the original reason I got ON this and the jdbc
> list was due to an issue we had with an inhouse java+pgsql program when
> it was deployed in Singapore... SGT wasn't recognized, then I
> discovered that China (another later deployment location) uses CST which
> collides with Central Standard Time and convinced the developers they
> HAD to use numeric times.

Yeah, that is a bee in my bonnet too. We fixed a bunch of issues around
SET TIMEZONE by adopting the zic code, but there's still a hardwired
list of timezone names (or more accurately, GMT-offset names) embedded
in datetime.c for purposes of parsing datetime input strings. We need
to make that list user-configurable. The existing "australian_timezones"
setting is just a half-baked attempt at that.

> TIMEZONES SUCK!

Sir Arthur Clarke (he who invented the idea of geosynchronous
communications satellites) has written of a far future where everyone
on earth thinks in UTC time. Works for me ;-) but I don't suppose the
Postgres codebase will live that long.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 2005-10-26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Klint Gore <kg@kgb.une.edu.au> writes:
>> On Tue, 25 Oct 2005 23:41:54 -0400 (EDT), Bruce Momjian
>> <pgman@candle.pha.pa.us> wrote:
>>> test=> select
>>> test-> ('2005-10-30 13:22:00-05'::timestamptz -
>>> test(> '2005-10-29 13:22:00-04'::timestamptz);
>>> ?column?
>>> ----------
>>> 25:00:00
>>> (1 row)
>
>> Is that actually the correct answer?
>
> I'm of the opinion that the correct answer, or at least the usually
> desired answer, is "1 day".

Timestamp subtraction is not age(). Subtraction should be precise, age()
is allowed to justify.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On 2005-10-26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> John R Pierce <pierce@hogranch.com> writes:
>> the whole DST thing falls apart when you deal with places that don't
>> respect it... arizona (except the navajo nation), for instance....
>
>> it would be impossible to calculate the 'correct' answer without knowing
>> the exact location...
>
> No, rather say "without knowing the correct timezone". All of this is
> about doing the calculations properly according to the rules of the
> current TimeZone setting.

Um, what? Under what conditions is it permissable for simple arithmetic on
(only) timestamptz values (which may have originated in different timezones
neither of which is the current one) to be dependent on the current timezone
setting?

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On 2005-10-26, Klint Gore <kg@kgb.une.edu.au> wrote:
> [sorry about the previous email, I quoted the wrong bit and clicked the
> wrong button]
>
> On Tue, 25 Oct 2005 23:41:54 -0400 (EDT), Bruce Momjian
><pgman@candle.pha.pa.us> wrote:
>> test=> select
>> test-> ('2005-10-30 13:22:00-05'::timestamptz -
>> test(> '2005-10-29 13:22:00-04'::timestamptz);
>> ?column?
>> ----------
>> 25:00:00
>> (1 row)
>
> Is that actually the correct answer?

Absolutely.

> Disregarding daylight savings, there is 25hrs between them. Once
> daylight savings is taken into account there should be 24 or 26 hours
> between them (southern/northern hemisphere respectively).
>
> Or have I missed something obvious?

Yes. The difference between those two times is exactly the same whatever
timezone the person running the query is in, because they are _completely
specified_ by the input. That difference is 25 hours. There are no
circumstances in which that difference could ever be 24 or 26 hours
regardless of what timezone the user is in. (The only way in which the
timezone makes a difference is that a user in US/Eastern might, under
some circumstances, wish to regard that time period as '1 day' rather
than 25 hours; no user in any other timezone would do so. Since the
conversion from '25 hours' to '1 day' loses information, it should not
happen.)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

John R Pierce wrote:
> >> test=> select
> >> test-> ('2005-10-30 13:22:00-05'::timestamptz -
> >> test(> '2005-10-29 13:22:00-04'::timestamptz);
> >> ?column?
> >> ----------
> >> 25:00:00
> >> (1 row)
> >
> >
> > Is that actually the correct answer?
> >
> > Disregarding daylight savings, there is 25hrs between them. Once
> > daylight savings is taken into account there should be 24 or 26 hours
> > between them (southern/northern hemisphere respectively).
>
> the whole DST thing falls apart when you deal with places that don't
> respect it... arizona (except the navajo nation), for instance....
>
> it would be impossible to calculate the 'correct' answer without knowing
> the exact location...

Yes, I ran this in EST5EDT time, so the answer is correct for that
timezone.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

I saw a lot of disussion because I forgot to specify that my tests were
for EST5EDT, but what about the use of interval_justify_hours() in
timestamp_mi(). Is this something we want to change?

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Klint Gore wrote:
> > On Tue, 25 Oct 2005 13:28:00 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > regression=# select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval;
> > > ?column?
> > > ------------------------
> > > 2005-10-30 13:22:00-05
> > > (1 row)
> > >
> > > regression=# select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29 13:22:00-04'::timestamptz;
> > > ?column?
> > > ----------------
> > > 1 day 01:00:00
> > > (1 row)
> > >
> > > ISTM that given the former result, the latter calculation ought to
> > > produce '1 day', not something else.
> >
> > Would the '1 day' result know it was 24 hours or be the new 23/24/25
> > hour version of '1 day'?
>
> It has no idea. When you do a subtraction, it isn't clear if you are
> interested in "days" or "hours", so we give hours. If you want days,
> you should convert the timestamps to dates and just subtract them.
>
> > If it was the new version, could you get the original values back?
> > i.e. what would be the result of
> > select
> > ('2005-10-29 13:22:00-04'::timestamptz +
> > ('2005-10-30 13:22:00-05'::timestamptz -
> > '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
>
> You bring up a good point here. With current CVS your subtraction
> yields:
>
> test-> ('2005-10-30 13:22:00-05'::timestamptz -
> test(> '2005-10-29 13:22:00-04'::timestamptz);
> ?column?
> ----------------
> 1 day 01:00:00
> (1 row)
>
> so adding that to the first timestamp gets:
>
> test=> select
> test-> ('2005-10-29 13:22:00-04'::timestamptz +
> test(> ('2005-10-30 13:22:00-05'::timestamptz -
> test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
> timezone
> ---------------------
> 2005-10-30 14:22:00
> (1 row)
>
> This is certainly _not_ what someone would expect as a return value.
> What happens is that we subtract to generate the number of hours
> different, but then get all smart that "oh, that is one day to add, and
> one hour" and return an unexpected value.
>
> This is actually a good argument that the use of
> interval_justify_hours() in timestamp_mi() is a mistake. Without this
> call, we have:
>
> test=> select
> test-> ('2005-10-30 13:22:00-05'::timestamptz -
> test(> '2005-10-29 13:22:00-04'::timestamptz);
> ?column?
> ----------
> 25:00:00
> (1 row)
>
> and
>
> test=> select
> test-> ('2005-10-29 13:22:00-04'::timestamptz +
> test(> ('2005-10-30 13:22:00-05'::timestamptz -
> test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
> timezone
> ---------------------
> 2005-10-30 13:22:00
> (1 row)
>
> but it also has the tendency to return some very high values for hours:
>
> test=> select
> test-> ('2005-12-30 13:22:00-05'::timestamptz -
> test(> '2005-10-29 13:22:00-04'::timestamptz);
> ?column?
> ------------
> 1489:00:00
> (1 row)
>
> but again, if you want days, you can cast to days.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thanks for all this discussion, fixing, etc. I'm currently having
"issues" getting postgres' date/time functions to do what I want. You
have obviously spotted some of the reasons for this.

Many of my issues disappear when I use 8.1, but it's still in beta. Is
it safe for me to use 8.1 in production, if I don't use any of the new
features? If not, would it be possible to backport these date/time
changes to 8.0 so that my program can operate correctly before 8.1 is
finished beta? I can do this backporting myself if someone can point me
to the relevant files. (I'm sure I can work it out myself if necessary,
but I'm a little busy at the moment).


BTW, Postgres' date functions are *great* except for these minor
problems. The best I've ever used.


Thanks!
Nicholas


Klint Gore wrote:

>On Tue, 25 Oct 2005 13:28:00 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>
>>regression=# select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval;
>> ?column?
>>------------------------
>> 2005-10-30 13:22:00-05
>>(1 row)
>>
>>regression=# select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29 13:22:00-04'::timestamptz;
>> ?column?
>>----------------
>> 1 day 01:00:00
>>(1 row)
>>
>>ISTM that given the former result, the latter calculation ought to
>>produce '1 day', not something else.
>>
>>
>
>Would the '1 day' result know it was 24 hours or be the new 23/24/25
>hour version of '1 day'?
>
>If it was the new version, could you get the original values back?
>i.e. what would be the result of
>select
>('2005-10-29 13:22:00-04'::timestamptz +
>('2005-10-30 13:22:00-05'::timestamptz -
> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
>
>klint.
>
>+---------------------------------------+-----------------+
>: Klint Gore : "Non rhyming :
>: EMail : kg@kgb.une.edu.au : slang - the :
>: Snail : A.B.R.I. : possibilities :
>: Mail University of New England : are useless" :
>: Armidale NSW 2351 Australia : L.J.J. :
>: Fax : +61 2 6772 5376 : :
>+---------------------------------------+-----------------+
>
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I saw a lot of disussion because I forgot to specify that my tests were
> for EST5EDT, but what about the use of interval_justify_hours() in
> timestamp_mi(). Is this something we want to change?

It's too late to mess with it for 8.1, but see my previous message
proposing a set of TODO items for future work.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org