Monthly Archive Broken with PostgreSQL

Note: This bug is similar to one that appears in Weekly Node module.

The following code does not work with PostgreSQL, since it doesn't have the function from_unixtime().

...
<?php
  $query = 'SELECT DATE_FORMAT(FROM_UNIXTIME(created),\'%Y-%m\') AS monthno,COUNT(*) AS nodenum FROM {node} n ';
  $query .= 'WHERE n.created>0 AND (n.status=1) AND n.type IN (' . implode(',', $nodes_to_include) . ') ';
  $query .=' GROUP BY monthno ';
  $query .= $rev_chronsort ? '' : 'ORDER BY monthno DESC ';
  $query .= $limit > 0 ? "LIMIT $limit" : '';
  return $query;
?>
...

I suggest moving the date conversion out of the SQL query and handling it in PHP (with the date() function). Once possible fix might look something like this.

...
<?php
function month_links_query($nodes_to_include, $rev_chronsort = NULL, $limit = NULL) {
  foreach ($nodes_to_include as $key => $value) {
    $nodes_to_include[$key] = "'". db_escape_string($value) . "'";
  }
  $query = 'SELECT created, COUNT(*) AS nodenum FROM {node} n ';
  $query .= 'WHERE n.created>0 AND (n.status=1) AND n.type IN (' . implode(',', $nodes_to_include) . ') ';
  $query .= ' GROUP BY created ';
  $query .= $rev_chronsort ? '' : 'ORDER BY created DESC ';
  $query .= $limit > 0 ? "LIMIT $limit" : '';
  return $query;
}

?>
...

...
<?php
function month_block($op='list', $delta=0, $edit=array()) {
  $_month_config = variable_get('month_config', array());

  switch ($op) {
    // listing of blocks, such as on the admin/system/block page
    case 'list': {
      $block[0]['info'] = t('Monthly Archives');
      return $block;
    }
    case 'view': {
        // our block content
        // plus one is the easiest way to find out if you need a "more" link
        $block['subject'] = t('Monthly Archives');
        if($_month_config['month_include_node_type']) 
        {
            $month_max_block_links = $_month_config['month_block_link_max'] + 1;
            $query = month_links_query($_month_config['month_include_node_type'],
                                       $_month_config['month_rev_chronsort'],
                                       $month_max_block_links);
                                       
            $monthlist = db_query(db_rewrite_sql($query, 'n', 'nid'));
        
            if (db_num_rows($monthlist) >= $month_max_block_links) 
            {
                $morelink = '<div class="more-link"><a href="' . 
                            base_path() . 
                            'month">more...</a></div>';
            }
            $block_content = array(); $listed_links = 0;
            
            while (($listed_links < $_month_config['month_block_link_max']) && $month = db_fetch_object($monthlist)) 
            {
                $listed_links += 1;
                $month_start = $month->created;
                $block_content[] = l(date('F Y',$month_start).' ('.$month->nodenum.')', 'month/'.date('Y/m',$month_start));
            }

?>
...

From the few tests that I've done, this seems to work.