Thread: Re: [ADMIN] Date Return must be As per Natural Calander

Re: [ADMIN] Date Return must be As per Natural Calander

From
Robert Treat
Date:
[rob@camel rms_db]$ cal 9 1752
   September 1752
Su Mo Tu We Th Fr Sa
       1  2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

I guess adding 1 day to 1752-09-02 should give us 1752-09-14, but your
right, it gives us 1752-09-03.

Forwarding this to -bugs


Robert Treat


On Sun, 2003-02-23 at 11:22, Aspire Something wrote:
>  Hi all ,
> Please Permit me to recive  ur valuable  knowledge and experience :-)
>
> In the Postgresql Documentation (read it in /7.3.2/units-history.html)
> it has been given that Postgresql  follows the Julian calander (Which
> indead is being  used by my system by default )
>
> So does it not mean when I add to a date  (integer) it must return the
> date as per the calendar  :
>
> i.e
>
> The following sql statements
> retuns date  1752-09-03
> insted of   1752-09-14
> you may do :
> $cal 9 1752
> on unix promt to verify  (Windows user sorry ur calendar may not show
> dates  <1970 !!! atleat mine does not )
> <code>
>  select date('1752-09-02') + 1 as some_date ;
>  some_date
> ------------
>  1752-09-03
> (1 row)
>  select date('1752-09-02') + interval'1 day' as some_day;
>       some_day
> ---------------------
>  1752-09-03 00:00:00
> (1 row)
> </code>
> Now  every thing above may sound stupid but if we in near future come
> accross the same situation how will the data base respond when my
> database relies 90% on the timestamp value
> their will be total mismatch of calendar(Which people follow) and
> database returning dates.
>
> Regards ,
> Aspire
>
> My Sys Config is
> ==================
> Red Hate 7.2 Kernel 2.4.7-10 on an i686
> Postgresql 7.3.2
> GCC 3.0.2 20010905
> =================
>




Re: [ADMIN] Date Return must be As per Natural Calander

From
Tom Lane
Date:
Robert Treat <xzilla@users.sourceforge.net> writes:
> I guess adding 1 day to 1752-09-02 should give us 1752-09-14, but your
> right, it gives us 1752-09-03.

As was pointed out at length just recently, the transition from Julian
to Gregorian calendars happened at different times in different places.
So the above claim is only correct for some places.

The conclusion from the previous discussion was that our existing
behavior (extrapolate Gregorian rules backwards indefinitely) is as
defensible as anything else that would be likely to get coded.

I suppose you could imagine something that looks at the locale and
tries to guess the appropriate transition date ... but I don't foresee
anyone getting very excited about coding it.

            regards, tom lane

Re: [ADMIN] Date Return must be As per Natural Calander

From
Oliver Elphick
Date:
On Mon, 2003-02-24 at 22:59, Robert Treat wrote:
> [rob@camel rms_db]$ cal 9 1752
>    September 1752
> Su Mo Tu We Th Fr Sa
>        1  2 14 15 16
> 17 18 19 20 21 22 23
> 24 25 26 27 28 29 30
>
> I guess adding 1 day to 1752-09-02 should give us 1752-09-14, but your
> right, it gives us 1752-09-03.
>
> Forwarding this to -bugs

cal is only valid for Britain and British colonies (and still gets other
things wrong, because the previous year started on 25th March, but cal
doesn't know it).  The date of change to the Gregorian calendar is
different for different countries.  There was a discussion of this on
the patches list recently
(http://archives.postgresql.org/pgsql-patches/2003-02/msg00038.php and
the surrounding thread).  The SQL spec calls for the Gregorian calendar
to be extended backwards.

The proper place for handling conversion to other calendars is through a
set of suitable functions, which haven't been written yet.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Peace I leave with you, my peace I give unto you; not
      as the world giveth, give I unto you. Let not your
      heart be troubled, neither let it be afraid."
                                John 14:27


Re: [ADMIN] Date Return must be As per Natural Calander

From
Oliver Elphick
Date:
On Tue, 2003-02-25 at 22:08, Tim Ellis wrote:
> On Mon, 2003-02-24 at 23:03, Oliver Elphick wrote:
> > different for different countries.  There was a discussion of this on
> > the patches list recently
> > (http://archives.postgresql.org/pgsql-patches/2003-02/msg00038.php and
> > the surrounding thread).  The SQL spec calls for the Gregorian calendar
> > to be extended backwards.
>
> In that post, you reference a web page:
>   http://serendipity.magnet.ch/hermetic/cal_stud/cal_art.htm
>
> Right now I get a 404 page not found for that URL. Any clues for me/us?
> http://www.serendipity.li/ndx.htm wasn't much help (and don't ask me how
> it got changed from .ch to .li -- they seem related sites).

Sorry. I Google-searched it that day, and I haven't seen it before or
since.  Try Google - maybe it will still be cached.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Peace I leave with you, my peace I give unto you; not
      as the world giveth, give I unto you. Let not your
      heart be troubled, neither let it be afraid."
                                John 14:27


Re: [ADMIN] Date Return must be As per Natural Calander

From
"Ross J. Reedstrom"
Date:
On Mon, Feb 24, 2003 at 11:06:38PM -0500, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > I guess adding 1 day to 1752-09-02 should give us 1752-09-14, but your
> > right, it gives us 1752-09-03.
>
> As was pointed out at length just recently, the transition from Julian
> to Gregorian calendars happened at different times in different places.
> So the above claim is only correct for some places.
>
> The conclusion from the previous discussion was that our existing
> behavior (extrapolate Gregorian rules backwards indefinitely) is as
> defensible as anything else that would be likely to get coded.

To quote SQL1992:

  4.5.3  Operations involving datetimes and intervals

  [...]
  Arithmetic operations involving items of type datetime or inter-
  val obey the natural rules associated with dates and times and
  yield valid datetime or interval results according to the Gregorian
  calendar.

Ross

Re: [ADMIN] Date Return must be As per Natural Calander

From
Tim Ellis
Date:
On Mon, 2003-02-24 at 23:03, Oliver Elphick wrote:
> different for different countries.  There was a discussion of this on
> the patches list recently
> (http://archives.postgresql.org/pgsql-patches/2003-02/msg00038.php and
> the surrounding thread).  The SQL spec calls for the Gregorian calendar
> to be extended backwards.

In that post, you reference a web page:
  http://serendipity.magnet.ch/hermetic/cal_stud/cal_art.htm

Right now I get a 404 page not found for that URL. Any clues for me/us?
http://www.serendipity.li/ndx.htm wasn't much help (and don't ask me how
it got changed from .ch to .li -- they seem related sites).

--
Tim Ellis
Author: http://tedia2sql.tigris.org