From b2b6548c79eb63faf1d0939893b3bf183d5410a1 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sat, 30 Jul 2005 14:52:04 +0000 Subject: [PATCH] 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. Michael Glaesemann --- doc/src/sgml/func.sgml | 20 ++++++++++++++++++- src/test/regress/expected/horology.out | 27 ++++++++++++++++++++++++++ src/test/regress/expected/interval.out | 13 +++++++++++++ src/test/regress/sql/horology.sql | 9 +++++++++ src/test/regress/sql/interval.sql | 7 +++++++ 5 files changed, 75 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 5c6092e424..55a5a63ae5 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -4903,6 +4903,24 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); 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 + 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', as there is + a change in daylight saving time at 2005-04-03 02:00 in time zone + CST7CDT. + + Date/Time Operators diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out index c0a7f6e5b8..fe85c2dfcf 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -598,6 +598,33 @@ SELECT (timestamp with time zone 'tomorrow' > 'now') as "True"; 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 ------------------------------ diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out index 7480e7f2e5..9efcdf8139 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -228,3 +228,16 @@ select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 @ 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) + diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index afc9feb0c8..d4be1f51d5 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -114,6 +114,15 @@ SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' 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"; diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql index cd17ebbc95..25e58659e7 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -69,3 +69,10 @@ select avg(f1) from interval_tbl; -- 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"; + -- 2.40.0