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(); |
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.
If we have a timestamp with a timezone then getting the unix time from this is simple.
Note now() returns a timestamp with timezone so this query gets the current unix time
SELECT EXTRACT(epoch FROM now()); |
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 |