Skip site navigation (1) Skip section navigation (2)

Re: Interval->day docs and regression tests

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Michael Glaesemann <grzm(at)myrealbox(dot)com>
Cc: pgsql-patches Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: Interval->day docs and regression tests
Date: 2005-07-30 14:52:02
Message-ID: 200507301452.j6UEq2w19540@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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(at)postgresql(dot)org so that your
>        message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2005-07-30 14:53:20
Subject: Re: Updated open items
Previous:From: Tom LaneDate: 2005-07-30 14:51:08
Subject: Re: Updated open items

pgsql-patches by date

Next:From: Tom LaneDate: 2005-07-30 14:55:33
Subject: Re: [HACKERS] Autovacuum loose ends
Previous:From: Magnus HaganderDate: 2005-07-30 14:46:43
Subject: Re: Updated instrumentation patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group