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

Commit46be0c1

Browse files
committed
> After all that about numbering centuries and millenia correctly,
> why does CVS tip still give me>> regression=# select extract(century from now());> date_part> -----------> 20> (1 row)> [ ... looks in code ... ]>> Apparently it's because you fixed only timestamp_part, and not> timestamptz_part. I'm not too sure about what timestamp_trunc or> timestamptz_trunc should do, but they may be wrong as well.Sigh... as usual, what is not tested does not work:-(> Could we have a more complete patch?Please find a submission attached. I hope it really fixes all decade,century and millenium issues for extract and *_trunc functions onintervaland other timestamp types. If someone could check that the resultsare reasonnable, it would be great.I indeed overlooked the fact that there were two functions. The patchfixes the code so that both variants agree.I added comments to interval extractions, because it relies on the Cdivision to have a negative remainder: -7/10 = 0 and remains -7.As for *_trunc functions, I have chosen to put the first year of thecentury or millennium: -100, 1, 101... 1001 2001 etc. Indeed, I don'tthink it would make sense to put 2000 (last year of the 2nd millennium)for rounding all years of the third millenium.I also fixed the code so that all decades last 10 years and decade 199means the 1990's.I have added some tests that are relevant to deal with tricky cases. Theformula may be simplified, but all these cases must pass. Please keepthem.Fabien Coelho
1 parent2674bbb commit46be0c1

File tree

3 files changed

+256
-12
lines changed

3 files changed

+256
-12
lines changed

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

Lines changed: 73 additions & 12 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.109 2004/06/03 17:57:09 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.110 2004/08/20 03:45:13 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2727,11 +2727,26 @@ timestamp_trunc(PG_FUNCTION_ARGS)
27272727
fsec=0;
27282728
break;
27292729
caseDTK_MILLENNIUM:
2730-
tm->tm_year= (tm->tm_year /1000)*1000;
2730+
/* see comments in timestamptz_trunc */
2731+
if (tm->tm_year>0)
2732+
tm->tm_year= ((tm->tm_year+999) /1000)*1000-999;
2733+
else
2734+
tm->tm_year=- ((999- (tm->tm_year-1))/1000)*1000+1;
27312735
caseDTK_CENTURY:
2732-
tm->tm_year= (tm->tm_year /100)*100;
2736+
/* see comments in timestamptz_trunc */
2737+
if (tm->tm_year>0)
2738+
tm->tm_year= ((tm->tm_year+99) /100)*100-99;
2739+
else
2740+
tm->tm_year=- ((99- (tm->tm_year-1))/100)*100+1;
27332741
caseDTK_DECADE:
2734-
tm->tm_year= (tm->tm_year /10)*10;
2742+
/* see comments in timestamptz_trunc */
2743+
if (val!=DTK_MILLENNIUM&&val!=DTK_CENTURY)
2744+
{
2745+
if (tm->tm_year>0)
2746+
tm->tm_year= (tm->tm_year /10)*10;
2747+
else
2748+
tm->tm_year=- ((8-(tm->tm_year-1)) /10)*10;
2749+
}
27352750
caseDTK_YEAR:
27362751
tm->tm_mon=1;
27372752
caseDTK_QUARTER:
@@ -2830,12 +2845,33 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
28302845
tm->tm_sec=0;
28312846
fsec=0;
28322847
break;
2848+
/* one may consider DTK_THOUSAND and DTK_HUNDRED... */
28332849
caseDTK_MILLENNIUM:
2834-
tm->tm_year= (tm->tm_year /1000)*1000;
2850+
/* truncating to the millennium? what is this supposed to mean?
2851+
* let us put the first year of the millennium...
2852+
* i.e. -1000, 1, 1001, 2001...
2853+
*/
2854+
if (tm->tm_year>0)
2855+
tm->tm_year= ((tm->tm_year+999) /1000)*1000-999;
2856+
else
2857+
tm->tm_year=- ((999- (tm->tm_year-1))/1000)*1000+1;
28352858
caseDTK_CENTURY:
2836-
tm->tm_year= (tm->tm_year /100)*100;
2859+
/* truncating to the century? as above: -100, 1, 101... */
2860+
if (tm->tm_year>0)
2861+
tm->tm_year= ((tm->tm_year+99) /100)*100-99 ;
2862+
else
2863+
tm->tm_year=- ((99- (tm->tm_year-1))/100)*100+1;
28372864
caseDTK_DECADE:
2838-
tm->tm_year= (tm->tm_year /10)*10;
2865+
/* truncating to the decade? first year of the decade.
2866+
* must not be applied if year was truncated before!
2867+
*/
2868+
if (val!=DTK_MILLENNIUM&&val!=DTK_CENTURY)
2869+
{
2870+
if (tm->tm_year>0)
2871+
tm->tm_year= (tm->tm_year /10)*10;
2872+
else
2873+
tm->tm_year=- ((8-(tm->tm_year-1)) /10)*10;
2874+
}
28392875
caseDTK_YEAR:
28402876
tm->tm_mon=1;
28412877
caseDTK_QUARTER:
@@ -2923,10 +2959,13 @@ interval_trunc(PG_FUNCTION_ARGS)
29232959
switch (val)
29242960
{
29252961
caseDTK_MILLENNIUM:
2962+
/* caution: C division may have negative remainder */
29262963
tm->tm_year= (tm->tm_year /1000)*1000;
29272964
caseDTK_CENTURY:
2965+
/* caution: C division may have negative remainder */
29282966
tm->tm_year= (tm->tm_year /100)*100;
29292967
caseDTK_DECADE:
2968+
/* caution: C division may have negative remainder */
29302969
tm->tm_year= (tm->tm_year /10)*10;
29312970
caseDTK_YEAR:
29322971
tm->tm_mon=0;
@@ -3221,7 +3260,14 @@ timestamp_part(PG_FUNCTION_ARGS)
32213260
break;
32223261

32233262
caseDTK_DECADE:
3224-
result= (tm->tm_year /10);
3263+
/* what is a decade wrt dates?
3264+
* let us assume that decade 199 is 1990 thru 1999...
3265+
* decade 0 starts on year 1 BC, and -1 is 11 BC thru 2 BC...
3266+
*/
3267+
if (tm->tm_year>=0)
3268+
result= (tm->tm_year /10);
3269+
else
3270+
result=-((8-(tm->tm_year-1)) /10);
32253271
break;
32263272

32273273
caseDTK_CENTURY:
@@ -3232,7 +3278,7 @@ timestamp_part(PG_FUNCTION_ARGS)
32323278
if (tm->tm_year>0)
32333279
result= ((tm->tm_year+99) /100);
32343280
else
3235-
/* caution: C division mayyave negative remainder */
3281+
/* caution: C division mayhave negative remainder */
32363282
result=- ((99- (tm->tm_year-1))/100);
32373283
break;
32383284

@@ -3445,15 +3491,27 @@ timestamptz_part(PG_FUNCTION_ARGS)
34453491
break;
34463492

34473493
caseDTK_DECADE:
3448-
result= (tm->tm_year /10);
3494+
/* see comments in timestamp_part */
3495+
if (tm->tm_year>0)
3496+
result= (tm->tm_year /10);
3497+
else
3498+
result=- ((8-(tm->tm_year-1)) /10);
34493499
break;
34503500

34513501
caseDTK_CENTURY:
3452-
result= (tm->tm_year /100);
3502+
/* see comments in timestamp_part */
3503+
if (tm->tm_year>0)
3504+
result= ((tm->tm_year+99) /100);
3505+
else
3506+
result=- ((99- (tm->tm_year-1))/100);
34533507
break;
34543508

34553509
caseDTK_MILLENNIUM:
3456-
result= (tm->tm_year /1000);
3510+
/* see comments in timestamp_part */
3511+
if (tm->tm_year>0)
3512+
result= ((tm->tm_year+999) /1000);
3513+
else
3514+
result=- ((999- (tm->tm_year-1))/1000);
34573515
break;
34583516

34593517
caseDTK_JULIAN:
@@ -3606,14 +3664,17 @@ interval_part(PG_FUNCTION_ARGS)
36063664
break;
36073665

36083666
caseDTK_DECADE:
3667+
/* caution: C division may have negative remainder */
36093668
result= (tm->tm_year /10);
36103669
break;
36113670

36123671
caseDTK_CENTURY:
3672+
/* caution: C division may have negative remainder */
36133673
result= (tm->tm_year /100);
36143674
break;
36153675

36163676
caseDTK_MILLENNIUM:
3677+
/* caution: C division may have negative remainder */
36173678
result= (tm->tm_year /1000);
36183679
break;
36193680

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

Lines changed: 149 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -930,3 +930,152 @@ SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3
930930
3
931931
(1 row)
932932

933+
--
934+
-- decade
935+
--
936+
SELECT EXTRACT(DECADE FROM DATE '1994-12-25'); -- 199
937+
date_part
938+
-----------
939+
199
940+
(1 row)
941+
942+
SELECT EXTRACT(DECADE FROM DATE '0010-01-01'); -- 1
943+
date_part
944+
-----------
945+
1
946+
(1 row)
947+
948+
SELECT EXTRACT(DECADE FROM DATE '0009-12-31'); -- 0
949+
date_part
950+
-----------
951+
0
952+
(1 row)
953+
954+
SELECT EXTRACT(DECADE FROM DATE '0001-01-01 BC'); -- 0
955+
date_part
956+
-----------
957+
0
958+
(1 row)
959+
960+
SELECT EXTRACT(DECADE FROM DATE '0002-12-31 BC'); -- -1
961+
date_part
962+
-----------
963+
-1
964+
(1 row)
965+
966+
SELECT EXTRACT(DECADE FROM DATE '0011-01-01 BC'); -- -1
967+
date_part
968+
-----------
969+
-1
970+
(1 row)
971+
972+
SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); -- -2
973+
date_part
974+
-----------
975+
-2
976+
(1 row)
977+
978+
--
979+
-- some other types:
980+
--
981+
-- on a timestamp.
982+
SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true
983+
true
984+
------
985+
t
986+
(1 row)
987+
988+
SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
989+
date_part
990+
-----------
991+
20
992+
(1 row)
993+
994+
-- on an interval
995+
SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1
996+
date_part
997+
-----------
998+
1
999+
(1 row)
1000+
1001+
SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0
1002+
date_part
1003+
-----------
1004+
0
1005+
(1 row)
1006+
1007+
SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0
1008+
date_part
1009+
-----------
1010+
0
1011+
(1 row)
1012+
1013+
SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1
1014+
date_part
1015+
-----------
1016+
-1
1017+
(1 row)
1018+
1019+
--
1020+
-- test trunc function!
1021+
--
1022+
SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001
1023+
date_trunc
1024+
--------------------------
1025+
Thu Jan 01 00:00:00 1001
1026+
(1 row)
1027+
1028+
SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20'); -- 1001-01-01
1029+
date_trunc
1030+
------------------------------
1031+
Thu Jan 01 00:00:00 1001 PST
1032+
(1 row)
1033+
1034+
SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1901
1035+
date_trunc
1036+
--------------------------
1037+
Tue Jan 01 00:00:00 1901
1038+
(1 row)
1039+
1040+
SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20'); -- 1901
1041+
date_trunc
1042+
------------------------------
1043+
Tue Jan 01 00:00:00 1901 PST
1044+
(1 row)
1045+
1046+
SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10'); -- 2001-01-01
1047+
date_trunc
1048+
------------------------------
1049+
Mon Jan 01 00:00:00 2001 PST
1050+
(1 row)
1051+
1052+
SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04'); -- 0001-01-01
1053+
date_trunc
1054+
------------------------------
1055+
Mon Jan 01 00:00:00 0001 PST
1056+
(1 row)
1057+
1058+
SELECT DATE_TRUNC('CENTURY', DATE '0055-08-10 BC'); -- 0100-01-01 BC
1059+
date_trunc
1060+
---------------------------------
1061+
Tue Jan 01 00:00:00 0100 PST BC
1062+
(1 row)
1063+
1064+
SELECT DATE_TRUNC('DECADE', DATE '1993-12-25'); -- 1990-01-01
1065+
date_trunc
1066+
------------------------------
1067+
Mon Jan 01 00:00:00 1990 PST
1068+
(1 row)
1069+
1070+
SELECT DATE_TRUNC('DECADE', DATE '0004-12-25'); -- 0001-01-01 BC
1071+
date_trunc
1072+
---------------------------------
1073+
Sat Jan 01 00:00:00 0001 PST BC
1074+
(1 row)
1075+
1076+
SELECT DATE_TRUNC('DECADE', DATE '0002-12-31 BC'); -- 0011-01-01 BC
1077+
date_trunc
1078+
---------------------------------
1079+
Mon Jan 01 00:00:00 0011 PST BC
1080+
(1 row)
1081+

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -235,3 +235,37 @@ SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2
235235
SELECT EXTRACT(MILLENNIUMFROMDATE'2001-01-01');-- 3
236236
-- next test to be fixed on the turn of the next millennium;-)
237237
SELECT EXTRACT(MILLENNIUMFROMCURRENT_DATE);-- 3
238+
--
239+
-- decade
240+
--
241+
SELECT EXTRACT(DECADEFROMDATE'1994-12-25');-- 199
242+
SELECT EXTRACT(DECADEFROMDATE'0010-01-01');-- 1
243+
SELECT EXTRACT(DECADEFROMDATE'0009-12-31');-- 0
244+
SELECT EXTRACT(DECADEFROMDATE'0001-01-01 BC');-- 0
245+
SELECT EXTRACT(DECADEFROMDATE'0002-12-31 BC');-- -1
246+
SELECT EXTRACT(DECADEFROMDATE'0011-01-01 BC');-- -1
247+
SELECT EXTRACT(DECADEFROMDATE'0012-12-31 BC');-- -2
248+
--
249+
-- some other types:
250+
--
251+
-- on a timestamp.
252+
SELECT EXTRACT(CENTURYFROM NOW())>=21AS True;-- true
253+
SELECT EXTRACT(CENTURYFROMTIMESTAMP'1970-03-20 04:30:00.00000');-- 20
254+
-- on an interval
255+
SELECT EXTRACT(CENTURYFROM INTERVAL'100 y');-- 1
256+
SELECT EXTRACT(CENTURYFROM INTERVAL'99 y');-- 0
257+
SELECT EXTRACT(CENTURYFROM INTERVAL'-99 y');-- 0
258+
SELECT EXTRACT(CENTURYFROM INTERVAL'-100 y');-- -1
259+
--
260+
-- test trunc function!
261+
--
262+
SELECT DATE_TRUNC('MILLENNIUM',TIMESTAMP'1970-03-20 04:30:00.00000');-- 1001
263+
SELECT DATE_TRUNC('MILLENNIUM',DATE'1970-03-20');-- 1001-01-01
264+
SELECT DATE_TRUNC('CENTURY',TIMESTAMP'1970-03-20 04:30:00.00000');-- 1901
265+
SELECT DATE_TRUNC('CENTURY',DATE'1970-03-20');-- 1901
266+
SELECT DATE_TRUNC('CENTURY',DATE'2004-08-10');-- 2001-01-01
267+
SELECT DATE_TRUNC('CENTURY',DATE'0002-02-04');-- 0001-01-01
268+
SELECT DATE_TRUNC('CENTURY',DATE'0055-08-10 BC');-- 0100-01-01 BC
269+
SELECT DATE_TRUNC('DECADE',DATE'1993-12-25');-- 1990-01-01
270+
SELECT DATE_TRUNC('DECADE',DATE'0004-12-25');-- 0001-01-01 BC
271+
SELECT DATE_TRUNC('DECADE',DATE'0002-12-31 BC');-- 0011-01-01 BC

0 commit comments

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