From 1973971821672447d30bda6f12736a7e5987569f Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Fri, 5 Mar 2004 02:41:14 +0000 Subject: [PATCH] Per a brief conversation with Tom, I've created a patch for adding support for 'week' within the date_trunc function. Within the patch I added a couple of test cases and associated target output, and changed the documentation to add 'week' appropriately. Robert Creager --- doc/src/sgml/func.sgml | 3 ++- src/backend/utils/adt/timestamp.c | 16 +++++++++++++++- src/test/regress/expected/timestamp.out | 6 ++++++ src/test/regress/expected/timestamptz.out | 10 ++++++++-- src/test/regress/sql/timestamp.sql | 2 ++ src/test/regress/sql/timestamptz.sql | 2 ++ 6 files changed, 35 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 66dae9bbdd..6c3856d26f 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -5309,6 +5309,7 @@ date_trunc('field', source minute hour day + week month year decade diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index ad79025ebc..4fb5f743b0 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.99 2004/02/14 20:16:17 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.100 2004/03/05 02:41:14 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -2564,6 +2564,13 @@ timestamp_trunc(PG_FUNCTION_ARGS) switch (val) { + case DTK_WEEK: + isoweek2date( date2isoweek( tm->tm_year, tm->tm_mon, tm->tm_mday ), &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday) ); + tm->tm_hour = 0; + tm->tm_min = 0; + tm->tm_sec = 0; + fsec = 0; + break; case DTK_MILLENNIUM: tm->tm_year = (tm->tm_year / 1000) * 1000; case DTK_CENTURY: @@ -2672,6 +2679,13 @@ timestamptz_trunc(PG_FUNCTION_ARGS) switch (val) { + case DTK_WEEK: + isoweek2date( date2isoweek( tm->tm_year, tm->tm_mon, tm->tm_mday ), &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday) ); + tm->tm_hour = 0; + tm->tm_min = 0; + tm->tm_sec = 0; + fsec = 0; + break; case DTK_MILLENNIUM: tm->tm_year = (tm->tm_year / 1000) * 1000; case DTK_CENTURY: diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index f037d576ec..7557bf2900 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -499,6 +499,12 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff | @ 1460 days 17 hours 32 mins 1 sec (54 rows) +SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc; + date_trunc_week | week_trunc +-----------------+-------------------------- + | Mon Feb 23 00:00:00 2004 +(1 row) + -- Test casting within a BETWEEN qualifier SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff FROM TIMESTAMP_TBL diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index a76e09a0f5..8a9bccf0a3 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -494,6 +494,12 @@ SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff | @ 1460 days 17 hours 32 mins 1 sec (54 rows) +SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc; + date_trunc_week | week_trunc +-----------------+------------------------------ + | Mon Feb 23 00:00:00 2004 PST +(1 row) + -- Test casting within a BETWEEN qualifier SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff FROM TIMESTAMPTZ_TBL @@ -1317,9 +1323,9 @@ SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. H | 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm (64 rows) -SELECT '' AS to_char_10, to_char(d1, 'YYYY WW IYYY IYY IY I IW') +SELECT '' AS to_char_10, to_char(d1, 'YYYY WW IYYY IYY IY I IW') FROM TIMESTAMPTZ_TBL; - to_char_10 | to_char + to_char_10 | to_char ------------+-------------------------- | | diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index 48a9151a70..ceda76f546 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -151,6 +151,8 @@ SELECT '' AS "49", d1 FROM TIMESTAMP_TBL SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; +SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc; + -- Test casting within a BETWEEN qualifier SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff FROM TIMESTAMP_TBL diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index 2a789b87bd..12572fa7ce 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -145,6 +145,8 @@ SELECT '' AS "49", d1 FROM TIMESTAMPTZ_TBL SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; +SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc; + -- Test casting within a BETWEEN qualifier SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff FROM TIMESTAMPTZ_TBL -- 2.40.0