Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1934055

Browse files
committed
Please find a small patch to fix the brain damage "century" and
"millennium" date part implementation in postgresql, both in the codeand the documentation, so that it conforms to the official definition.If you do not agree with the official definition, please send yourcomplaint to "pope@vatican.org". I'm not responsible for them;-)With the previous version, the centuries and millenniums had a wrongnumber and started the wrong year. Moreover century number 0, which doesnot exist in reality, lasted 200 years. Also, millennium number 0 lasted2000 years.If you want postgresql to have it's own definition of "century" and"millennium" that does not conform to the one of the society, just givethem another name. I would suggest "pgCENTURY" and "pgMILLENNIUM";-)IMO, if someone may use the options, it means that postgresql is used forhistorical data, so it make sense to have an historical definition. Also,I just want to divide the year by 100 or 1000, I can do that quite easily.BACKWARD INCOMPATIBLE CHANGEFabien Coelho - coelho@cri.ensmp.fr
1 parent296f485 commit1934055

File tree

4 files changed

+176
-14
lines changed

4 files changed

+176
-14
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 23 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.196 2004/03/30 15:53:18 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.197 2004/04/10 18:02:59 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -4948,18 +4948,31 @@ EXTRACT (<replaceable>field</replaceable> FROM <replaceable>source</replaceable>
49484948
<term><literal>century</literal></term>
49494949
<listitem>
49504950
<para>
4951-
Theyear field divided by 100
4951+
Thehistorical definition of a century.
49524952
</para>
49534953

49544954
<screen>
4955-
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-1620:38:40');
4955+
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-1612:21:13');
49564956
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
4957+
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
4958+
<lineannotation>Result: </lineannotation><computeroutput>21</computeroutput>
49574959
</screen>
49584960

49594961
<para>
4960-
Note that the result for the century field is simply the year field
4961-
divided by 100, and not the conventional definition which puts most
4962-
years in the 1900's in the twentieth century.
4962+
An historical century is a period of 100 years.
4963+
The first century starts at 0001-01-01 00:00:00 AD, although
4964+
they did not know at the time. This definition applies to all
4965+
Gregorian calendar countries. There is no number 0 century,
4966+
you go from -1 to 1.
4967+
4968+
If you disagree with this, please write your complaint to:
4969+
Pope, Cathedral Saint-Peter of Roma, Vatican.
4970+
</para>
4971+
4972+
<para>
4973+
Compatibility: if you want the previous postgres version of century,
4974+
just divide the year by 100. Note that with this definition,
4975+
century number 0 lasts 200 years.
49634976
</para>
49644977
</listitem>
49654978
</varlistentry>
@@ -5083,18 +5096,17 @@ SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
50835096
<term><literal>millennium</literal></term>
50845097
<listitem>
50855098
<para>
5086-
Theyear field divided by 1000
5099+
Theconventional historical millennium.
50875100
</para>
50885101

50895102
<screen>
50905103
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
5091-
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
5104+
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
50925105
</screen>
50935106

50945107
<para>
5095-
Note that the result for the millennium field is simply the year field
5096-
divided by 1000, and not the conventional definition which puts
5097-
years in the 1900's in the second millennium.
5108+
Years in the 1900's are in the second millennium.
5109+
The third millennium starts January 1, 2001.
50985110
</para>
50995111
</listitem>
51005112
</varlistentry>

‎src/backend/utils/adt/timestamp.c

Lines changed: 15 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.103 2004/03/30 15:53:18 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.104 2004/04/10 18:02:59 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3273,11 +3273,23 @@ timestamp_part(PG_FUNCTION_ARGS)
32733273
break;
32743274

32753275
caseDTK_CENTURY:
3276-
result= (tm->tm_year /100);
3276+
/* centuries AD, c>0: year in [ (c-1)*100+1 : c*100 ]
3277+
* centuries BC, c<0: year in [ c*100 : (c+1)*100-1 ]
3278+
* there is no number 0 century.
3279+
*/
3280+
if (tm->tm_year>0)
3281+
result= ((tm->tm_year+99) /100);
3282+
else
3283+
/* caution: C division may yave negative remainder */
3284+
result=- ((99- (tm->tm_year-1))/100);
32773285
break;
32783286

32793287
caseDTK_MILLENNIUM:
3280-
result= (tm->tm_year /1000);
3288+
/* see comments above. */
3289+
if (tm->tm_year>0)
3290+
result= ((tm->tm_year+999) /1000);
3291+
else
3292+
result=- ((999- (tm->tm_year-1))/1000);
32813293
break;
32823294

32833295
caseDTK_JULIAN:

‎src/test/regress/expected/date.out

Lines changed: 111 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -819,3 +819,114 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
819819
2
820820
(1 row)
821821

822+
--
823+
-- test extract!
824+
--
825+
-- century
826+
--
827+
SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
828+
date_part
829+
-----------
830+
-2
831+
(1 row)
832+
833+
SELECT EXTRACT(CENTURY FROM DATE '0100-12-31 BC'); -- -1
834+
date_part
835+
-----------
836+
-1
837+
(1 row)
838+
839+
SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); -- -1
840+
date_part
841+
-----------
842+
-1
843+
(1 row)
844+
845+
SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1
846+
date_part
847+
-----------
848+
1
849+
(1 row)
850+
851+
SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1
852+
date_part
853+
-----------
854+
1
855+
(1 row)
856+
857+
SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19
858+
date_part
859+
-----------
860+
19
861+
(1 row)
862+
863+
SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20
864+
date_part
865+
-----------
866+
20
867+
(1 row)
868+
869+
SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20
870+
date_part
871+
-----------
872+
20
873+
(1 row)
874+
875+
SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21
876+
date_part
877+
-----------
878+
21
879+
(1 row)
880+
881+
SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true
882+
true
883+
------
884+
t
885+
(1 row)
886+
887+
--
888+
-- millennium
889+
--
890+
SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1
891+
date_part
892+
-----------
893+
-1
894+
(1 row)
895+
896+
SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1
897+
date_part
898+
-----------
899+
1
900+
(1 row)
901+
902+
SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1
903+
date_part
904+
-----------
905+
1
906+
(1 row)
907+
908+
SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2
909+
date_part
910+
-----------
911+
2
912+
(1 row)
913+
914+
SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2
915+
date_part
916+
-----------
917+
2
918+
(1 row)
919+
920+
SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3
921+
date_part
922+
-----------
923+
3
924+
(1 row)
925+
926+
-- next test to be fixed on the turn of the next millennium;-)
927+
SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3
928+
date_part
929+
-----------
930+
3
931+
(1 row)
932+

‎src/test/regress/sql/date.sql

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -208,3 +208,30 @@ SELECT date 'tomorrow' - date 'today' AS "One day";
208208
SELECTdate'today'-date'yesterday'AS"One day";
209209

210210
SELECTdate'tomorrow'-date'yesterday'AS"Two days";
211+
212+
--
213+
-- test extract!
214+
--
215+
-- century
216+
--
217+
SELECT EXTRACT(CENTURYFROMDATE'0101-12-31 BC');-- -2
218+
SELECT EXTRACT(CENTURYFROMDATE'0100-12-31 BC');-- -1
219+
SELECT EXTRACT(CENTURYFROMDATE'0001-12-31 BC');-- -1
220+
SELECT EXTRACT(CENTURYFROMDATE'0001-01-01');-- 1
221+
SELECT EXTRACT(CENTURYFROMDATE'0001-01-01 AD');-- 1
222+
SELECT EXTRACT(CENTURYFROMDATE'1900-12-31');-- 19
223+
SELECT EXTRACT(CENTURYFROMDATE'1901-01-01');-- 20
224+
SELECT EXTRACT(CENTURYFROMDATE'2000-12-31');-- 20
225+
SELECT EXTRACT(CENTURYFROMDATE'2001-01-01');-- 21
226+
SELECT EXTRACT(CENTURYFROMCURRENT_DATE)>=21AS True;-- true
227+
--
228+
-- millennium
229+
--
230+
SELECT EXTRACT(MILLENNIUMFROMDATE'0001-12-31 BC');-- -1
231+
SELECT EXTRACT(MILLENNIUMFROMDATE'0001-01-01 AD');-- 1
232+
SELECT EXTRACT(MILLENNIUMFROMDATE'1000-12-31');-- 1
233+
SELECT EXTRACT(MILLENNIUMFROMDATE'1001-01-01');-- 2
234+
SELECT EXTRACT(MILLENNIUMFROMDATE'2000-12-31');-- 2
235+
SELECT EXTRACT(MILLENNIUMFROMDATE'2001-01-01');-- 3
236+
-- next test to be fixed on the turn of the next millennium;-)
237+
SELECT EXTRACT(MILLENNIUMFROMCURRENT_DATE);-- 3

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp