0001738: [PostgreSQL/SQlite] Tools history page returns database error

Piwigo Bugtracker

Viewing Issue Advanced Details [ Jump to Notes ]

[ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0001738

[Piwigo] other

minor

always

2010.06.21 23:58

2010.06.30 00:00

Reporter

madman

View Status

public

Assigned To

nikrou

Priority

normal

Resolution

fixed

Platform

Postgresql

Status

closed

CentOS 5.4

Projection

none

OS Version

Postgresql 8.4.2

ETA

none

Fixed in Version

2.1.2

Product Version

2.1.1

Target Version

Product Build

Trunk r6565

Summary

0001738: [PostgreSQL/SQlite] Tools history page returns database error

Description

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
;';

Steps To Reproduce

New installation -> Administration -> Tools -> History

Additional Information

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.

Tags

No tags attached.

browser

Mozilla

Database engine and version

Postgresql 8.4.2

PHP version

5.3.2

Web server

Apache 2.2.3

Attached Files

Relationships

Notes
(0003987) svn (reporter) 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 (reporter) 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.

Issue History
Date Modified	Username	Field	Change
2010.06.21 23:58	madman	New Issue
2010.06.21 23:58	madman	browser	=> Mozilla
2010.06.21 23:58	madman	MySQL version	=> Postgresql 8.4.2
2010.06.21 23:58	madman	PHP version	=> 5.3.2
2010.06.21 23:58	madman	Web server	=> Apache 2.2.3
2010.06.22 09:20	plg	Status	new => assigned
2010.06.22 09:20	plg	Assigned To	=> nikrou
2010.06.22 23:01	svn	Checkin
2010.06.22 23:01	svn	Note Added: 0003987
2010.06.22 23:01	svn	Checkin
2010.06.22 23:01	svn	Note Added: 0003988
2010.06.22 23:02	nikrou	Status	assigned => closed
2010.06.22 23:02	nikrou	Resolution	open => fixed
2010.06.22 23:02	nikrou	Fixed in Version	=> 2.1.2
2010.06.30 00:00	plg	Summary	Tools history page returns database error => [PostgreSQL/SQlite] Tools history page returns database error
2010.06.30 00:00	plg	Additional Information Updated

Mantis 1.1.6[^]

Contact