MantisBT - Piwigo
View Issue Details
0001738Piwigootherpublic2010.06.21 23:582010.06.30 00:00
madman 
nikrou 
normalminoralways
closedfixed 
PostgresqlCentOS 5.4Postgresql 8.4.2
2.1.1 
2.1.2 
Mozilla
Postgresql 8.4.2
5.3.2
Apache 2.2.3
0001738: [PostgreSQL/SQlite] Tools history page returns database error
See the error below; PostgreSQL does not have an hour() fuction. Instead the extract() function should be used, this is also supported by MySQL: http://www.java2s.com/Tutorial/MySQL/0280__Date-Time-Functions/SELECTEXTRACTHOURFROM20030102103000000123.htm [^]

The query in admin/stats.php on line 116 should be rewritten:
$query = '
SELECT
    date,
    extract(hour from time) AS hour,
    MAX(id) AS max_id,
    COUNT(*) AS nb_pages
  FROM '.HISTORY_TABLE.'
  WHERE summarized = \'false\'
  GROUP BY
    date,
    hour
  ORDER BY
    date ASC,
    hour ASC
;';
New installation -> Administration -> Tools -> History
Warning: pg_query() [function.pg-query]: Query failed: ERROR: function hour(time without time zone) does not exist LINE 4: hour(time) AS hour, ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. in /var/www/sites/fotos.aarjan.nl/piwigo/piwigo_svn/include/dblayer/functions_pgsql.inc.php on line 122
SELECT date, hour(time) AS hour, MAX(id) AS max_id, COUNT(*) AS nb_pages FROM piwigo_history WHERE summarized = 'false' GROUP BY date, hour ORDER BY date ASC, hour ASC ;
ERROR: function hour(time without time zone) does not exist LINE 4: hour(time) AS hour, ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
No tags attached.
Issue History
2010.06.21 23:58madmanNew Issue
2010.06.21 23:58madmanbrowser => Mozilla
2010.06.21 23:58madmanMySQL version => Postgresql 8.4.2
2010.06.21 23:58madmanPHP version => 5.3.2
2010.06.21 23:58madmanWeb server => Apache 2.2.3
2010.06.22 09:20plgStatusnew => assigned
2010.06.22 09:20plgAssigned To => nikrou
2010.06.22 23:01svnCheckin
2010.06.22 23:01svnNote Added: 0003987
2010.06.22 23:01svnCheckin
2010.06.22 23:01svnNote Added: 0003988
2010.06.22 23:02nikrouStatusassigned => closed
2010.06.22 23:02nikrouResolutionopen => fixed
2010.06.22 23:02nikrouFixed in Version => 2.1.2
2010.06.30 00:00plgSummaryTools history page returns database error => [PostgreSQL/SQlite] Tools history page returns database error
2010.06.30 00:00plgAdditional Information Updated

Notes
(0003987)
svn   
2010.06.22 23:01   
[Subversion] r6578 by nikrou on trunk

-----[Subversion commit log]----------------------------------------------------
Bug 1738 fixed : hour function doesn't exists for database other than mysql.
(0003988)
svn   
2010.06.22 23:01   
[Subversion] r6579 by nikrou on branch 2.1

-----[Subversion commit log]----------------------------------------------------
Bug 1738 fixed : merge from trunk
hour function doesn't exists for database other than mysql.