Обсуждение: 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.sgml 22 Jul 2005 21:16:14 -0000 1.269
--- doc/src/sgml/func.sgml 26 Jul 2005 00:43:49 -0000
***************
*** 4903,4908 ****
--- 4903,4926 ----
such pair.
</para>
+ <para>
+ When adding an <type>interval</type> value to (or subtracting an
+ <type>interval</type> value from) a <type>timestamp with time
zone</type>
+ value, the days component advances (or decrements) the date of the
+ <type>timestamp with time zone<type> 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 <literal>interval '1
day'</literal>
+ does not necessarily equal <literal>interval '24 hours'</literal>.
+ For example, with the session time zone set to <literal>CST7CDT</
literal>
+ <literal>timestamp with time zone '2005-04-02 12:00-07' +
interval '1 day' </literal>
+ will produce <literal>timestamp with time zone '2005-04-03
12:00-06'</literal>,
+ while adding <literal>interval '24 hours'</literal> to the same
initial
+ <type>timestamp with time zone</type> produces
+ <literal>timestamp with time zone '2005-04-03 13:00-06'</
literal>, as there is
+ a change in daylight saving time at <literal>2005-04-03 02:00</
literal> in time zone
+ <literal>CST7CDT</literal>.
+ </para>
+
<table id="operators-datetime-table">
<title>Date/Time Operators</title>
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.out 27 May 2005 21:31:23
-0000 1.56
--- src/test/regress/expected/horology.out 26 Jul 2005 00:43:49 -0000
***************
*** 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.out 20 Jul 2005 16:42:32
-0000 1.12
--- src/test/regress/expected/interval.out 26 Jul 2005 00:43:49 -0000
***************
*** 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.sql 7 Apr 2005 01:51:41 -0000
1.30
--- src/test/regress/sql/horology.sql 26 Jul 2005 00:43:49 -0000
***************
*** 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 'tomorrow' > 'now') as "True";
+ -- 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";
+ SELECT timestamp with time zone '2005-04-02 12:00-07' + interval
'24 hours' as "Apr 3, 13:00";
+ SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1
day' as "Apr 2, 12:00";
+ SELECT timestamp with time zone '2005-04-03 12:00-06' - interval
'24 hours' as "Apr 2, 11:00";
+ RESET TIME ZONE;
+
+
SELECT timestamptz(date '1994-01-01', time '11:00') AS
"Jan_01_1994_10am";
SELECT timestamptz(date '1994-01-01', time '10:00') AS
"Jan_01_1994_9am";
SELECT timestamptz(date '1994-01-01', time with time zone
'11:00-8') AS "Jan_01_1994_11am";
Index: src/test/regress/sql/interval.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/interval.sql,v
retrieving revision 1.7
diff -c -r1.7 interval.sql
*** src/test/regress/sql/interval.sql 26 May 2005 02:04:14
-0000 1.7
--- src/test/regress/sql/interval.sql 26 Jul 2005 00:43:49 -0000
***************
*** 69,71 ****
--- 69,78 ----
-- test long interval input
select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days
17 minutes 31 seconds'::interval;
+
+
+ -- 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";
+ 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";
+
Вложения
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.sgml 22 Jul 2005 21:16:14 -0000 1.269 > --- doc/src/sgml/func.sgml 26 Jul 2005 00:43:49 -0000 > *************** > *** 4903,4908 **** > --- 4903,4926 ---- > such pair. > </para> > > + <para> > + When adding an <type>interval</type> value to (or subtracting an > + <type>interval</type> value from) a <type>timestamp with time > zone</type> > + value, the days component advances (or decrements) the date of the > + <type>timestamp with time zone<type> 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 <literal>interval '1 > day'</literal> > + does not necessarily equal <literal>interval '24 hours'</literal>. > + For example, with the session time zone set to <literal>CST7CDT</ > literal> > + <literal>timestamp with time zone '2005-04-02 12:00-07' + > interval '1 day' </literal> > + will produce <literal>timestamp with time zone '2005-04-03 > 12:00-06'</literal>, > + while adding <literal>interval '24 hours'</literal> to the same > initial > + <type>timestamp with time zone</type> produces > + <literal>timestamp with time zone '2005-04-03 13:00-06'</ > literal>, as there is > + a change in daylight saving time at <literal>2005-04-03 02:00</ > literal> in time zone > + <literal>CST7CDT</literal>. > + </para> > + > <table id="operators-datetime-table"> > <title>Date/Time Operators</title> > > 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.out 27 May 2005 21:31:23 > -0000 1.56 > --- src/test/regress/expected/horology.out 26 Jul 2005 00:43:49 -0000 > *************** > *** 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.out 20 Jul 2005 16:42:32 > -0000 1.12 > --- src/test/regress/expected/interval.out 26 Jul 2005 00:43:49 -0000 > *************** > *** 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.sql 7 Apr 2005 01:51:41 -0000 > 1.30 > --- src/test/regress/sql/horology.sql 26 Jul 2005 00:43:49 -0000 > *************** > *** 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 'tomorrow' > 'now') as "True"; > > + -- 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"; > + SELECT timestamp with time zone '2005-04-02 12:00-07' + interval > '24 hours' as "Apr 3, 13:00"; > + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval '1 > day' as "Apr 2, 12:00"; > + SELECT timestamp with time zone '2005-04-03 12:00-06' - interval > '24 hours' as "Apr 2, 11:00"; > + RESET TIME ZONE; > + > + > SELECT timestamptz(date '1994-01-01', time '11:00') AS > "Jan_01_1994_10am"; > SELECT timestamptz(date '1994-01-01', time '10:00') AS > "Jan_01_1994_9am"; > SELECT timestamptz(date '1994-01-01', time with time zone > '11:00-8') AS "Jan_01_1994_11am"; > Index: src/test/regress/sql/interval.sql > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/interval.sql,v > retrieving revision 1.7 > diff -c -r1.7 interval.sql > *** src/test/regress/sql/interval.sql 26 May 2005 02:04:14 > -0000 1.7 > --- src/test/regress/sql/interval.sql 26 Jul 2005 00:43:49 -0000 > *************** > *** 69,71 **** > --- 69,78 ---- > > -- test long interval input > select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days > 17 minutes 31 seconds'::interval; > + > + > + -- 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"; > + 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"; > + > > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- 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
Tom Lane wrote: > Michael Glaesemann <grzm@myrealbox.com> 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 -0000 1.146 --- src/backend/utils/adt/timestamp.c 30 Jul 2005 18:19:14 -0000 *************** *** 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); }