[development] Will this work in Postgres?

[Nancy Wichmann]

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.


Nancy E. Wichmann, PMP

Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King,
Jr.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.drupal.org/pipermail/development/attachments/20090824/ba49060b/attachment.htm>