Interval->day docs and regression tests

Started by Michael Glaesemannover 20 years ago4 messages

grzm@myrealbox.com

over 20 years ago

1 attachment(s)

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";
+

Attachments:

interval-docs-regress.diffapplication/octet-stream; name=interval-docs-regress.diff; x-unix-mode=0644Download

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";
+

Bruce Momjian

pgman@candle.pha.pa.us

over 20 years ago

In reply to: Michael Glaesemann (#1)

Re: Interval->day docs and regression tests

Patch applied. Thanks.

---------------------------------------------------------------------------

Michael Glaesemann wrote:

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

tgl@sss.pgh.pa.us

over 20 years ago

In reply to: Michael Glaesemann (#1)

Re: [PATCHES] Interval->day docs and regression tests

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)

regards, tom lane

Bruce Momjian

pgman@candle.pha.pa.us

over 20 years ago

In reply to: Tom Lane (#3)

1 attachment(s)

Re: Interval->day docs and regression tests

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

Attachments:

/bjm/difftext/plainDownload

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);
  }