Date Return must be As per Natural Calander
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
=================
[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
Show quoted text
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 ,
AspireMy Sys Config is
==================
Red Hate 7.2 Kernel 2.4.7-10 on an i686
Postgresql 7.3.2
GCC 3.0.2 20010905
=================
On Monday 24 February 2003 05:59 pm, you 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 30I guess adding 1http://www.genfair.com/dates.htm day to 1752-09-02 should
give us 1752-09-14, but your
right, it gives us 1752-09-03.
Forwarding this to -bugs
take a look at this website!
http://www.genfair.com/dates.htm
i quote:::::::::
The cause of ambiguities - 1. Julian vs. Gregorian
Unfortunately the sixteenth century was a time of severe religious division
right across Europe. States still obedient to the Papacy adopted the
Gregorian calendar at once, that is in October 1582. These were Spain,
Portugal and Italy, with France following in December of that year, and
Prussia, the Catholic States of Germany, Holland and Flanders on 1st January
1583. Catholic parts of Switzerland followed in the next two years, Poland
went Gregorian in 1586 and Hungary in 1587. In the year 1700 the German and
Netherland Protestant States and Denmark adopted the Gregorian calendar.
Sweden wavered, keeping the Gregorian non-leap year of 1700 but reverting
back in 1712 by having two leap days that year. The Swedes finally settled
for the Gregorian calendar in 1753 omitting the eleven days from 18th to the
end of February of that year.
In Britain the Gregorian calendar was not adopted until 1752, and the start of
year date was changed to 1st January by the same Act of Parliament. The day
following 31st December 1751 was decreed to be 1st January 1752 and 2nd
September 1752 was followed by 14th September. As England had taken the year
1700 to be a leap year, the difference between the Julian and Gregorian
calendars now amounted to eleven days. The changes were to apply to all the
Dominions of the British Crown, including of course the North American
colonies, and will be the ones most of interest to family historians reading
this article.
Show quoted text
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 ,
AspireMy Sys Config is
==================
Red Hate 7.2 Kernel 2.4.7-10 on an i686
Postgresql 7.3.2
GCC 3.0.2 20010905
=================---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
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
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 30I 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
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
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
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.htmRight 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