1 Topic by Ulph

  • Ulph
  • Member
  • Offline
  • Registered: 2005-02-06
  • Posts: 30

Topic: News Generator PostgreSQL incompatibility

At line 96 in AP_News_Generator.php I suggest a change of the query to the following:

$time = mktime(0, 0, 0, 1, $month_start, $year_start);
$result = $db->query('SELECT id, subject FROM '.$db->prefix.'topics WHERE forum_id='.$forum_id.' AND posted>='.$time.' AND posted<'.$time.' ORDER BY posted DESC') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());

The reason is that UNX_TIMESTAMP() does not exist in PostgreSQL

Ulph's Website

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: News Generator PostgreSQL incompatibility

Thanks for the report, but the fix you posted won't work at all. posted can't be both bigger than $time and smaller than $time. I suggest the following fix instead:

if ($db_type == 'pgsql')
    $result = $db->query('SELECT id, subject FROM '.$db->prefix.'topics WHERE forum_id='.$forum_id.' AND posted>=DATE_PART(\'EPOCH\', \''.$year_start.'-'.$month_start.'-01\'::date) AND posted<DATE_PART(\'EPOCH\', \''.$year_end.'-'.$month_end.'-01\'::date) ORDER BY posted DESC') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());
else
    $result = $db->query('SELECT id, subject FROM '.$db->prefix.'topics WHERE forum_id='.$forum_id.' AND posted>=UNIX_TIMESTAMP(\''.$year_start.'-'.$month_start.'-01\') AND posted<UNIX_TIMESTAMP(\''.$year_end.'-'.$month_end.'-01\') ORDER BY posted DESC') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());

I currently don't have PostgreSQL installed so i can't test it. Could someone perhaps do that?

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website

3 Reply by Ulph

  • Ulph
  • Member
  • Offline
  • Registered: 2005-02-06
  • Posts: 30

Re: News Generator PostgreSQL incompatibility

Sorry. I wasn't paying attention to what the code was trying to do. That query goes through. Thanks.

Ulph's Website