Interval->day docs and regression tests
От | Michael Glaesemann |
---|---|
Тема | Interval->day docs and regression tests |
Дата | |
Msg-id | 0A57C6CE-45A3-433F-A5A1-F6B3B1A74C3A@myrealbox.com обсуждение исходный текст |
Ответы |
Re: Interval->day docs and regression tests
|
Список | pgsql-patches |
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"; +
Вложения
В списке pgsql-patches по дате отправления: