You will get better, faster answers by sending questions to a
PostgreSQL mailing list. By emailing me directly you may not get a
timely response if I don't have time to answer. Others can then
answer and learn from the subsequent discussion. I'm ccing this to
pgsql-general.

On Mar 15, 2006, at 6:45 , Linda wrote:

> Hi, Michael
>
> I have a question about the output format of the INTERVAL type in
> version
> 8.1.1. In previous versions, I could do the following:
>
> select (uptime::varchar)::interval from machine_info;
>
> where uptime is an INTEGER type, the number of seconds the machine
> has been
> up since last reboot. This used to produce output in this format:
> 21 days 02:47:04
>
> Now in v8.1.1, the output format is
> 506:47:04
>
> How can I get the "justified" output as before? Is there some
> setting of
> datestyle that affects the output? I have tried specifying
> "interval day
> to second" but that doesn't work. Using the new justify_hours
> function
> works, but is it possible to do something that will run on older
> versions
> of postgresql?
>
> Thanks,
> Linda
>
> --
> Linda Gray
> Unitrends Corporation
> 803.454.0300 ext. 241
>

justify_hours is also in 8.1 and should do what you want.

test=# select '506:47:04'::interval;
interval
-----------
506:47:04
(1 row)

test=# select justify_hours('506:47:04'::interval);
justify_hours
------------------
21 days 02:47:04
(1 row)

test=# select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.1.3 on powerpc-apple-darwin8.4.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5247)
(1 row)


Michael Glaesemann
grzm myrealbox com




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

According to the SQL standard, shouldn't this work?

select '506:47:04'::interval day to second ;

Is there a portable way to do this without using justify_hours()?

- Thanks

On Tuesday 14 March 2006 6:58 pm, Michael Glaesemann wrote:
> You will get better, faster answers by sending questions to a
> PostgreSQL mailing list. By emailing me directly you may not get a
> timely response if I don't have time to answer. Others can then
> answer and learn from the subsequent discussion. I'm ccing this to
> pgsql-general.
>
> On Mar 15, 2006, at 6:45 , Linda wrote:
>
> > Hi, Michael
> >
> > I have a question about the output format of the INTERVAL type in
> > version
> > 8.1.1. In previous versions, I could do the following:
> >
> > select (uptime::varchar)::interval from machine_info;
> >
> > where uptime is an INTEGER type, the number of seconds the machine
> > has been
> > up since last reboot. This used to produce output in this format:
> > 21 days 02:47:04
> >
> > Now in v8.1.1, the output format is
> > 506:47:04
> >
> > How can I get the "justified" output as before? Is there some
> > setting of
> > datestyle that affects the output? I have tried specifying
> > "interval day
> > to second" but that doesn't work. Using the new justify_hours
> > function
> > works, but is it possible to do something that will run on older
> > versions
> > of postgresql?
> >
> > Thanks,
> > Linda
> >
> > --
> > Linda Gray
> > Unitrends Corporation
> > 803.454.0300 ext. 241
> >
>
> justify_hours is also in 8.1 and should do what you want.
>
> test=# select '506:47:04'::interval;
> interval
> -----------
> 506:47:04
> (1 row)
>
> test=# select justify_hours('506:47:04'::interval);
> justify_hours
> ------------------
> 21 days 02:47:04
> (1 row)
>
> test=# select version();
>
> version
> ------------------------------------------------------------------------
> ----------------------------------------------------------------------
> PostgreSQL 8.1.3 on powerpc-apple-darwin8.4.0, compiled by GCC
> powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
> build 5247)
> (1 row)
>
>
> Michael Glaesemann
> grzm myrealbox com
>
>
>
>

--
Linda Gray
Unitrends Corporation
803.454.0300 ext. 241

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Mar 15, 2006, at 23:39 , Linda wrote:

> According to the SQL standard, shouldn't this work?
>
> select '506:47:04'::interval day to second ;

No one has implemented this in PostgreSQL yet.

> Is there a portable way to do this without using justify_hours()?

Not currently that I know of.

Michael Glaesemann
grzm myrealbox com




---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Michael Glaesemann <grzm@myrealbox.com> writes:
> On Mar 15, 2006, at 23:39 , Linda wrote:
>> According to the SQL standard, shouldn't this work?
>>
>> select '506:47:04'::interval day to second ;

> No one has implemented this in PostgreSQL yet.

It depends on what you define as "work". 8.1 says

regression=# select '506:47:04'::interval day to second ;
interval
-----------
506:47:04
(1 row)

8.0 and before say

regression=# select '506:47:04'::interval day to second ;
interval
------------------
21 days 02:47:04
(1 row)

because before 8.1 we didn't distinguish intervals of "1 day" and "24 hours"
as being different. But the syntax has been accepted for a long time,
at least back to 7.0.

If there's some specific functionality you're after, you should say what
it is rather than expecting us to guess what you mean.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Mar 16, 2006, at 10:13 , Tom Lane wrote:

> But the syntax has been accepted for a long time,
> at least back to 7.0.

Well, look at that! Is this documented? Looks like I should install
the DocBook toolchain again.

Michael Glaesemann
grzm myrealbox com




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

I've been having some email problems, so my apologies if this is a
duplicate.

On Mar 16, 2006, at 22:49 , Linda wrote:


> Thanks for your reply. I guess you missed the original email. I
> have an
> application that is retrieving "uptime" (an integer number of
> seconds since
> reboot) and recasting it as varchar and then interval type.
>

Rather than perform this cast, you might want to make your own
function to handle this. Here are a couple (one in PL/pgSQL, the
other SQL). You should be able to use these functions any relatively
modern PostgreSQL installation.

(I find the x_int * interval some_int construct a bit cleaner than
forcing a cast as well.)

create or replace function secs_to_interval(integer)
returns interval
strict
immutable
language plpgsql as '
declare
secs alias for $1;
secs_per_day constant integer default 86400;
begin
return secs / secs_per_day * interval ''1 day'' + secs %
secs_per_day * interval ''1 second'';
end;
';

create or replace function secs_to_interval_sql(integer) returns
interval
strict
immutable
language sql as '
select $1 / 86400 * interval ''1 day'' + $1 % 86400 * interval ''1
second'';
';

test=# select secs_to_interval(1824459), secs_to_interval_sql(1824459);
secs_to_interval | secs_to_interval_sql
------------------+----------------------
21 days 02:47:39 | 21 days 02:47:39
(1 row)

test=# select secs_to_interval(86400), secs_to_interval_sql(86400);
secs_to_interval | secs_to_interval_sql
------------------+----------------------
1 day | 1 day
(1 row)

test=# select secs_to_interval(302), secs_to_interval_sql(302);
secs_to_interval | secs_to_interval_sql
------------------+----------------------
00:05:02 | 00:05:02
(1 row)

test=# select secs_to_interval(1824459 * 2), secs_to_interval_sql
(1824459 * 2);
secs_to_interval | secs_to_interval_sql
------------------+----------------------
42 days 05:35:18 | 42 days 05:35:18
(1 row)

Hope this helps.

Michael Glaesemann
grzm myrealbox com



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster