Okay, all you Postgres experts who want to be helpful, here's a question for you:
I'm trying to select all revisions of nodes that have revisions other than the current one that are older than a given time and of a selected type(s).
The following query does what I need in MySql, will it work in Postgres?
SELECT r.nid, r.vid, r.timestamp, DATE(
FROM_UNIXTIME(r.timestamp)) AS 'date', n.title, n.type
FROM {node_revisions}
r INNER JOIN {node} n ON r.nid =
n.nid
WHERE n.nid IN (
SELECT
r.nid FROM {node_revisions} r
INNER JOIN {node} n ON r.nid = n.nid AND r.vid <> n.vid
WHERE r.timestamp <1248708062 AND
n.type IN ('page', 'story')
)
ORDER BY r.nid, r.timestamp
DESC
From looking at the PG docs, I see that "DATE( FROM_UNIXTIME( r.timestamp ) ) AS 'date'" probably needs to be changed to "CAST(r.timestamp AS date) AS 'date'" and I can do that in my code. The funny thing is that the MySql docs mention doing the same, but it returns a NULL.