Re: [PATCHES] Interval->day docs and regression tests
Tom Lane wrote: > Michael Glaesemann <[EMAIL PROTECTED]> writes: > > Please find attached diffs for documentation and simple regression > > tests for the new interval->day changes. > > The buildfarm results suggest that justify_days is broken in the > integer-datetimes case, eg from panda: > > *** ./expected/interval.out Sat Jul 30 16:20:48 2005 > --- ./results/interval.out Sat Jul 30 16:24:31 2005 > *** > *** 238,243 > SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 > seconds') as "7 mons 6 days 5 hours 4 mins 3 seconds"; >7 mons 6 days 5 hours 4 mins 3 seconds > > ! @ 7 mons 6 days 5 hours 4 mins 3 secs > (1 row) > > --- 238,243 > SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 > seconds') as "7 mons 6 days 5 hours 4 mins 3 seconds"; >7 mons 6 days 5 hours 4 mins 3 seconds > > ! @ 1 mon 186 days 5 hours 4 mins 3 secs > (1 row) Thanks, fixed and code cleaned up. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/backend/utils/adt/timestamp.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.146 diff -c -c -r1.146 timestamp.c *** src/backend/utils/adt/timestamp.c 24 Jul 2005 04:37:07 - 1.146 --- src/backend/utils/adt/timestamp.c 30 Jul 2005 18:19:14 - *** *** 1915,1922 #ifdef HAVE_INT64_TIMESTAMP result->time += span->day * USECS_PER_DAY; ! result->day = result->time / USECS_PER_DAY; ! result->time -= result->day * USECS_PER_DAY; #else result->time += span->day * (double)SECS_PER_DAY; TMODULO(result->time, result->day, (double)SECS_PER_DAY); --- 1915,1921 #ifdef HAVE_INT64_TIMESTAMP result->time += span->day * USECS_PER_DAY; ! TMODULO(result->time, result->day, USECS_PER_DAY); #else result->time += span->day * (double)SECS_PER_DAY; TMODULO(result->time, result->day, (double)SECS_PER_DAY); *** *** 1939,1952 result->day = span->day; result->time = span->time; ! #ifdef HAVE_INT64_TIMESTAMP ! result->day += span->month * (double)DAYS_PER_MONTH; ! result->month = span->day / DAYS_PER_MONTH; ! result->day -= result->month * DAYS_PER_MONTH; ! #else ! result->day += span->month * (double)DAYS_PER_MONTH; ! TMODULO(result->day, result->month, (double)DAYS_PER_MONTH); ! #endif PG_RETURN_INTERVAL_P(result); } --- 1938,1945 result->day = span->day; result->time = span->time; ! result->day += span->month * DAYS_PER_MONTH; ! TMODULO(result->day, result->month, DAYS_PER_MONTH); PG_RETURN_INTERVAL_P(result); } ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Interval->day docs and regression tests
Patch applied. Thanks. --- Michael Glaesemann wrote: > Please find attached diffs for documentation and simple regression > tests for the new interval->day changes. I added tests for > justify_hours() and justify_days() to interval.sql, as they take > interval input and produce interval output. If there's a more > appropriate place for them, please let me know. > > I've included the diff in the email as well (below) for ease of review. > > Michael Glaesemann > grzm myrealbox com > > Index: doc/src/sgml/func.sgml > === > RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v > retrieving revision 1.269 > diff -c -r1.269 func.sgml > *** doc/src/sgml/func.sgml22 Jul 2005 21:16:14 -1.269 > --- doc/src/sgml/func.sgml26 Jul 2005 00:43:49 - > *** > *** 4903,4908 > --- 4903,4926 > such pair. > > > + > +When adding an interval value to (or subtracting an > +interval value from) a timestamp with time > zone > +value, the days component advances (or decrements) the date of the > +timestamp with time zone by the indicated number of > days. > +Across daylight saving time changes (with the session tiem zone > set to a > +time zone that recognizes DST), this means interval '1 > day' > +does not necessarily equal interval '24 hours'. > +For example, with the session time zone set to CST7CDT literal> > +timestamp with time zone '2005-04-02 12:00-07' + > interval '1 day' > +will produce timestamp with time zone '2005-04-03 > 12:00-06', > +while adding interval '24 hours' to the same > initial > +timestamp with time zone produces > +timestamp with time zone '2005-04-03 13:00-06' literal>, as there is > +a change in daylight saving time at 2005-04-03 02:00 literal> in time zone > +CST7CDT. > + > + > > Date/Time Operators > > Index: src/test/regress/expected/horology.out > === > RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/ > horology.out,v > retrieving revision 1.56 > diff -c -r1.56 horology.out > *** src/test/regress/expected/horology.out27 May 2005 21:31:23 > -1.56 > --- src/test/regress/expected/horology.out26 Jul 2005 00:43:49 - > *** > *** 598,603 > --- 598,630 > t >(1 row) > > + -- timestamp with time zone, interval arithmetic around DST change > + SET TIME ZONE 'CST7CDT'; > + SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 > day' as "Apr 3, 12:00"; > + Apr 3, 12:00 > + -- > + Sun Apr 03 12:00:00 2005 CDT > + (1 row) > + > + SELECT timestamp with time zone '2005-04-02 12:00-07' + interval > '24 hours' as "Apr 3, 13:00"; > + Apr 3, 13:00 > + -- > + Sun Apr 03 13:00:00 2005 CDT > + (1 row) > + > + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 > day' as "Apr 2, 12:00"; > + Apr 2, 12:00 > + -- > + Sat Apr 02 12:00:00 2005 CST > + (1 row) > + > + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval > '24 hours' as "Apr 2, 11:00"; > + Apr 2, 11:00 > + -- > + Sat Apr 02 11:00:00 2005 CST > + (1 row) > + > + RESET TIME ZONE; >SELECT timestamptz(date '1994-01-01', time '11:00') AS > "Jan_01_1994_10am"; > Jan_01_1994_10am >-- > Index: src/test/regress/expected/interval.out > === > RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/ > interval.out,v > retrieving revision 1.12 > diff -c -r1.12 interval.out > *** src/test/regress/expected/interval.out20 Jul 2005 16:42:32 > -1.12 > --- src/test/regress/expected/interval.out26 Jul 2005 00:43:49 - > *** > *** 228,230 > --- 228,243 > @ 4541 years 4 mons 4 days 17 mins 31 secs >(1 row) > > + -- test justify_hours() and justify_days() > + SELECT justify_hours(interval '6 months 3 days 52 hours 3 minutes 2 > seconds') as "6 mons 5 days 4 hours 3 mins 2 seconds"; > + 6 mons 5 days 4 hours 3 mins 2 seconds > + > + @ 6 mons 5 days 4 hours 3 mins 2 secs > + (1 row) > + > + SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 > seconds') as "7 mons 6 days 5 hours 4 mins 3 seconds"; > + 7 mons 6 days 5 hours 4 mins 3 seconds > + > + @ 7 mons 6 days 5 hours 4 mins 3 secs > + (1 row) > + > Index: src/test/regress/sql/horology.sql > === > RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/horology.sql,v > retrievin
[PATCHES] Interval->day docs and regression tests
Please find attached diffs for documentation and simple regression tests for the new interval->day changes. I added tests for justify_hours() and justify_days() to interval.sql, as they take interval input and produce interval output. If there's a more appropriate place for them, please let me know. I've included the diff in the email as well (below) for ease of review. Michael Glaesemann grzm myrealbox com Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.269 diff -c -r1.269 func.sgml *** doc/src/sgml/func.sgml22 Jul 2005 21:16:14 -1.269 --- doc/src/sgml/func.sgml26 Jul 2005 00:43:49 - *** *** 4903,4908 --- 4903,4926 such pair. + +When adding an interval value to (or subtracting an +interval value from) a timestamp with time zone +value, the days component advances (or decrements) the date of the +timestamp with time zone by the indicated number of days. +Across daylight saving time changes (with the session tiem zone set to a +time zone that recognizes DST), this means interval '1 day' +does not necessarily equal interval '24 hours'. +For example, with the session time zone set to CST7CDTliteral> +timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' +will produce timestamp with time zone '2005-04-03 12:00-06', +while adding interval '24 hours' to the same initial +timestamp with time zone produces +timestamp with time zone '2005-04-03 13:00-06'literal>, as there is +a change in daylight saving time at 2005-04-03 02:00literal> in time zone +CST7CDT. + + Date/Time Operators Index: src/test/regress/expected/horology.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/ horology.out,v retrieving revision 1.56 diff -c -r1.56 horology.out *** src/test/regress/expected/horology.out27 May 2005 21:31:23 -1.56 --- src/test/regress/expected/horology.out26 Jul 2005 00:43:49 - *** *** 598,603 --- 598,630 t (1 row) + -- timestamp with time zone, interval arithmetic around DST change + SET TIME ZONE 'CST7CDT'; + SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' as "Apr 3, 12:00"; + Apr 3, 12:00 + -- + Sun Apr 03 12:00:00 2005 CDT + (1 row) + + SELECT timestamp with time zone '2005-04-02 12:00-07' + interval '24 hours' as "Apr 3, 13:00"; + Apr 3, 13:00 + -- + Sun Apr 03 13:00:00 2005 CDT + (1 row) + + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 day' as "Apr 2, 12:00"; + Apr 2, 12:00 + -- + Sat Apr 02 12:00:00 2005 CST + (1 row) + + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '24 hours' as "Apr 2, 11:00"; + Apr 2, 11:00 + -- + Sat Apr 02 11:00:00 2005 CST + (1 row) + + RESET TIME ZONE; SELECT timestamptz(date '1994-01-01', time '11:00') AS "Jan_01_1994_10am"; Jan_01_1994_10am -- Index: src/test/regress/expected/interval.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/ interval.out,v retrieving revision 1.12 diff -c -r1.12 interval.out *** src/test/regress/expected/interval.out20 Jul 2005 16:42:32 -1.12 --- src/test/regress/expected/interval.out26 Jul 2005 00:43:49 - *** *** 228,230 --- 228,243 @ 4541 years 4 mons 4 days 17 mins 31 secs (1 row) + -- test justify_hours() and justify_days() + SELECT justify_hours(interval '6 months 3 days 52 hours 3 minutes 2 seconds') as "6 mons 5 days 4 hours 3 mins 2 seconds"; + 6 mons 5 days 4 hours 3 mins 2 seconds + + @ 6 mons 5 days 4 hours 3 mins 2 secs + (1 row) + + SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as "7 mons 6 days 5 hours 4 mins 3 seconds"; + 7 mons 6 days 5 hours 4 mins 3 seconds + + @ 7 mons 6 days 5 hours 4 mins 3 secs + (1 row) + Index: src/test/regress/sql/horology.sql === RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/horology.sql,v retrieving revision 1.30 diff -c -r1.30 horology.sql *** src/test/regress/sql/horology.sql7 Apr 2005 01:51:41 - 1.30 --- src/test/regress/sql/horology.sql26 Jul 2005 00:43:49 - *** *** 114,119 --- 114,128 SELECT (timestamp with time zone 'tomorrow' = (timestamp with time zone 'yesterday' + interval '2 days')) as "True"; SELECT (timestamp with time zone 'to
