pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to figure out when was a table created
5+ messages / 4 participants
[nested] [flat]
* How to figure out when was a table created
@ 2003-09-29 21:30 David B <postgresql@thegatelys.com>
2003-10-02 23:01 ` How to figure out when was a table created David B <postgresql@thegatelys.com>
0 siblings, 1 reply; 5+ messages in thread
From: David B @ 2003-09-29 21:30 UTC (permalink / raw)
To: pgsql-sql
Folks,
I have a list of tables for which I want to get the date they were
created...and if possible the date last updateded.
I suspect there is a pg_??? table that can answer this question but I don't
know what it is and I cannot find it mentioned in any docs.
Any suggestions...tia
-D
p.s. Love this forum!
^ permalink raw reply [nested|flat] 5+ messages in thread
* How to figure out when was a table created
2003-09-29 21:30 How to figure out when was a table created David B <postgresql@thegatelys.com>
@ 2003-10-02 23:01 ` David B <postgresql@thegatelys.com>
2003-10-03 06:33 ` Re: How to figure out when was a table created achill@matrix.gatewaynet.com
2003-10-03 11:54 ` Re: How to figure out when was a table created Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 5+ messages in thread
From: David B @ 2003-10-02 23:01 UTC (permalink / raw)
To: pgsql-sql
Hi folks,
I posted this question a few days ago and got no response so I guess it
cannot be done (surprising!)
So that leaves me with my business problem.
We create a table for each days activity.
After N days (typically 7 days) we can drop the table.
The table name is not known so cannot force business to make tablename
something like mydata_MMDDYY
I'd like to be able to do something like:
SELECT tablename
FROM pg_???
WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7 days',
'dd/mm/yy' )
Any suggestions?
--- Prior msg was:
Folks,
I have a list of tables for which I want to get the date they were
created...and if possible the date last updateded.
I suspect there is a pg_??? table that can answer this question but I don't
know what it is and I cannot find it mentioned in any docs.
Any suggestions...tia
-D
p.s. Love this forum!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to figure out when was a table created
2003-09-29 21:30 How to figure out when was a table created David B <postgresql@thegatelys.com>
2003-10-02 23:01 ` How to figure out when was a table created David B <postgresql@thegatelys.com>
@ 2003-10-03 06:33 ` achill@matrix.gatewaynet.com
2003-10-03 12:12 ` Re: How to figure out when was a table created btober@seaworthysys.com
1 sibling, 1 reply; 5+ messages in thread
From: achill@matrix.gatewaynet.com @ 2003-10-03 06:33 UTC (permalink / raw)
To: David B <postgresql@thegatelys.com>; +Cc: pgsql-sql
Well, in certain filesystems you can have the birth time
(like ufs2) stored in the inode struct.
So you find the file name in your $PGDATA/base directory
using the oid of your table (in pg_class),
and then you open that file with stat (2) or utimes (2) (or
from perl) to read creation data.
All that apply for FreeBSD, see if creation time is supported
in ext2/3.
On Thu, 2 Oct 2003, David B wrote:
> Hi folks,
>
> I posted this question a few days ago and got no response so I guess it
> cannot be done (surprising!)
> So that leaves me with my business problem.
>
> We create a table for each days activity.
> After N days (typically 7 days) we can drop the table.
> The table name is not known so cannot force business to make tablename
> something like mydata_MMDDYY
>
> I'd like to be able to do something like:
> SELECT tablename
> FROM pg_???
> WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7 days',
> 'dd/mm/yy' )
>
> Any suggestions?
>
> --- Prior msg was:
>
> Folks,
>
> I have a list of tables for which I want to get the date they were
> created...and if possible the date last updateded.
>
> I suspect there is a pg_??? table that can answer this question but I don't
> know what it is and I cannot find it mentioned in any docs.
>
> Any suggestions...tia
> -D
>
> p.s. Love this forum!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to figure out when was a table created
2003-09-29 21:30 How to figure out when was a table created David B <postgresql@thegatelys.com>
2003-10-02 23:01 ` How to figure out when was a table created David B <postgresql@thegatelys.com>
2003-10-03 06:33 ` Re: How to figure out when was a table created achill@matrix.gatewaynet.com
@ 2003-10-03 12:12 ` btober@seaworthysys.com
0 siblings, 0 replies; 5+ messages in thread
From: btober@seaworthysys.com @ 2003-10-03 12:12 UTC (permalink / raw)
To: achill@matrix.gatewaynet.com; +Cc: postgresql@thegatelys.com; pgsql-sql
Can you add two columns to the table creation definition, one with a
default current timestamp, the second to be updated with current time in
an on update trigger. Then perhaps you could do something like
SELECT
min(new_insert_timestamp_column),
max(new_update_timestamp_column) FROM your_table;
to get the table-wide date of first entry and last update.
~Berend Tober
>
> Well, in certain filesystems you can have the birth time
> (like ufs2) stored in the inode struct.
>
> So you find the file name in your $PGDATA/base directory
> using the oid of your table (in pg_class),
> and then you open that file with stat (2) or utimes (2) (or
> from perl) to read creation data.
>
> All that apply for FreeBSD, see if creation time is supported
> in ext2/3.
>
> On Thu, 2 Oct 2003, David B wrote:
>
>> Hi folks,
>>
>> I posted this question a few days ago and got no response so I guess
>> it cannot be done (surprising!)
>> So that leaves me with my business problem.
>>
>> We create a table for each days activity.
>> After N days (typically 7 days) we can drop the table.
>> The table name is not known so cannot force business to make
>> tablename something like mydata_MMDDYY
>>
>> I'd like to be able to do something like:
>> SELECT tablename
>> FROM pg_???
>> WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7
>> days',
>> 'dd/mm/yy' )
>>
>> Any suggestions?
>>
>> --- Prior msg was:
>>
>> Folks,
>>
>> I have a list of tables for which I want to get the date they were
>> created...and if possible the date last updateded.
>>
>> I suspect there is a pg_??? table that can answer this question but I
>> don't know what it is and I cannot find it mentioned in any docs.
>>
>> Any suggestions...tia
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to figure out when was a table created
2003-09-29 21:30 How to figure out when was a table created David B <postgresql@thegatelys.com>
2003-10-02 23:01 ` How to figure out when was a table created David B <postgresql@thegatelys.com>
@ 2003-10-03 11:54 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2003-10-03 11:54 UTC (permalink / raw)
To: David B <postgresql@thegatelys.com>; +Cc: pgsql-sql
On Thu, Oct 02, 2003 at 16:01:16 -0700,
David B <postgresql@thegatelys.com> wrote:
> Hi folks,
>
> I posted this question a few days ago and got no response so I guess it
> cannot be done (surprising!)
> So that leaves me with my business problem.
>
> We create a table for each days activity.
> After N days (typically 7 days) we can drop the table.
> The table name is not known so cannot force business to make tablename
> something like mydata_MMDDYY
>
> I'd like to be able to do something like:
> SELECT tablename
> FROM pg_???
> WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7 days',
> 'dd/mm/yy' )
>
> Any suggestions?
You could consider changing the way you do things. You might be able to
accomplish your goal by keeping the data in one table and keeping
partial indexes on the table corresponding to the last seven days.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-10-03 12:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-29 21:30 How to figure out when was a table created David B <postgresql@thegatelys.com>
2003-10-02 23:01 ` David B <postgresql@thegatelys.com>
2003-10-03 06:33 ` achill@matrix.gatewaynet.com
2003-10-03 12:12 ` btober@seaworthysys.com
2003-10-03 11:54 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox