diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/utils/adt/timestamp.c | 18 | ||||
| -rw-r--r-- | src/test/regress/expected/date.out | 111 | ||||
| -rw-r--r-- | src/test/regress/sql/date.sql | 27 |
3 files changed, 153 insertions, 3 deletions
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index dafc8ae5bed..b2628a3a6f6 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.103 2004/03/30 15:53:18 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.104 2004/04/10 18:02:59 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -3273,11 +3273,23 @@ timestamp_part(PG_FUNCTION_ARGS) break; case DTK_CENTURY: - result = (tm->tm_year / 100); + /* centuries AD, c>0: year in [ (c-1)*100+1 : c*100 ] + * centuries BC, c<0: year in [ c*100 : (c+1)*100-1 ] + * there is no number 0 century. + */ + if (tm->tm_year > 0) + result = ((tm->tm_year+99) / 100); + else + /* caution: C division may yave negative remainder */ + result = - ((99 - (tm->tm_year-1))/100); break; case DTK_MILLENNIUM: - result = (tm->tm_year / 1000); + /* see comments above. */ + if (tm->tm_year > 0) + result = ((tm->tm_year+999) / 1000); + else + result = - ((999 - (tm->tm_year-1))/1000); break; case DTK_JULIAN: diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out index 20ffaf188de..77bbdb96762 100644 --- a/src/test/regress/expected/date.out +++ b/src/test/regress/expected/date.out @@ -819,3 +819,114 @@ SELECT date 'tomorrow' - date 'yesterday' AS "Two days"; 2 (1 row) +-- +-- test extract! +-- +-- century +-- +SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2 + date_part +----------- + -2 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '0100-12-31 BC'); -- -1 + date_part +----------- + -1 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); -- -1 + date_part +----------- + -1 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19 + date_part +----------- + 19 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20 + date_part +----------- + 20 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20 + date_part +----------- + 20 +(1 row) + +SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21 + date_part +----------- + 21 +(1 row) + +SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true + true +------ + t +(1 row) + +-- +-- millennium +-- +SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1 + date_part +----------- + -1 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1 + date_part +----------- + 1 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2 + date_part +----------- + 2 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2 + date_part +----------- + 2 +(1 row) + +SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3 + date_part +----------- + 3 +(1 row) + +-- next test to be fixed on the turn of the next millennium;-) +SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3 + date_part +----------- + 3 +(1 row) + diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql index 10bd87cadcf..a3cad66ed2e 100644 --- a/src/test/regress/sql/date.sql +++ b/src/test/regress/sql/date.sql @@ -208,3 +208,30 @@ SELECT date 'tomorrow' - date 'today' AS "One day"; SELECT date 'today' - date 'yesterday' AS "One day"; SELECT date 'tomorrow' - date 'yesterday' AS "Two days"; + +-- +-- test extract! +-- +-- century +-- +SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2 +SELECT EXTRACT(CENTURY FROM DATE '0100-12-31 BC'); -- -1 +SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); -- -1 +SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1 +SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1 +SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19 +SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20 +SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20 +SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21 +SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true +-- +-- millennium +-- +SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1 +SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1 +SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1 +SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2 +SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2 +SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3 +-- next test to be fixed on the turn of the next millennium;-) +SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3 |