aboutsummaryrefslogtreecommitdiff
path: root/src/backend/utils/adt/timestamp.c
diff options
context:
space:
mode:
authorBruce Momjian <bruce@momjian.us>2004-08-20 03:45:14 +0000
committerBruce Momjian <bruce@momjian.us>2004-08-20 03:45:14 +0000
commit46be0c18f120466da9a9765d4ac15fdeaab4b0c7 (patch)
tree8b5e9cda6da45e94637c09e2395d926ddcc8c541 /src/backend/utils/adt/timestamp.c
parent2674bbbe9960f3fb73ecfe771f3cdffe7dc8f1d4 (diff)
downloadpostgresql-46be0c18f120466da9a9765d4ac15fdeaab4b0c7.tar.gz
postgresql-46be0c18f120466da9a9765d4ac15fdeaab4b0c7.zip
> 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 on interval and other timestamp types. If someone could check that the results are reasonnable, it would be great. I indeed overlooked the fact that there were two functions. The patch fixes the code so that both variants agree. I added comments to interval extractions, because it relies on the C division to have a negative remainder: -7/10 = 0 and remains -7. As for *_trunc functions, I have chosen to put the first year of the century or millennium: -100, 1, 101... 1001 2001 etc. Indeed, I don't think 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 199 means the 1990's. I have added some tests that are relevant to deal with tricky cases. The formula may be simplified, but all these cases must pass. Please keep them. Fabien Coelho
Diffstat (limited to 'src/backend/utils/adt/timestamp.c')
-rw-r--r--src/backend/utils/adt/timestamp.c85
1 files changed, 73 insertions, 12 deletions
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 3f6d4315230..a38bbda9af1 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.109 2004/06/03 17:57:09 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.110 2004/08/20 03:45:13 momjian Exp $
*
*-------------------------------------------------------------------------
*/
@@ -2727,11 +2727,26 @@ timestamp_trunc(PG_FUNCTION_ARGS)
fsec = 0;
break;
case DTK_MILLENNIUM:
- tm->tm_year = (tm->tm_year / 1000) * 1000;
+ /* see comments in timestamptz_trunc */
+ if (tm->tm_year > 0)
+ tm->tm_year = ((tm->tm_year+999) / 1000) * 1000 - 999;
+ else
+ tm->tm_year = - ((999 - (tm->tm_year-1))/1000) * 1000 + 1;
case DTK_CENTURY:
- tm->tm_year = (tm->tm_year / 100) * 100;
+ /* see comments in timestamptz_trunc */
+ if (tm->tm_year > 0)
+ tm->tm_year = ((tm->tm_year+99) / 100) * 100 - 99;
+ else
+ tm->tm_year = - ((99 - (tm->tm_year-1))/100) * 100 + 1;
case DTK_DECADE:
- tm->tm_year = (tm->tm_year / 10) * 10;
+ /* see comments in timestamptz_trunc */
+ if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
+ {
+ if (tm->tm_year > 0)
+ tm->tm_year = (tm->tm_year / 10) * 10;
+ else
+ tm->tm_year = - ((8-(tm->tm_year-1)) / 10) * 10;
+ }
case DTK_YEAR:
tm->tm_mon = 1;
case DTK_QUARTER:
@@ -2830,12 +2845,33 @@ timestamptz_trunc(PG_FUNCTION_ARGS)
tm->tm_sec = 0;
fsec = 0;
break;
+ /* one may consider DTK_THOUSAND and DTK_HUNDRED... */
case DTK_MILLENNIUM:
- tm->tm_year = (tm->tm_year / 1000) * 1000;
+ /* truncating to the millennium? what is this supposed to mean?
+ * let us put the first year of the millennium...
+ * i.e. -1000, 1, 1001, 2001...
+ */
+ if (tm->tm_year > 0)
+ tm->tm_year = ((tm->tm_year+999) / 1000) * 1000 - 999;
+ else
+ tm->tm_year = - ((999 - (tm->tm_year-1))/1000) * 1000 + 1;
case DTK_CENTURY:
- tm->tm_year = (tm->tm_year / 100) * 100;
+ /* truncating to the century? as above: -100, 1, 101... */
+ if (tm->tm_year > 0)
+ tm->tm_year = ((tm->tm_year+99) / 100) * 100 - 99 ;
+ else
+ tm->tm_year = - ((99 - (tm->tm_year-1))/100) * 100 + 1;
case DTK_DECADE:
- tm->tm_year = (tm->tm_year / 10) * 10;
+ /* truncating to the decade? first year of the decade.
+ * must not be applied if year was truncated before!
+ */
+ if (val != DTK_MILLENNIUM && val != DTK_CENTURY)
+ {
+ if (tm->tm_year > 0)
+ tm->tm_year = (tm->tm_year / 10) * 10;
+ else
+ tm->tm_year = - ((8-(tm->tm_year-1)) / 10) * 10;
+ }
case DTK_YEAR:
tm->tm_mon = 1;
case DTK_QUARTER:
@@ -2923,10 +2959,13 @@ interval_trunc(PG_FUNCTION_ARGS)
switch (val)
{
case DTK_MILLENNIUM:
+ /* caution: C division may have negative remainder */
tm->tm_year = (tm->tm_year / 1000) * 1000;
case DTK_CENTURY:
+ /* caution: C division may have negative remainder */
tm->tm_year = (tm->tm_year / 100) * 100;
case DTK_DECADE:
+ /* caution: C division may have negative remainder */
tm->tm_year = (tm->tm_year / 10) * 10;
case DTK_YEAR:
tm->tm_mon = 0;
@@ -3221,7 +3260,14 @@ timestamp_part(PG_FUNCTION_ARGS)
break;
case DTK_DECADE:
- result = (tm->tm_year / 10);
+ /* what is a decade wrt dates?
+ * let us assume that decade 199 is 1990 thru 1999...
+ * decade 0 starts on year 1 BC, and -1 is 11 BC thru 2 BC...
+ */
+ if (tm->tm_year>=0)
+ result = (tm->tm_year / 10);
+ else
+ result = -((8-(tm->tm_year-1)) / 10);
break;
case DTK_CENTURY:
@@ -3232,7 +3278,7 @@ timestamp_part(PG_FUNCTION_ARGS)
if (tm->tm_year > 0)
result = ((tm->tm_year+99) / 100);
else
- /* caution: C division may yave negative remainder */
+ /* caution: C division may have negative remainder */
result = - ((99 - (tm->tm_year-1))/100);
break;
@@ -3445,15 +3491,27 @@ timestamptz_part(PG_FUNCTION_ARGS)
break;
case DTK_DECADE:
- result = (tm->tm_year / 10);
+ /* see comments in timestamp_part */
+ if (tm->tm_year>0)
+ result = (tm->tm_year / 10);
+ else
+ result = - ((8-(tm->tm_year-1)) / 10);
break;
case DTK_CENTURY:
- result = (tm->tm_year / 100);
+ /* see comments in timestamp_part */
+ if (tm->tm_year > 0)
+ result = ((tm->tm_year+99) / 100);
+ else
+ result = - ((99 - (tm->tm_year-1))/100);
break;
case DTK_MILLENNIUM:
- result = (tm->tm_year / 1000);
+ /* see comments in timestamp_part */
+ if (tm->tm_year > 0)
+ result = ((tm->tm_year+999) / 1000);
+ else
+ result = - ((999 - (tm->tm_year-1))/1000);
break;
case DTK_JULIAN:
@@ -3606,14 +3664,17 @@ interval_part(PG_FUNCTION_ARGS)
break;
case DTK_DECADE:
+ /* caution: C division may have negative remainder */
result = (tm->tm_year / 10);
break;
case DTK_CENTURY:
+ /* caution: C division may have negative remainder */
result = (tm->tm_year / 100);
break;
case DTK_MILLENNIUM:
+ /* caution: C division may have negative remainder */
result = (tm->tm_year / 1000);
break;