Thread: 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?
> 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
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
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
> 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
> 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
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)
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/