[HACKERS] date_trunc problem in HEAD
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 date_trunc.patch Description: Binary data pgpMzsnWN5kpL.pgp Description: PGP signature
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] date_trunc problem in HEAD
When grilled further on (Sun, 13 Mar 2005 19:40:02 +0100), Kurt Roeckx [EMAIL PROTECTED] 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 pgpsLcfsPEZ1Y.pgp Description: PGP signature
Re: [HACKERS] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 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 pgp7qKDfwTHuS.pgp Description: PGP signature
