Re: question about postgresql time intervals

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

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

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

Michael Glaesemann <grzm(at)myrealbox(dot)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

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

On Wednesday 15 March 2006 8:13 pm, Tom Lane wrote:
> Michael Glaesemann <grzm(at)myrealbox(dot)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.
>

Hi, Tom

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. For example,

select (1824459::varchar)::interval;

Which in previous versions of Postgres returned '21 days 02:47:39'. If I
use "justify_hours" the application will not run on older versions of
Postgres, and will not be portable to other DBs. I thought perhaps some
"datestyle" setting was different or some other factor that I was
overlooking. I am trying to find a generic way to get the same output on
newer versions of PostgreSQL.

Thanks,
Linda

> regards, tom lane
>

--
Linda Gray
Unitrends Corporation
803.454.0300 ext. 241

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

On Friday 17 March 2006 10:20 pm, Michael Glaesemann wrote:
> 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
>
>
>

Hi, Michael

Thanks for the suggestion! This approach will work on both older and newer
version of PostgreSQL.

Thanks,
Linda

--
Linda Gray
Unitrends Corporation
803.454.0300 ext. 241