Thread: Re: fomatting an interval (resend)
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)
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)
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.
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 >
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.
-----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-----
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. > >
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?)
-----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-----
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"
>> 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;
>> 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;
-----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-----
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?
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
Re: How to enumerate foreign key constraints after migrating from 7.1.3?
From
Forest Wilkinson
Date:
>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?
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.
Re: How to enumerate foreign key constraints after migrating from 7.1.3?
From
Forest Wilkinson
Date:
>> 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.