You can do date and time arithmetic using current time functions or a timestamp column in your table. For example to add or subtract seven days try:

	select datefield + 7 from table where id=1;
	select now() + '7 days'::interval;
	select now() - '7 days'::interval;  

Contributors: Williams, Travis L, NPONS tlw at att.com, scott.marlowe scott.marlowe at ihs.com, Robert Treat xzilla at users.sourceforge.net

Recommended Reading [GENERAL] PHP and PostgreSQL 26-Oct-2002

Here are some recommendations for documentation on PHP and PostgreSQL.

Book

• PHP and PostgreSQL Advanced Web Programming by Ewald Geschwinde, published by SAMS

• http://www.devshed.com/Server_Side/PHP/PostgreSQL/page1.html
• http://www.phpbuilder.com/columns/linuxjournal200009.php3

• PHPComplete.com has PHP PostgreSQL tutorials.
• The PHP Reference manual has a good chapter on PostgreSQL interface functions.
• phpPgAdmin is a web based admin tool for PostgreSQL written completely in PHP. More info on phpPgAdmin is available at http://phppgadmin.sourceforge.net .

Oracle has a way of "pinning" a table into memory for faster access. PostgreSQL does not because general concensus is that it is not a good idea.

If the table is accessed enough, it will be in the cache anyway. If it is not, then perhaps it wasn't the best candidate for the cache and it would have the effect of making the problem worse. Pinning a table would be "just mucking with the symptoms instead of curing the [disease]".

However, it is acknowledged that the caching algorithms for PostgreSQL are due for tune-up and there are people looking at the issues now.

Contributors: David Blood david at matraex.com, Tom Lane tgl at sss.pgh.pa.us, Jan Wieck JanWieck at Yahoo.com

TCP KEEPALIVE and connectivity [GENERAL] Connection timeout 19-Oct-2002

There is no particular setting in PostgreSQL for a connection timeout. However, for TCP connections PostgreSQL enables TCP KEEPALIVE. Tom Lane says, "If network connectivity to the client is lost the kernel will time out and close the connection. The length of the keepalive timeout is kernel-dependent but should be an hour or more in RFC-compliant implementations."

Contributors: Williams, Travis L, NPONS tlw at att.com, Tom Lane tgl at sss.pgh.pa.us

Counting rows [GENERAL] count() for a select statement? 01-Oct-2002

To count the rows resulting in a selection use:

	select count(*) from (select ....) foo; 
	or
	select count(*) from ....;

But don't forget that count(*) is not a speedy function. If you must have the count and are fetching the rows for further processing, it is suggested that you count the results in the client.

Contributors: Jean-Christian Imbeault jc at mega-bucks.co.jp, Stephan Szabo sszabo at megazone23.bigpanda.com, snpe snpe at snpe.co.yu

Timezone display and storage [GENERAL] database design with timestamp 05-Nov-2002

Storing timestamps with timezones lets the database handle the timezone conversions. When you select or run a report, the data will be in the current timezone. To run a report for someone in a different timezone, you can set the timezone in SQL before running the report. You can "change" timezones to affect the output of the report or query in order to generate more meaningful reports for people in different timezones.

	area=# select current_time;
		timetz
	--------------------
 	 10:03:02.390849-06
	(1 row)

	area=# set time zone 'PST8PDT';
	SET
	area=# select current_time;
       		timetz
	--------------------
 	 08:03:19.241939-08

PostgreSQL stores time in UTC like Unix. The timezone determines the display formatting. Each "reader" of the data will see the time in their own timezone.

You also use timezones when you want to preserve the original timezone of the data. You must know or store separately the origin timezone and apply it with set time zone 'xxx'. For example, sometimes it makes more sense to be clear that something happened at commute time in Minneapolis than to know it happened early afternoon in California.

Contributors: TJ tj at nospam.com, Bruno Wolff III bruno at wolff.to, Tom Lane tgl at sss.pgh.pa.us, elein at varlena.com