fomatting an interval

How can I format an interval? I want something like the default format but without the
milliseconds. However if I try to format it myself I lose the parts that are greater than
hours. http://developer.postgresql.org/docs/postgres/functions-formatting.html is not much
help. Does anyone know can I get the default format?

Your best option is probably to use the substring function to parse out
only the parts you want, and then combine them back together again if that's
what you need. If you do that in a function, you can re-use it whenever
you need it again.

Though it isn't specifically what you're after, below is an example that
might get you started, I wrote this earlier today to give me the
functionality of the 'months_between' function in Oracle.

It isn't quite an identical replacement yet, as Oracle's months_between()
function considers the dates '2001-01-31' and '2001-02-28' to be 1 month
apart while pgsql's age() function considers them to be 28 days apart.
I may have to add a few days to the 'age' to handle this.
--
Mike Nolan

create or replace function months_between(date, date)
returns integer as
'
DECLARE
date1 alias for $1;
date2 alias for $2;
wk_years int;
wk_months int;
BEGIN

if date1 is null or date2 is null then
return NULL;
end if;
wk_years := cast( coalesce(substring(age(date1, date2)
from ''([0123456789]*) year''),''0'') as int);
wk_months := cast( coalesce(substring(age(date1, date2)
from ''([0123456789]* ) mon''),''0'') as int);
return wk_years*12 + wk_months;
END
' language 'plpgsql';

I was considering doing something with substring, excpet I couldn't count on the interval
being anything in particular. Most of the time it is HH:MM:SS.mmm but sometimes it has
days before, and I can't count on there being .mmm at the end. Somtimes it is just .mm or .m.

nolan@celery.tssi.com wrote:

How can I format an interval? I want something like the default format but without the
milliseconds. However if I try to format it myself I lose the parts that are greater than
hours. http://developer.postgresql.org/docs/postgres/functions-formatting.html is not much
help. Does anyone know can I get the default format?

Your best option is probably to use the substring function to parse out
only the parts you want, and then combine them back together again if that's
what you need. If you do that in a function, you can re-use it whenever
you need it again.

Though it isn't specifically what you're after, below is an example that
might get you started, I wrote this earlier today to give me the
functionality of the 'months_between' function in Oracle.

It isn't quite an identical replacement yet, as Oracle's months_between()
function considers the dates '2001-01-31' and '2001-02-28' to be 1 month
apart while pgsql's age() function considers them to be 28 days apart.
I may have to add a few days to the 'age' to handle this.
--
Mike Nolan

create or replace function months_between(date, date)
returns integer as
'
DECLARE
date1 alias for $1;
date2 alias for $2;
wk_years int;
wk_months int;
BEGIN

if date1 is null or date2 is null then
return NULL;
end if;
wk_years := cast( coalesce(substring(age(date1, date2)
from ''([0123456789]*) year''),''0'') as int);
wk_months := cast( coalesce(substring(age(date1, date2)
from ''([0123456789]* ) mon''),''0'') as int);
return wk_years*12 + wk_months;
END
' language 'plpgsql';

--
Joseph Shraibman
joseph@xtenit.com
Increase signal to noise ratio. http://xis.xtenit.com

How can I format an interval?

Well, there are several possibilities such as to_char() and EXTRACT()
...

I want something like the default format but without the milliseconds.

... but for this particular problem, why not just round the given
interval to an integral number of seconds, by casting it to interval(0)?

regards, tom lane

Tom Lane wrote:

How can I format an interval?

Well, there are several possibilities such as to_char() and EXTRACT()
...

Right, except I don't know what format to use for to_char()

I want something like the default format but without the milliseconds.

... but for this particular problem, why not just round the given
interval to an integral number of seconds, by casting it to interval(0)?

playpen=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

playpen=# begin;
BEGIN
playpen=# create table timetable (start timestamp, finish timestamp);
CREATE TABLE
playpen=# insert into timetable values('2003-05-12 21:37:44.933', '2003-05-12 21:39:14.752');
INSERT 1648889 1
playpen=# select start, finish, (finish-start),(finish-start)::interval(0) from timetable;
start | finish | ?column? | interval
-------------------------+-------------------------+--------------+--------------
2003-05-12 21:37:44.933 | 2003-05-12 21:39:14.752 | 00:01:29.819 | 00:01:29.819
(1 row)

Joseph Shraibman <joseph@xtenit.com> writes:

playpen=# select start, finish, (finish-start),(finish-start)::interval(0) from timetable;
start | finish | ?column? | interval
-------------------------+-------------------------+--------------+--------------
2003-05-12 21:37:44.933 | 2003-05-12 21:39:14.752 | 00:01:29.819 | 00:01:29.819
(1 row)

[ blinks... ] It works for me in CVS tip ... [ time passes... ]
You're right though, it's busted in 7.3.*, and this was the fix:

2003-01-08 19:58 tgl

* src/include/catalog/pg_proc.h: Add missing pg_proc entry for
interval_scale(). The lack of this entry causes interval rounding
not to work as expected in 7.3, for example SELECT
'18:17:15.6'::interval(0) does not round the value. I did not
force initdb, but one is needed to install the added row.

We could backpatch this into the 7.3 branch, but it would only help
people who recompiled *and* re-initdb'd from the 7.3.3 sources. I
thought at the time it'd just create confusion to do that. I'm willing
to listen to other opinions though ...

regards, tom lane

On Mon, May 12, 2003 at 11:49:23PM -0400, Tom Lane wrote:

Joseph Shraibman <joseph@xtenit.com> writes:

2003-01-08 19:58 tgl

* src/include/catalog/pg_proc.h: Add missing pg_proc entry for
interval_scale(). The lack of this entry causes interval rounding
not to work as expected in 7.3, for example SELECT
'18:17:15.6'::interval(0) does not round the value. I did not
force initdb, but one is needed to install the added row.

We could backpatch this into the 7.3 branch, but it would only help
people who recompiled *and* re-initdb'd from the 7.3.3 sources. I
thought at the time it'd just create confusion to do that. I'm willing
to listen to other opinions though ...

Given that recompiling and adding the new tuple into pg_proc by means of
a simple INSERT(*) should be enough, it's hardly necessary to force an
initdb. Much less if there are not too many complaints, and a forced
initdb would force a dump/restore cycle that no one would like.

(*) It _can_ be done, right?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Everybody understands Mickey Mouse. Few understand Hermann Hesse.
Hardly anybody understands Einstein. And nobody understands Emperor Norton."

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

Given that recompiling and adding the new tuple into pg_proc by means of
a simple INSERT(*) should be enough, it's hardly necessary to force an
initdb.

(*) It _can_ be done, right?

I think so. The critical point is that interval_scale is not in the
compiled-in table of known internal functions in 7.3.2. A recompile
with the added pg_proc.h line should get it in there, and then you could
manually add the pg_proc entry without actually doing initdb. But I
haven't tested it to be sure there are no gotchas.

regards, tom lane

Hello all,

in my audit trail tables I want two columns to _always_ be
CURRENT_USER/CURRENT_TIMESTAMP.

I am currently doing this:
...
modified_by name not null default CURRENT_USER check(modified_by=CURRENT_USER),
...
(respective for CURRENT_TIMESTAMP)

I know this can also be achieved with a trigger on insert/update.
However, I'd like to know what is the "PostgreSQL way" of
doing this ? Do I achieve what I want with my above solution ?
It seems to work but feels clunky.

I am sure this has been discussed before on the mailing lists
but despite my search I have not been able to locate the
threads. Please point me to the keywords I need to use for the
search to succeed.

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Hello all,

in my audited tables I do this:
modify_when time stamp with time zone not null default CURRENT_TIMESTAMP check(modify_when=now()),

This works on insert. However, on update a function runs
via a trigger but fails with:
ExecReplace: rejected due to CHECK constraint audit_mark_modify_when

I can't get my head around why. The definitions go like this:

---------------------
1) an audited table (all but "dummy" inherited
from table audit_mark)

Table "test"
Attribute | Type | Modifier
-------------+--------------------------+-------------------------------------------------------------
pk_audit | integer | not null default nextval('"audit_mark_pk_audit_seq"'::text)
row_version | integer | default 0
modify_when | timestamp with time zone | not null default "timestamp"('now'::text)
modify_by | name | not null default "current_user"()
dummy | character varying(10) |
Constraints: (modify_by = "current_user"())
(modify_when = now())
------------------
2) the corresponding audit trail table (all but "dummy"
inherited from table audit_log):

Table "log_test"
Attribute | Type | Modifier
---------------+--------------------------+------------------------------------------------------------
pk_audit | integer | not null default nextval('"audit_log_pk_audit_seq"'::text)
orig_version | integer | not null default 0
orig_when | timestamp with time zone | not null
orig_by | name | not null
orig_tableoid | oid | not null
audit_action | character varying(6) | not null
audit_when | timestamp with time zone | not null default "timestamp"('now'::text)
audit_by | name | not null default "current_user"()
dummy | character varying(10) |
Constraints: (audit_by = "current_user"())
(audit_when = now())
((audit_action = 'UPDATE'::"varchar") OR (audit_action = 'DELETE'::"varchar"))
------------------
3) the function and trigger used to keep the audit trail:

CREATE FUNCTION f_audit_test() RETURNS OPAQUE AS '
BEGIN
-- explicitely increment row version counter
NEW.row_version := OLD.row_version + 1;
INSERT INTO log_test (
-- auditing metadata
orig_version, orig_when, orig_by, orig_tableoid, audit_action,
-- table content, except audit_mark data
dummy
) VALUES (
-- auditing metadata
OLD.row_version, OLD.modify_when, OLD.modify_by, TG_RELID, TG_OP,
-- table content, except audit_mark data
OLD.dummy
);
return NEW;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER t_audit_test
BEFORE UPDATE OR DELETE
ON test
FOR EACH ROW EXECUTE PROCEDURE f_audit_test();
---------------------
Insert works, update fails. Delete, too, but that's due to my
returning NEW which isn't defined, so don't mind that.

Any help is appreciated. This is on 7.1.3 (I know that's
rather old).

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Tue, 13 May 2003, Karsten Hilbert wrote:

in my audit trail tables I want two columns to _always_ be
CURRENT_USER/CURRENT_TIMESTAMP.

I'm guessing you mean that you want the two columns to always be the
user/time of the row's last modification, not always the current user and
current time (of who/when a select is done).

I am currently doing this:
...
modified_by name not null default CURRENT_USER check(modified_by=CURRENT_USER),
...
(respective for CURRENT_TIMESTAMP)

I know this can also be achieved with a trigger on insert/update.
However, I'd like to know what is the "PostgreSQL way" of
doing this ? Do I achieve what I want with my above solution ?

Assuming you want an automatically modified field, probably not. As you
noted, defaults aren't automatically propogated to columns on update.
Triggers are probably the best way to do it.

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

in my audited tables I do this:
modify_when time stamp with time zone not null default CURRENT_TIMESTAMP check(modify_when=now()),

This works on insert. However, on update a function runs
via a trigger but fails with:
ExecReplace: rejected due to CHECK constraint audit_mark_modify_when

Well, yeah. A default is computed on insert, but it has nothing to do
with updates. The above would essentially force all updates to
explicitly include "SET modify_when = now()", or the check condition
would fail.

The best way to achieve the effect you want is probably with a BEFORE
INSERT OR UPDATE trigger that explicitly sets NEW.modify_when = now().
Having done that, you don't need either the default or the check,
because there is no way to override the trigger's action (except with
another trigger).

regards, tom lane

I'm guessing you mean that you want the two columns to always be the
user/time of the row's last modification, not always the current user and
current time (of who/when a select is done).

Yes. I was imprecise.

Assuming you want an automatically modified field, probably not. As you
noted, defaults aren't automatically propogated to columns on update.
Triggers are probably the best way to do it.

Thanks. I've been implementing the triggers already. Seems to
work as expected.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Tom,

The best way to achieve the effect you want is probably with a BEFORE
INSERT OR UPDATE trigger that explicitly sets NEW.modify_when = now().
Having done that, you don't need either the default or the check,
because there is no way to override the trigger's action (except with
another trigger).

Did so. Works. Thanks.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

in my audited tables I do this:
modify_when time stamp with time zone not null default CURRENT_TIMESTAMP check(modify_when=now()),

This works on insert. However, on update a function runs
via a trigger but fails with:
ExecReplace: rejected due to CHECK constraint audit_mark_modify_when

Any help is appreciated. This is on 7.1.3 (I know that's
rather old).

Never mind since it's still the old mistake with the check
constraint.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Mon, May 12, 2003 at 09:09:20PM -0400, Tom Lane wrote:

How can I format an interval?

Well, there are several possibilities such as to_char() and EXTRACT()
...

I think we will mark to_char(interval) as deprecated function and
it will removed in some next release. It was already discussed.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

I'm sorry I am not up on hackers' discussions on this.

What is to be the alternative to to_char()?
I use to_char very heavily for pretty output of dates and times.
And I know I'm not the only one.

Will there be separate from interval, from timestamp, etc. functions?
Extract is not adequate because then you have to concatenate
it all back together again. It is important to have the formatting
simple and in one function if possible. A lot of notice needs
to be put out before deprecating to_char since it is really
widely used.

(The 7.3.3. bug is unfortunate. But I don't think it is a trigger
to dump the whole function.)

elein

On Wednesday 28 May 2003 03:24, Karel Zak wrote:

On Mon, May 12, 2003 at 09:09:20PM -0400, Tom Lane wrote:

How can I format an interval?

Well, there are several possibilities such as to_char() and EXTRACT()
...

I think we will mark to_char(interval) as deprecated function and
it will removed in some next release. It was already discussed.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
=============================================================
elein@varlena.com Database Consulting www.varlena.com
PostgreSQL General Bits http:/www.varlena.com/GeneralBits/
"Free your mind the rest will follow" -- en vogue

On Thu, May 29, 2003 at 07:22:04PM -0700, elein wrote:

I'm sorry I am not up on hackers' discussions on this.

What is to be the alternative to to_char()?
I use to_char very heavily for pretty output of dates and times.
And I know I'm not the only one.

Will there be separate from interval, from timestamp, etc. functions?
Extract is not adequate because then you have to concatenate
it all back together again. It is important to have the formatting
simple and in one function if possible. A lot of notice needs
to be put out before deprecating to_char since it is really
widely used.

No deprecating to_char (= means to_char versions), but deprecated is
_only_ "interval" to_char() version, because is unworkable.

The others to_char() versions for timestamp, date, numeric, etc. will
still in PostgreSQL and I hope it will there forever ;-)

Sorry if my last mail dismay someone -- we talked about
to_char(interval) only.

On Wednesday 28 May 2003 03:24, Karel Zak wrote:

I think we will mark to_char(interval) as deprecated function and

^^^^^^^

it will removed in some next release. It was already discussed.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/