date_trunc problem in HEAD

Started by Robert Creageralmost 21 years ago5 messages

Robert Creager

Robert_Creager@LogicalChaos.org

almost 21 years ago

1 attachment(s)

Hey All,

I goofed with the patch I submitted last year for adding 'week' capability to
the date_trunc function.

Attached is a patch against HEAD for your review.

Cheers,
Rob

--
11:00:49 up 47 days, 16:17, 4 users, load average: 3.01, 2.37, 2.37
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004

Attachments:

date_trunc.patchapplication/octet-stream; name=date_trunc.patchDownload

Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.117
diff -c -r1.117 timestamp.c
*** src/backend/utils/adt/timestamp.c	31 Dec 2004 22:01:22 -0000	1.117
--- src/backend/utils/adt/timestamp.c	13 Mar 2005 17:58:18 -0000
***************
*** 2729,2735 ****
  	Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
  	Timestamp	result;
  	int			type,
! 				val;
  	char	   *lowunits;
  	fsec_t		fsec;
  	struct pg_tm tt,
--- 2729,2736 ----
  	Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
  	Timestamp	result;
  	int			type,
! 				val,
! 				year;
  	char	   *lowunits;
  	fsec_t		fsec;
  	struct pg_tm tt,
***************
*** 2754,2760 ****
--- 2755,2769 ----
  		switch (val)
  		{
  			case DTK_WEEK:
+ 				year = tm->tm_year;
  				isoweek2date(date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday), &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
+ 				/* the new year cannot be greater than the
+ 				 * original year, so we subtract one if it is
+ 				 */
+ 				if ( tm->tm_year > year )
+ 					{
+ 					--tm->tm_year;
+ 					}
  				tm->tm_hour = 0;
  				tm->tm_min = 0;
  				tm->tm_sec = 0;
***************
*** 2847,2853 ****
  	TimestampTz result;
  	int			tz;
  	int			type,
! 				val;
  	bool		redotz = false;
  	char	   *lowunits;
  	fsec_t		fsec;
--- 2856,2863 ----
  	TimestampTz result;
  	int			tz;
  	int			type,
! 				val,
! 				year;
  	bool		redotz = false;
  	char	   *lowunits;
  	fsec_t		fsec;
***************
*** 2874,2880 ****
--- 2884,2898 ----
  		switch (val)
  		{
  			case DTK_WEEK:
+ 				year = tm->tm_year;
  				isoweek2date(date2isoweek(tm->tm_year, tm->tm_mon, tm->tm_mday), &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
+ 				/* the new year cannot be greater than the
+ 				 * original year, so we subtract one if it is
+ 				 */
+ 				if ( tm->tm_year > year )
+ 					{
+ 					--tm->tm_year;
+ 					}
  				tm->tm_hour = 0;
  				tm->tm_min = 0;
  				tm->tm_sec = 0;
Index: src/test/regress/expected/timestamp.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/timestamp.out,v
retrieving revision 1.27
diff -c -r1.27 timestamp.out
*** src/test/regress/expected/timestamp.out	3 Jun 2004 02:08:06 -0000	1.27
--- src/test/regress/expected/timestamp.out	13 Mar 2005 17:58:18 -0000
***************
*** 499,508 ****
      | @ 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
--- 499,508 ----
      | @ 1460 days 17 hours 32 mins 1 sec
  (54 rows)
  
! SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2005-01-01 15:44:17.71393' ) AS week_trunc;
   date_trunc_week |        week_trunc        
  -----------------+--------------------------
!                  | Sun Jan 02 00:00:00 2005
  (1 row)
  
  -- Test casting within a BETWEEN qualifier
Index: src/test/regress/expected/timestamptz.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/timestamptz.out,v
retrieving revision 1.17
diff -c -r1.17 timestamptz.out
*** src/test/regress/expected/timestamptz.out	11 Jul 2004 04:57:20 -0000	1.17
--- src/test/regress/expected/timestamptz.out	13 Mar 2005 17:58:19 -0000
***************
*** 494,503 ****
      | @ 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
--- 494,503 ----
      | @ 1460 days 17 hours 32 mins 1 sec
  (54 rows)
  
! SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp with time zone '2005-01-01 15:44:17.71393' ) AS week_trunc;
   date_trunc_week |          week_trunc          
  -----------------+------------------------------
!                  | Sun Jan 02 00:00:00 2005 PST
  (1 row)
  
  -- Test casting within a BETWEEN qualifier
Index: src/test/regress/sql/timestamp.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/timestamp.sql,v
retrieving revision 1.13
diff -c -r1.13 timestamp.sql
*** src/test/regress/sql/timestamp.sql	5 Mar 2004 02:41:14 -0000	1.13
--- src/test/regress/sql/timestamp.sql	13 Mar 2005 17:58:19 -0000
***************
*** 151,157 ****
  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
--- 151,157 ----
  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 '2005-01-01 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
Index: src/test/regress/sql/timestamptz.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/timestamptz.sql,v
retrieving revision 1.6
diff -c -r1.6 timestamptz.sql
*** src/test/regress/sql/timestamptz.sql	5 Mar 2004 02:41:14 -0000	1.6
--- src/test/regress/sql/timestamptz.sql	13 Mar 2005 17:58:19 -0000
***************
*** 145,151 ****
  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
--- 145,151 ----
  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 '2005-01-01 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

Kurt Roeckx

kurt@roeckx.be

almost 21 years ago

In reply to: Robert Creager (#1)

Re: date_trunc problem in HEAD

On Sun, Mar 13, 2005 at 11:12:32AM -0700, Robert Creager wrote:

Hey All,

I goofed with the patch I submitted last year for adding 'week' capability to
the date_trunc function.

Attached is a patch against HEAD for your review.

It has this comment in it:
/* the new year cannot be greater than the
* original year, so we subtract one if it is

Can you please explain that?

The "iso" year can be both greater and smaller than the current
year.

Kurt

Robert Creager

Robert_Creager@LogicalChaos.org

almost 21 years ago

In reply to: Kurt Roeckx (#2)

Re: date_trunc problem in HEAD

When grilled further on (Sun, 13 Mar 2005 19:40:02 +0100),
Kurt Roeckx <kurt@roeckx.be> confessed:

Attached is a patch against HEAD for your review.

It has this comment in it:
/* the new year cannot be greater than the
* original year, so we subtract one if it is

Not doing to well here. When will the ISO year be greater than the current
year? But, what I did is incorrect and 2006-01-01 shows the next problem date:

SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2006-01-01' ) AS
week_trunc;

date_trunc_week | week_trunc
-----------------+---------------------
| 2006-12-25 00:00:00

Heck, even what I submitted, test and all is wrong:

SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2005-01-01' ) AS
week_trunc;
date_trunc_week | week_trunc
-----------------+---------------------
| 2005-01-02 00:00:00

The date should be 2005-01-03. Sigh. Maybe I should of just submitted a bug
report about it...

So, unless someone else knows how to do this correctly, I'll have to actually
think about it.

Cheers,
Rob

--
12:34:02 up 47 days, 17:50, 4 users, load average: 2.34, 2.60, 2.55
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004

Kurt Roeckx

kurt@roeckx.be

almost 21 years ago

In reply to: Robert Creager (#3)

Re: date_trunc problem in HEAD

On Sun, Mar 13, 2005 at 12:48:00PM -0700, Robert Creager wrote:

When grilled further on (Sun, 13 Mar 2005 19:40:02 +0100),
Kurt Roeckx <kurt@roeckx.be> confessed:

Attached is a patch against HEAD for your review.

It has this comment in it:
/* the new year cannot be greater than the
* original year, so we subtract one if it is

Not doing to well here. When will the ISO year be greater than the current
year? But, what I did is incorrect and 2006-01-01 shows the next problem date:

The iso year can be greater than the current year at the end of
the year and smaller on the start of the year. You have either
of those at every year change.

SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2006-01-01' ) AS
week_trunc;

date_trunc_week | week_trunc
-----------------+---------------------
| 2006-12-25 00:00:00

I expected 2005-12-26 here.

SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2005-01-01' ) AS
week_trunc;
date_trunc_week | week_trunc
-----------------+---------------------
| 2005-01-02 00:00:00

That's a higher date, and obviouly looks wrong. Here I expected
2004-12-27

The date should be 2005-01-03. Sigh. Maybe I should of just submitted a bug
report about it...

That's the next week, and not what I would expect to get as
result.

Kurt

Robert Creager

Robert_Creager@LogicalChaos.org

almost 21 years ago

In reply to: Kurt Roeckx (#2)

Re: date_trunc problem in HEAD

OK. I believe the following function provides the correct functionality.
Agree/disagree? If it's good, I'll figure out how to convert this little
monster to C...

CREATE OR REPLACE FUNCTION
date_trunc_week(timestamp without time zone)
RETURNS timestamp without time zone
AS '
DECLARE
reading_time ALIAS FOR $1;
year timestamp;
dow integer;
temp interval;
weeks text;
adjust text;
BEGIN
year := date_trunc( ''year''::text, reading_time );
dow := date_part( ''dow'', year );
IF dow >= 4 THEN
adjust := 1 - dow || '' day'';
ELSIF dow != 1 THEN
adjust := dow - 6 || '' day'';
ELSE
adjust := ''0 day'';
END IF;
temp := reading_time - (year + adjust::interval);
weeks := trunc(date_part( ''days'', temp ) / 7) || '' weeks'';
RETURN year + adjust::interval + weeks::interval;
END;
' LANGUAGE plpgsql;

select date_trunc_week( '2004-01-01' ); -- 2003-12-29 00:00:00
select date_trunc_week( '2005-01-01' ); -- 2004-12-27 00:00:00
select date_trunc_week( '2005-06-01' ); -- 2005-05-30 00:00:00
select date_trunc_week( '2006-01-01' ); -- 2005-12-26 00:00:00
select date_trunc_week( '2007-01-01' ); -- 2007-01-01 00:00:00

Thanks for your input on this Kurt.

Cheers,
Rob

--
21:48:49 up 48 days, 3:05, 4 users, load average: 3.80, 3.13, 2.82
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004