Обсуждение: Is this a bug? : select '26/10/2003'::date + interval '1 day';

Hi.

Is this a bug?

s001=> select '25/10/2003'::date + interval '1 day';
         ?column?
--------------------------
 26/10/2003 00:00:00 CEST
(1 row)

s001=> select '26/10/2003'::date + interval '1 day';
        ?column?
-------------------------
 26/10/2003 23:00:00 CET  (????)
(1 row)

Is that the same user on the same machine and on the same database?
If so, than its probably a bug, since CEST and CET have one hour
difference but the timezone shouldn't be changing over time.

Arjen

> -----Oorspronkelijk bericht-----
> Van: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] Namens Conxita Marín
> Verzonden: donderdag 22 mei 2003 12:28
> Aan: Pgsql-General-post (E-mail)
> Onderwerp: [GENERAL] Is this a bug? : select
> '26/10/2003'::date + interval '1 day';
>
>
> Hi.
>
> Is this a bug?
>
> s001=> select '25/10/2003'::date + interval '1 day';
>          ?column?
> --------------------------
>  26/10/2003 00:00:00 CEST
> (1 row)
>
> s001=> select '26/10/2003'::date + interval '1 day';
>         ?column?
> -------------------------
>  26/10/2003 23:00:00 CET  (????)
> (1 row)
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

=?iso-8859-1?Q?Conxita_Mar=EDn?= <comarin@telefonica.net> writes:
> Is this a bug?

Not if Oct 26 is a daylight savings transition day where you live.

There's been discussion of whether type interval should represent
'1 day' differently from '24 hours'.  The current behavior is surely
correct for an interval of '24 hours', but arguably it's not what you
want if you say '1 day'.  No one's got round to making it happen though.

In the meantime, I suspect what you are really looking for is the
date-plus-integer operation, not the date-plus-interval operation.
The latter promotes the date to a timestamp, which doesn't seem to
be what you would like.

            regards, tom lane

On Thu, 22 May 2003, [iso-8859-1] Conxita Mar�n wrote:

> Hi.
>
> Is this a bug?
>
> s001=> select '25/10/2003'::date + interval '1 day';
>          ?column?
> --------------------------
>  26/10/2003 00:00:00 CEST
> (1 row)
>
> s001=> select '26/10/2003'::date + interval '1 day';
>         ?column?
> -------------------------
>  26/10/2003 23:00:00 CET  (????)
> (1 row)

Is that your daylight savings time changeover point (appears to be from
the output, but it's worth checking).  IIRC '1 day' is taken as equivalent
to 24 hours which because of the time zone switch causes the above (it'd
presumably be 27/10/2003 00:00:00 CEST but that's not your timezone
anymore).

Thanks all for your responses.

That is, Oct 26 is a daylight savings transition day im my area. Postgres
interpret interval '1 day' equal to 24 hours and Oct 26 day has 25 hours, so
the day doesn't change.

To add exactly 1 day to any date I do this, such as Tom Lane suggests:

s001=> select '26/10/2003'::date + 1;
  ?column?
------------
 27/10/2003
(1 row)

Regards,

Conxita

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Stephan Szabo
Sent: jueves, 22 de mayo de 2003 18:13
To: cmarin@dims.com
Cc: Pgsql-General-post (E-mail)
Subject: Re: [GENERAL] Is this a bug? : select '26/10/2003'::date +
interval


s001=> select '26/10/2003'::date + 1;
  ?column?
------------
 27/10/2003
(1 row)

On Thu, 22 May 2003, [iso-8859-1] Conxita Marín wrote:

> Hi.
>
> Is this a bug?
>
> s001=> select '25/10/2003'::date + interval '1 day';
>          ?column?
> --------------------------
>  26/10/2003 00:00:00 CEST
> (1 row)
>
> s001=> select '26/10/2003'::date + interval '1 day';
>         ?column?
> -------------------------
>  26/10/2003 23:00:00 CET  (????)
> (1 row)

Is that your daylight savings time changeover point (appears to be from
the output, but it's worth checking).  IIRC '1 day' is taken as equivalent
to 24 hours which because of the time zone switch causes the above (it'd
presumably be 27/10/2003 00:00:00 CEST but that's not your timezone
anymore).


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Thursday 22 May 2003 11:27 am, Conxita Marín wrote:
> Hi.
>
> Is this a bug?
>
> s001=> select '25/10/2003'::date + interval '1 day';
>          ?column?
> --------------------------
>  26/10/2003 00:00:00 CEST
> (1 row)
>
> s001=> select '26/10/2003'::date + interval '1 day';
>         ?column?
> -------------------------
>  26/10/2003 23:00:00 CET  (????)
> (1 row)

If CEST is Central European Summer Time
And CET is Central European Time

Then I'd guess you're looking at the end of the summer-time adjustment when
the clocks go back 1 hour.
--
  Richard Huxton
  Archonet Ltd