Monthly Archive Broken with PostgreSQL
anolson - November 3, 2006 - 14:37
| Project: | Monthly Archive |
| Version: | 4.7.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | wmostrey |
| Status: | duplicate |
Jump to:
Description
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.
#1
The reason DATE_FORMAT(FROM_UNIXTIME(created),\'%Y-%m\') is used, is because all items of that month are then grouped together. If you don't, and just selected created, you will get this:
April (1)
April (1)
April (1)
Instead of
April (3)
I will ook into this further though, I'll see what functions both MySQL and PostgreSQL have, or how this can be fixed in PHP while still keeping a clean code. Thanks for pointing this out!
#2
Look for epoch in PostgreSQL documentation.
http://www.postgresql.org/docs/7.4/interactive/functions-datetime.htmlepoch
For date and timestamp values, the number of seconds since 1970-01-01 00:00:00-00 (can be negative); for interval values, the total number of seconds in the interval
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
Result: 982384720
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800
#3
Duplicate of #150721 which is now resolved.
#4