Thread: fomatting an interval

fomatting an interval

From
Joseph Shraibman
Date:
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?


Re: fomatting an interval

From
nolan@celery.tssi.com
Date:
> 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';


Re: fomatting an interval

From
Joseph Shraibman
Date:
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


Re: fomatting an interval

From
Tom Lane
Date:
>>> 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


Re: fomatting an interval

From
Tom Lane
Date:
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


Re: fomatting an interval

From
Alvaro Herrera
Date:
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."


Re: fomatting an interval

From
Tom Lane
Date:
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


forcing a literal value in a column

From
Karsten Hilbert
Date:
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


Re: forcing a literal value in a column

From
Karsten Hilbert
Date:
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


Re: forcing a literal value in a column

From
Stephan Szabo
Date:
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.


Re: forcing a literal value in a column

From
Tom Lane
Date:
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


Re: forcing a literal value in a column

From
Karsten Hilbert
Date:
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


Re: forcing a literal value in a column

From
Karsten Hilbert
Date:
> 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


Re: forcing a literal value in a column

From
Karsten Hilbert
Date:
> 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


Re: fomatting an interval

From
Joseph Shraibman
Date:
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)


Re: fomatting an interval

From
Karel Zak
Date:
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/

to_char (was Re: fomatting an interval)

From
elein
Date:

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


Re: to_char (was Re: fomatting an interval)

From
Karel Zak
Date:
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/