Thread: Interval output format
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
"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
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
"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
"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
"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
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
> "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