Java PostgreSQL timestamp to a Unix timestamp

Suppose we have a timestamp in the database and would like to convert it to a Unix timestamp
There is a function called getTime() that will return the unix time.

long messageTime = rs.getTimestamp("startTime", Calendar.getInstance(TimeZone.getTimeZone("UTC"))).getTime();

PostgreSQL getting the lastest item from a history table (the entry with the latest timestamp)

SELECT * FROM history_items
WHERE timeofhistory=(SELECT MAX(timeofhistory) FROM history_items)

The sub query will fetch the highest timestamp and then that is used as a condition for the query.

note: If you are using a version older than 8.1, then MIN and MAX is not able to use indexes, so instead use a ORDER BY and LIMIT clause

SELECT * FROM history_items
ORDER BY timeofhistory DESC LIMIT 1

This will of course work for versions 8.1 and higher as well, it should be identical to MAX and MIN in time.

PostgreSQL 8.3 adding uuid-ossp on Debian

UUID is a nice way to generate (almost) unique id’s.

However on PostgreSQL 8.3 the data type exists, but if you wish to be able to generate UUID’s you need to get a module (in this case uuid-ossp).

On Debian this module is in the contrib package, so if you have not installed this before do it now

user@server:~$ sudo apt-get install postgresql-contrib

Then you need to “load” the module into PostgreSQL; or more accurately you tell PostgreSQL about the new functions this module has, how to reach them etc.
Depending on you security settings (in pg_hba.conf) you may need to log in as the postgres user on your system.

postgres@sever:~$ psql -d MYDB -U postgres -f /usr/share/postgresql/8.3/contrib/uuid-ossp.sql