Thread: Interval output format

Interval output format

From
"Stefan Waidele jun."
Date:
How can I tell Postgres to return an interval value in an format like hhh:mm?

I want to write an application to sum up hours.
I can get the sum with something like

select sum(hours) from table

But the output is '2 10:15'. This is correct, meaning I have worked 58
hours and 15 minutes.

The user needs something like '58:15', and my front end could convert that
(Delphi-Application).
But then I want the reports to be done on the server, using Perl. Good,
Perl can calculate
that. But I have to re implement the conversion, which ist error-prone.

Also it would be nice to have Postgres do the conversion, so that front
ends like psql could
be used.

Thanks for Your help,

Stefan


Re: Interval output format

From
Tom Lane
Date:
"Stefan Waidele jun." <St.Waidele.jun@Krone-Neuenburg.de> writes:
> How can I tell Postgres to return an interval value in an format like hhh:mm?

See to_char(),
http://www.postgresql.org/devel-corner/docs/postgres/functions-formatting.htm

            regards, tom lane

Re: Interval output format

From
"Stefan Waidele jun."
Date:
At 13:37 18.01.2001 -0500, Tom Lane wrote:
>"Stefan Waidele jun." <St.Waidele.jun@Krone-Neuenburg.de> writes:
> > How can I tell Postgres to return an interval value in an format like
> hhh:mm?
>
>See to_char(),
>http://www.postgresql.org/devel-corner/docs/postgres/functions-formatting.htm
>
>                         regards, tom lane

Thanks Tom,

I have been at this page before and did not get a clue.
Have I overlooked something? (Note: I am using an interval, not a date)
It would be great to have an example, since to_char('1 14:30', 'hh:mm')
(and others) gave me errors.

On the next page I have found dat_part(), and solved my problem using:
select date_part('day', sum(gearbeitet))*24+date_part('hour',
sum(gearbeitet))||':'||date_part('min', sum(gearbeitet)) from stunden;

But here comes the next problem:
When I tried to do a SQL-Function

create function sum_gearbeitet(text)
returns text
as "select
date_part('day',sum(gearbeitet))*24+date_part('hour',sum(gearbeitet))||':'||date_part('min',sum(gearbeitet))
from stunden;"
language 'sql';

  psql said:

NOTICE:  identifier "select
date_part('day',sum(gearbeitet))*24+date_part('hour',sum(gearbeitet))||':'||date_part('min',sum(gearbeitet))
from stunden;" will be truncated to "select date_part('day',sum(gear"
ERROR:  parser: parse error at or near """

What am I doing wrong? (shorter functions are also truncated)

Stefan


Re: Re: Interval output format

From
"Oliver Elphick"
Date:
"Stefan Waidele jun." wrote:
  >At 13:37 18.01.2001 -0500, Tom Lane wrote:
  >>"Stefan Waidele jun." <St.Waidele.jun@Krone-Neuenburg.de> writes:
  >> > How can I tell Postgres to return an interval value in an format like
  >> hhh:mm?
  >>
  >>See to_char(),
  >>http://www.postgresql.org/devel-corner/docs/postgres/functions-formatting.h
      >tm

to_char() can't take an interval, even in 7.1:

bray=# select proname,pronargs,proargtypes from pg_proc where proname = 'to_char';
 proname | pronargs | proargtypes
---------+----------+-------------
 to_char |        2 |       20 25
 to_char |        2 |       23 25
 to_char |        2 |      700 25
 to_char |        2 |      701 25
 to_char |        2 |     1184 25
 to_char |        2 |     1700 25
(6 rows)

and date_part() merely extracts the requested part, thus losing data:

bray=#  select date_part('hour','3 days 10:23'::INTERVAL);
 date_part
-----------
        10
(1 row)

Can to_char be extended?
--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For the eyes of the LORD run to and fro throughout the
      whole earth, to show himself strong in the behalf of
      them whose heart is perfect toward him..."
                                   II Chronicles 16:9



Re: Re: Interval output format

From
Tom Lane
Date:
"Stefan Waidele jun." <St.Waidele.jun@Krone-Neuenburg.de> writes:
> At 13:37 18.01.2001 -0500, Tom Lane wrote:
> How can I tell Postgres to return an interval value in an format like
>> hhh:mm?
>>
>> See to_char(),
>> http://www.postgresql.org/devel-corner/docs/postgres/functions-formatting.htm

My mistake :-( ... I thought there was a variant of to_char for
intervals as well as timestamps, but I see there is not.  Karel,
does this seem like it should be added for 7.2?  (Too late for
7.1, I think.)

            regards, tom lane

Re: Re: Interval output format

From
Tom Lane
Date:
"Stefan Waidele jun." <St.Waidele.jun@Krone-Neuenburg.de> writes:
> create function sum_gearbeitet(text)
> returns text
> as "select
> date_part('day',sum(gearbeitet))*24+date_part('hour',sum(gearbeitet))||':'||date_part('min',sum(gearbeitet))
> from stunden;"
> language 'sql';

That's just bogus syntax.  The function body has to be surrounded by
single quotes ' not double quotes ".  (Also, any single quote in the
function body has to be written as '' or \' to get past the string-
literal parser.  Kind of a pain --- you might want to use something
like pgaccess to make editing easier.)

            regards, tom lane

Re: Re: Interval output format

From
"Stefan Waidele jun."
Date:
Just for the records, in case someone has the same problem:

Q: How do I have an interval displayed only in 'hours:minutes' instead of
the default 'days hours:minutes'

A: My solution is a function like this:

CREATE FUNCTION "to_hours" (interval )
RETURNS text
AS 'select date_part(''day'', $1)*24 + date_part(''hour'', $1) || '':'' ||
date_part(''min'', $1);'
LANGUAGE 'SQL'

This allows for the following:

SELECT someattribute, to_hours( sum(myinterval) ) from mytable group by
someattribue;

which is all I need. It works like I expected a built-in - at least for my
purposes.
It even works if the interval is larger than a year, but only because the
interval uses days as its largest unit.

DRAWBACKS:
1. This function WILL break, if intervals will happen to have a
'date_part('[week|month|year]', i)'

2. This function returns the minute part only as single digit if minutes<10
(130:7 instead of 130:07) which makes it harder to parse the output. But
then again if You need the output split, You could use date_part on the
original value.

It seems to me that Postgres already has all the code it needs, it is just
has to be put together.
If to_char(INTERVAL) makes it into any release of Postgres, I will change
my queries to use it :-)

Thanks for Your help,

Stefan

At 14:29 19.01.2001 +0100, Karel Zak wrote:

> > "Stefan Waidele jun." <St.Waidele.jun@Krone-Neuenburg.de> writes:
> > > At 13:37 18.01.2001 -0500, Tom Lane wrote:
> > > How can I tell Postgres to return an interval value in an format like
> > >> hhh:mm?
> > >> [...]
> > does this seem like it should be added for 7.2?  (Too late for
> > 7.1, I think.)
>
>  For 7.1 it's really too late. For 7.2 I will speculate about it - first
>I must explore intervals inwards.
>
>                         Karel




Re: Re: Interval output format

From
Karel Zak
Date:
> "Stefan Waidele jun." <St.Waidele.jun@Krone-Neuenburg.de> writes:
> > At 13:37 18.01.2001 -0500, Tom Lane wrote:
> > How can I tell Postgres to return an interval value in an format like
> >> hhh:mm?
> >>
> >> See to_char(),
> >> http://www.postgresql.org/devel-corner/docs/postgres/functions-formatting.htm
>
> My mistake :-( ... I thought there was a variant of to_char for
> intervals as well as timestamps, but I see there is not.  Karel,
> does this seem like it should be added for 7.2?  (Too late for
> 7.1, I think.)

 For 7.1 it's really too late. For 7.2 I will speculate about it - first
I must explore intervals inwards.

            Karel