<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16890" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial>
<P><FONT size=2>Okay, all you Postgres experts who want to be helpful, here's a
question for you:</FONT></P>
<P><FONT size=2>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).</FONT></P>
<P><FONT size=2>The following query does what I need in MySql, will it work in
Postgres?</FONT></P><FONT face="Times New Roman">
<P><FONT face=Courier size=2>SELECT r.nid, r.vid, r.timestamp, DATE(
FROM_UNIXTIME(r.timestamp)) AS 'date', n.title, n.type<BR>FROM {node_revisions}
r<SPAN class=156211617-24082009> </SPAN>INNER JOIN {node} n ON r.nid =
n.nid<BR>WHERE n.nid IN (<BR><SPAN class=156211617-24082009> </SPAN>SELECT
r.nid FROM {node_revisions} r<BR><SPAN class=156211617-24082009>
</SPAN>INNER JOIN {node} n ON r.nid = n.nid AND r.vid <> n.vid<BR><SPAN
class=156211617-24082009> </SPAN>WHERE r.timestamp <1248708062 AND
n.type IN ('page', 'story')</FONT></P>
<P><FONT face=Courier size=2>)<BR>ORDER BY r.nid, r.timestamp
DESC<BR></FONT><FONT face=Arial size=2></FONT></P>
<P><FONT face=Arial size=2>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.</FONT></P></FONT></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face="Comic Sans MS" color=#ff00ff size=4>Nancy E. Wichmann,
PMP</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV align=left><FONT face=Arial size=2><FONT face="Courier New">Injustice
anywhere is a threat to justice everywhere. -- Dr. Martin L. King,
Jr</FONT>.</FONT></DIV>
<DIV> </DIV></BODY></HTML>