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)


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)
Mon, 12 May 2003 21:47:00 -0400
Joseph Shraibman <jks@selectacast.net>
Re: fomatting an interval (resend)
How about:

select to_char(finish-start, 'HH24:MI:SS') from timetable;
 to_char  
----------
 00:01:29

This won't work if the interval is > 24 hours - you would have to add 
formatting to allow for days.

Cheers,
Steve


On Monday 12 May 2003 6:47 pm, Joseph Shraibman wrote:
> 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)


Tue, 13 May 2003 09:08:35 -0700
Steve Crawford <scrawford@pinpointresearch.com>
Re: fomatting an interval (resend)
On Tue, 13 May 2003, Steve Crawford wrote:

> How about:
> 
> select to_char(finish-start, 'HH24:MI:SS') from timetable;
>  to_char  
> ----------
>  00:01:29
> 
> This won't work if the interval is > 24 hours - you would have to add 
> formatting to allow for days.
> 

... which is exactly my problem.


Tue, 13 May 2003 13:41:42 -0400 (EDT)
jks@selectacast.net
Re: fomatting an interval (resend)
write a pl/pgsql function that outputs the format, based on receiving the interval in seconds.

jks@selectacast.net wrote:
> On Tue, 13 May 2003, Steve Crawford wrote:
> 
> 
>>How about:
>>
>>select to_char(finish-start, 'HH24:MI:SS') from timetable;
>> to_char  
>>----------
>> 00:01:29
>>
>>This won't work if the interval is > 24 hours - you would have to add 
>>formatting to allow for days.
>>
> 
> 
> ... which is exactly my problem.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 


Tue, 13 May 2003 15:06:04 -0700
Dennis Gearon <gearond@cvc.net>
Re: fomatting an interval (resend)
Dennis Gearon wrote:
> write a pl/pgsql function that outputs the format, based on receiving 
> the interval in seconds.
> 
I don't want to do something so complicated.  There is already a to_char() method, I just 
want to know what I should pass into it.


Tue, 13 May 2003 18:42:08 -0400
Joseph Shraibman <jks@selectacast.net>
Re: fomatting an interval (resend)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


>> write a pl/pgsql function that outputs the format, based on receiving 
>> the interval in seconds.
>
> I don't want to do something so complicated.  There is already a to_char() 
> method, I just want to know what I should pass into it.

What sort of output are you exactly looking for? Intervals by nature output 
a mixed format, so I don't think there is anything simple you can do with 
to_char to get what you want. Can you give some examples of your desired 
output for different intervals?


Here is one consistent conversion that will show you the number of seconds 
without anything else:


select round(extract(epoch from finish) - extract(epoch from start)) from timetable;


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305141036

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+wlWQvJuQZxSWSsgRApHtAJ4ppW3EgVQpoveF6z+yEKPW0BziKACg75bO
eZfRxYjgHopdTeO4B7vv/j0=
=sID9
-----END PGP SIGNATURE-----




Wed, 14 May 2003 14:44:04 -0000
greg@turnstep.com
Re: fomatting an interval (resend)
If you have variable length arguments to 'to_char()', you may not be able to succesfully use it.

Joseph Shraibman wrote:
> Dennis Gearon wrote:
> 
>> write a pl/pgsql function that outputs the format, based on receiving 
>> the interval in seconds.
>>
> I don't want to do something so complicated.  There is already a 
> to_char() method, I just want to know what I should pass into it.
> 
> 


Wed, 14 May 2003 08:41:20 -0700
Dennis Gearon <gearond@cvc.net>
Re: fomatting an interval (resend)
On a related subject, I'm trying to get postgres 7.3.2 to spit out an
interval in terms of days.  For example, I'm currently getting this
behavior:

<-  select '2002-05-04 01:03'::timestamp - '2002-05-02'::timestamp;
->  2 days 01:03

The result is an interval, which gets reported as some varying
combination of days, hours, minutes, etc.  (Maybe even months or years
in some cases?)  What my application wants is an integer value
representing the difference between timestamps in terms of days.  I
tried using the round() function, but round() doesn't work on
intervals.

I just saw the following query posted earlier in this thread:

>Here is one consistent conversion that will show you the number of seconds 
>without anything else:
>
>select round(extract(epoch from finish) - extract(epoch from start)) from timetable;

Hmm.  The postgres 7.3 docs give me the impression that extract() will
return one field of a multi-field value, such as '4 days' from '2
years 4 days 15:01'.  Experimenting in psql seems to prove this.  For
example:

<- select extract( day from '2 years 4 days 15:01'::interval);
-> 4

Okay, so extract() doesn't fit the bill either.  How do I get the
difference between two timestamps in terms of days, expressed as an
integer?  Moreover, how do I get any interval expressed in those
terms?

(Should I be posting this to the -sql list instead?)



Wed, 14 May 2003 10:12:41 -0700
"Forest Wilkinson" <lyris-pg@tibit.com>
Re: fomatting an interval (resend)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Okay, so extract() doesn't fit the bill either.  How do I get the
> difference between two timestamps in terms of days, expressed as an
> integer?  Moreover, how do I get any interval expressed in those
> terms?

select round((extract(epoch from finish) - extract(epoch from start))/(60*60*24)) 
  from timetable;

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305141326

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+wnzCvJuQZxSWSsgRAkEJAKDpgxGsi9Y+0mp74t1TqQe8xc7CKwCg2NFf
uS25ZUK0P8d5bnasX581+dA=
=kZ96
-----END PGP SIGNATURE-----




Wed, 14 May 2003 17:31:18 -0000
greg@turnstep.com
Re: fomatting an interval (resend)
On Wed, May 14, 2003 at 10:12:41AM -0700, Forest Wilkinson wrote:

> >Here is one consistent conversion that will show you the number of seconds 
> >without anything else:
> >
> >select round(extract(epoch from finish) - extract(epoch from start)) from timetable;
> 
> Hmm.  The postgres 7.3 docs give me the impression that extract() will
> return one field of a multi-field value, such as '4 days' from '2
> years 4 days 15:01'.  Experimenting in psql seems to prove this.  For
> example:

Extracting(epoch) from an interval is a special case.  It returns the
total number of seconds in the interval.  Note that it is quite
different from extracting(epoch) from a timestamp.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Si quieres ser creativo, aprende el arte de perder el tiempo"

Wed, 14 May 2003 22:02:57 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: fomatting an interval (resend)
>> Okay, so extract() doesn't fit the bill either.  How do I get the
>> difference between two timestamps in terms of days, expressed as an
>> integer?  Moreover, how do I get any interval expressed in those
>> terms?
>
>select round((extract(epoch from finish) - extract(epoch from start))/(60*60*24)) 
>  from timetable;

How would the following differ from the query above?

select round(extract(epoch from (finish - start))/(60*60*24)) from
timetable;


Thu, 22 May 2003 18:07:55 -0700
lyris-pg@tibit.com
Re: fomatting an interval
>> Okay, so extract() doesn't fit the bill either.  How do I get the
>> difference between two timestamps in terms of days, expressed as an
>> integer?  Moreover, how do I get any interval expressed in those
>> terms?
>
>select round((extract(epoch from finish) - extract(epoch from start))/(60*60*24)) 
>  from timetable;

How would that differ from the query below?

select round(extract(epoch from (finish - start))/(60*60*24)) from
timetable;


Thu, 29 May 2003 14:26:16 -0700
lyris-pg@tibit.com
Re: fomatting an interval (resend)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


>>> Okay, so extract() doesn't fit the bill either.  How do I get the
>>> difference between two timestamps in terms of days, expressed as an
>>> integer?  Moreover, how do I get any interval expressed in those
>>> terms?

>> select round((extract(epoch from finish) - 
>> extract(epoch from start))/(60*60*24)) from timetable;

>How would that differ from the query below?
>
>select round(extract(epoch from (finish - start))/(60*60*24)) from 
>timetable;

No real difference - the first is extracting from timestamps, the 
second is extracting from an interval. The second is more compact, 
but I prefer the first as it is a little more explicit in what is 
happening.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305301433
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+16QZvJuQZxSWSsgRAhPyAKC9lz5pAr7W94Sm6MPXp77CoG04UgCg062W
0ylMMu/79liaR48hG3tIw2c=
=zQTl
-----END PGP SIGNATURE-----



Fri, 30 May 2003 18:37:06 -0000
greg@turnstep.com
Re: fomatting an interval (resend)
I'm porting a postgres client app (along with all the users'
databases) from postgres 7.1.3 to 7.3.x.  The new version of the app
needs a way of enumerating foreign key constraints on a table,
including the constraint names and the constrained columns.
Unfortunately, postgres 7.1.3 didn't have a pg_constraint table, and
the dump/restore process doesn't create the appropriate pg_constraint
rows for foreign keys when migrating.  With that in mind, how can I
enumerate foreign key constraints in any database, whether it was
originally created with postgres 7.1.3 or 7.3.x?

It looks like I can get a list of constraint names from the pg_trigger
table, but each foreign key constraint corresponds to several triggers
on either of the two tables involved.  How can I distinguish the
referenced table from the referencing one?  Is pg_trigger even the
correct place to look?

Once I find a migrated foreign key constraint, since it won't have an
entry in pg_constraint, will I be able to drop it?


Tue, 10 Jun 2003 14:45:07 -0700
Forest Wilkinson <lyris-pg@tibit.com>
How to enumerate foreign key constraints after migrating from 7.1.3?
Forest Wilkinson <lyris-pg@tibit.com> writes:
> I'm porting a postgres client app (along with all the users'
> databases) from postgres 7.1.3 to 7.3.x.  The new version of the app
> needs a way of enumerating foreign key constraints on a table,
> including the constraint names and the constrained columns.
> Unfortunately, postgres 7.1.3 didn't have a pg_constraint table, and
> the dump/restore process doesn't create the appropriate pg_constraint
> rows for foreign keys when migrating.

I believe there is a contrib module to help with updating foreign keys
into proper 7.3 form.  Look in contrib/adddepend.

> With that in mind, how can I
> enumerate foreign key constraints in any database, whether it was
> originally created with postgres 7.1.3 or 7.3.x?

Something looking at the triggers might work, but I'd recommend just
getting the pg_constraint entries to exist and then using those.

			regards, tom lane

Tue, 10 Jun 2003 17:57:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: How to enumerate foreign key constraints after migrating from
	7.1.3?
>I believe there is a contrib module to help with updating foreign keys
>into proper 7.3 form.  Look in contrib/adddepend.
>
>> With that in mind, how can I
>> enumerate foreign key constraints in any database, whether it was
>> originally created with postgres 7.1.3 or 7.3.x?
>
>Something looking at the triggers might work, but I'd recommend just
>getting the pg_constraint entries to exist and then using those.

Unfortunately, having all my users run contrib/adddepend isn't an
option for me.  However, that script does contain a good deal of
information that I may be able to use for detecting old-style foreign
key constraints in my own code.

Okay, more questions:
I see that adddepend detects old-style foreign key constraints by
looking for groups of 3 triggers having 6 or more identical function
arguments.  Is that the best way to do it?  It occurs to me that an
alternative might be to find triggers that call RI_FKey_check_ins()
and have the tgisconstraint flag set.  Will either approach be safe in
postgres 7.4?  Perhaps a combination of the two would be best?

Would this topic be more appropriate for the hackers list?


Tue, 10 Jun 2003 19:08:20 -0700
Forest Wilkinson <lyris-pg@tibit.com>
Re: How to enumerate foreign key constraints after migrating from
	7.1.3?

On Tue, 10 Jun 2003, Forest Wilkinson wrote:

> >I believe there is a contrib module to help with updating foreign keys
> >into proper 7.3 form.  Look in contrib/adddepend.
> >
> >> With that in mind, how can I
> >> enumerate foreign key constraints in any database, whether it was
> >> originally created with postgres 7.1.3 or 7.3.x?
> >
> >Something looking at the triggers might work, but I'd recommend just
> >getting the pg_constraint entries to exist and then using those.
>
> Unfortunately, having all my users run contrib/adddepend isn't an
> option for me.  However, that script does contain a good deal of
> information that I may be able to use for detecting old-style foreign
> key constraints in my own code.
>
> Okay, more questions:
> I see that adddepend detects old-style foreign key constraints by
> looking for groups of 3 triggers having 6 or more identical function
> arguments.  Is that the best way to do it?  It occurs to me that an
> alternative might be to find triggers that call RI_FKey_check_ins()
> and have the tgisconstraint flag set.  Will either approach be safe in
> postgres 7.4?  Perhaps a combination of the two would be best?

Without looking at the other triggers, you can't determine the referential
action information since that's encoded in the functions the other two
triggers call.


Tue, 10 Jun 2003 19:22:04 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: How to enumerate foreign key constraints after migrating
Moving to -hackers.

> Unfortunately, having all my users run contrib/adddepend isn't an
> option for me.  However, that script does contain a good deal of
> information that I may be able to use for detecting old-style foreign
> key constraints in my own code.

I assume you're doing the database upgrade for them or providing
instructions?  Could this be a mandatory portion of that process?

> Okay, more questions:
> I see that adddepend detects old-style foreign key constraints by
> looking for groups of 3 triggers having 6 or more identical function
> arguments.  Is that the best way to do it?  It occurs to me that an
> alternative might be to find triggers that call RI_FKey_check_ins()
> and have the tgisconstraint flag set.  Will either approach be safe in
> postgres 7.4?  Perhaps a combination of the two would be best?

Yes, a combination of the two would probably be better.  You would need
to be careful about function call names for FKeys, there are a fair
number of them.  Checking for 3 triggers with the function name starting
with RI_FKey* would probably be better.

That said, I've not heard of any issues with the current implementation
of adddepend, which is also used in a few other well used programs.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
10 Jun 2003 22:25:05 -0400
Rod Taylor <rbt@rbt.ca>
Re: [GENERAL] How to enumerate foreign key constraints after
On Tue, Jun 10, 2003 at 10:25:05PM -0400, Rod Taylor wrote:

> > I see that adddepend detects old-style foreign key constraints by
> > looking for groups of 3 triggers having 6 or more identical function
> > arguments.  Is that the best way to do it?

> That said, I've not heard of any issues with the current implementation
> of adddepend, which is also used in a few other well used programs.

I used adddepend on a relatively complicated schema with lots of foreign
key constraints and sequences it worked pretty well.  It was from 7.1.3
to 7.4devel.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Investigaciï¿½n es lo que hago cuando no sï¿½ lo que estoy haciendo"
(Wernher von Braun)

Tue, 10 Jun 2003 23:05:40 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: [GENERAL] How to enumerate foreign key constraints after
>> Okay, more questions:
>> I see that adddepend detects old-style foreign key constraints by
>> looking for groups of 3 triggers having 6 or more identical function
>> arguments.  Is that the best way to do it?  It occurs to me that an
>> alternative might be to find triggers that call RI_FKey_check_ins()
>> and have the tgisconstraint flag set.  Will either approach be safe in
>> postgres 7.4?  Perhaps a combination of the two would be best?
>
>Without looking at the other triggers, you can't determine the referential
>action information since that's encoded in the functions the other two
>triggers call.

I understand that.  I just want a list of the foreign key constraints
that are set on the columns of a table.  I don't really need to know
what happens when a referenced column gets modified.


Wed, 11 Jun 2003 09:52:48 -0700
Forest Wilkinson <lyris-pg@tibit.com>
Re: How to enumerate foreign key constraints after migrating from
	7.1.3?
Rod Taylor <rbt@rbt.ca> wrote:

>> Unfortunately, having all my users run contrib/adddepend isn't an
>> option for me.  However, that script does contain a good deal of
>> information that I may be able to use for detecting old-style foreign
>> key constraints in my own code.
>
>I assume you're doing the database upgrade for them or providing
>instructions?  Could this be a mandatory portion of that process?

We're referring users to chapter 9.3 of the PostgreSQL Administrator's
Guide for data migration.  Even the standard postgres dump/restore
procedure is nearly too much for our users.  Anything more would be
asking for trouble.

>> Okay, more questions:
>> I see that adddepend detects old-style foreign key constraints by
>> looking for groups of 3 triggers having 6 or more identical function
>> arguments.  Is that the best way to do it?  It occurs to me that an
>> alternative might be to find triggers that call RI_FKey_check_ins()
>> and have the tgisconstraint flag set.  Will either approach be safe in
>> postgres 7.4?  Perhaps a combination of the two would be best?
>
>Yes, a combination of the two would probably be better.  You would need
>to be careful about function call names for FKeys, there are a fair
>number of them.  Checking for 3 triggers with the function name starting
>with RI_FKey* would probably be better.

Will the tgisconstraint flag always be set for these triggers?  Will
the function names still be RI_FKey* once these databases are migrated
to postgres 7.4 or later?



Wed, 11 Jun 2003 10:43:50 -0700
Forest Wilkinson <lyris-pg@tibit.com>
Re: [GENERAL] How to enumerate foreign key constraints after
	migrating from 7.1.3?
> >Yes, a combination of the two would probably be better.  You would need
> >to be careful about function call names for FKeys, there are a fair
> >number of them.  Checking for 3 triggers with the function name starting
> >with RI_FKey* would probably be better.
> 
> Will the tgisconstraint flag always be set for these triggers?  Will
> the function names still be RI_FKey* once these databases are migrated
> to postgres 7.4 or later?

Until someone has a good reason to change things, they'll probably stay
the same, so you will probably get a release or two out of it.

But it is much easier to change the internal structures now, as
PostgreSQL doesn't have nearly as much of a backward compatibility issue
anymore.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
11 Jun 2003 20:01:15 +0000
Rod Taylor <rbt@rbt.ca>
Re: [GENERAL] How to enumerate foreign key constraints after