Re: interval's and printing...

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(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry Rosenman <ler(at)lerctr(dot)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

Tom Lane wrote:
> Larry Rosenman <ler(at)lerctr(dot)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(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

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(at)lerctr(dot)org
> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

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

--On Monday, June 23, 2003 23:41:29 -0400 Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)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(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

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

--On Tuesday, June 24, 2003 09:46:33 -0400 Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)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(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

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

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

--On Tuesday, June 24, 2003 10:44:25 -0400 Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)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(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

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

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(at)candle(dot)pha(dot)pa(dot)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(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
>
>
>
> --
> Larry Rosenman http://www.lerctr.org/~ler
> Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
>
>
>
>

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

--On Tuesday, June 24, 2003 14:43:24 -0400 Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)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(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749