Why does the interval type not print seconds when they are zero?

This leads to inconsistent reading of the information.

7.3.3:

ler=# select '13 minutes'::interval;
 interval
----------
 00:13
(1 row)

ler=# select '13 minutes 1 second'::interval;
 interval
----------
 00:13:01
(1 row)

ler=#

I noticed this when I loaded the data from my long distance company into
a PG database.


-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749




Sat, 21 Jun 2003 16:07:10 -0500
Larry Rosenman <ler@lerctr.org>
interval's and printing...
Larry Rosenman <ler@lerctr.org> writes:
> Why does the interval type not print seconds when they are zero?

Seems like a bug to me too.  Anyone think it's not?

Note this only occurs with DateStyle = ISO, the other datestyles
use a different format for intervals.

			regards, tom lane

Sat, 21 Jun 2003 18:27:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: interval's and printing... 
Tom Lane wrote:
> Larry Rosenman <ler@lerctr.org> writes:
> > Why does the interval type not print seconds when they are zero?
> 
> Seems like a bug to me too.  Anyone think it's not?
> 
> Note this only occurs with DateStyle = ISO, the other datestyles
> use a different format for intervals.

Clearly seems like a bug to me.

-- 
  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

Sun, 22 Jun 2003 18:05:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: interval's and printing...

Add to TODO:

	* Have SELECT '13 minutes'::interval display zero seconds


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

Larry Rosenman wrote:
> Why does the interval type not print seconds when they are zero?
> 
> This leads to inconsistent reading of the information.
> 
> 7.3.3:
> 
> ler=# select '13 minutes'::interval;
>  interval
> ----------
>  00:13
> (1 row)
> 
> ler=# select '13 minutes 1 second'::interval;
>  interval
> ----------
>  00:13:01
> (1 row)
> 
> ler=#
> 
> I noticed this when I loaded the data from my long distance company into
> a PG database.
> 
> 
> -- 
> Larry Rosenman                     http://www.lerctr.org/~ler
> Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
  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

Mon, 23 Jun 2003 23:41:29 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: interval's and printing...


--On Monday, June 23, 2003 23:41:29 -0400 Bruce Momjian 
<pgman@candle.pha.pa.us> wrote:

>
> Add to TODO:
>
> 	* Have SELECT '13 minutes'::interval display zero seconds
you might want to clarify the TODO to include in ISO DateStyle.

(Per tom's comments).

LER



-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749




Tue, 24 Jun 2003 02:08:29 -0500
Larry Rosenman <ler@lerctr.org>
Re: interval's and printing...
Larry Rosenman wrote:
> >
> > Add to TODO:
> >
> > 	* Have SELECT '13 minutes'::interval display zero seconds
> you might want to clarify the TODO to include in ISO DateStyle.
> 
> (Per tom's comments).

Can you supply an example/text?

-- 
  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

Tue, 24 Jun 2003 09:46:33 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: interval's and printing...


--On Tuesday, June 24, 2003 09:46:33 -0400 Bruce Momjian 
<pgman@candle.pha.pa.us> wrote:

> Larry Rosenman wrote:
>> >
>> > Add to TODO:
>> >
>> > 	* Have SELECT '13 minutes'::interval display zero seconds
>> you might want to clarify the TODO to include in ISO DateStyle.
>>
>> (Per tom's comments).
>
> Can you supply an example/text?
* Have SELECT '13 minutes'::interval display zero seconds when using 
DateStyle ISO


>
> --
>   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



-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749




Tue, 24 Jun 2003 09:23:08 -0500
Larry Rosenman <ler@lerctr.org>
Re: interval's and printing...
Larry Rosenman wrote:
> >> > 	* Have SELECT '13 minutes'::interval display zero seconds
> >> you might want to clarify the TODO to include in ISO DateStyle.
> >>
> >> (Per tom's comments).
> >
> > Can you supply an example/text?
> * Have SELECT '13 minutes'::interval display zero seconds when using 
> DateStyle ISO

I am still confused.  What does such output look like.   Right now I
get:

	test=> SELECT '13 minutes'::interval;
	 interval
	----------
	 00:13
	(1 row)
	
I assume the idea is to display it as 00:13:00, right?

-- 
  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

Tue, 24 Jun 2003 10:44:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: interval's and printing...


--On Tuesday, June 24, 2003 10:44:25 -0400 Bruce Momjian 
<pgman@candle.pha.pa.us> wrote:

> Larry Rosenman wrote:
>> >> > 	* Have SELECT '13 minutes'::interval display zero seconds
>> >> you might want to clarify the TODO to include in ISO DateStyle.
>> >>
>> >> (Per tom's comments).
>> >
>> > Can you supply an example/text?
>> * Have SELECT '13 minutes'::interval display zero seconds when using
>> DateStyle ISO
>
> I am still confused.  What does such output look like.   Right now I
> get:
>
> 	test=> SELECT '13 minutes'::interval;
> 	 interval
> 	----------
> 	 00:13
> 	(1 row)
> 	
> I assume the idea is to display it as 00:13:00, right?
Correct, but with Postgres DateStyle or SQL, it does other stuff:

$ psql
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

ler=# set datestyle='postgres'
ler-# ;
SET
ler=# select '13 minutes'::interval
ler-# ;
 interval
-----------
 @ 13 mins
(1 row)

ler=# set datestyle='sql';
SET
ler=# select '13 minutes'::interval;
 interval
-----------
 @ 13 mins
(1 row)

ler=# set datestyle='iso,us';
SET
ler=# select '13 minutes'::interval;
 interval
----------
 00:13
(1 row)

ler=#
>
> --
>   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



-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749




Tue, 24 Jun 2003 09:50:20 -0500
Larry Rosenman <ler@lerctr.org>
Re: interval's and printing...

TODO updated:

	* Have SELECT '13 minutes'::interval display zero seconds in ISO
	  datestyle


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

Larry Rosenman wrote:
> 
> 
> --On Tuesday, June 24, 2003 10:44:25 -0400 Bruce Momjian 
> <pgman@candle.pha.pa.us> wrote:
> 
> > Larry Rosenman wrote:
> >> >> > 	* Have SELECT '13 minutes'::interval display zero seconds
> >> >> you might want to clarify the TODO to include in ISO DateStyle.
> >> >>
> >> >> (Per tom's comments).
> >> >
> >> > Can you supply an example/text?
> >> * Have SELECT '13 minutes'::interval display zero seconds when using
> >> DateStyle ISO
> >
> > I am still confused.  What does such output look like.   Right now I
> > get:
> >
> > 	test=> SELECT '13 minutes'::interval;
> > 	 interval
> > 	----------
> > 	 00:13
> > 	(1 row)
> > 	
> > I assume the idea is to display it as 00:13:00, right?
> Correct, but with Postgres DateStyle or SQL, it does other stuff:
> 
> $ psql
> Welcome to psql 7.3.3, the PostgreSQL interactive terminal.
> 
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
> 
> ler=# set datestyle='postgres'
> ler-# ;
> SET
> ler=# select '13 minutes'::interval
> ler-# ;
>  interval
> -----------
>  @ 13 mins
> (1 row)
> 
> ler=# set datestyle='sql';
> SET
> ler=# select '13 minutes'::interval;
>  interval
> -----------
>  @ 13 mins
> (1 row)
> 
> ler=# set datestyle='iso,us';
> SET
> ler=# select '13 minutes'::interval;
>  interval
> ----------
>  00:13
> (1 row)
> 
> ler=#
> >
> > --
> >   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
> 
> 
> 
> -- 
> Larry Rosenman                     http://www.lerctr.org/~ler
> Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
> 
> 
> 
> 

-- 
  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

Tue, 24 Jun 2003 14:43:24 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: interval's and printing...


--On Tuesday, June 24, 2003 14:43:24 -0400 Bruce Momjian 
<pgman@candle.pha.pa.us> wrote:

>
> TODO updated:
>
> 	* Have SELECT '13 minutes'::interval display zero seconds in ISO
> 	  datestyle
>
I just posted a patch to -patches for this.  (it's a quickie, even).



-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749




Tue, 24 Jun 2003 16:12:11 -0500
Larry Rosenman <ler@lerctr.org>
Re: interval's and printing...