When enabling calendar block for the first time, SQL error found like this:

* warning: pg_query(): Query failed: ERROR: invalid input syntax for type reltime: "INTERVAL 28800 SECONDS" in /srv/www/web7/web/drupal6/includes/database.pgsql.inc on line 138.
* user warning: query: SELECT node.nid AS nid, node.changed AS node_changed, node.title AS node_title, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format, node.type AS node_type FROM (SELECT DISTINCT ON (nid) * FROM node) node LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE ((node.changed::ABSTIME + 'INTERVAL 28800 SECONDS') >= '2008-08-01 00:00:00' AND (node.changed::ABSTIME + 'INTERVAL 28800 SECONDS') <= '2008-08-31 23:59:59') in /srv/www/web7/web/drupal6/modules/views/includes/view.inc on line 681.
* warning: pg_query(): Query failed: ERROR: invalid input syntax for type reltime: "INTERVAL 28800 SECONDS" in /srv/www/web7/web/drupal6/includes/database.pgsql.inc on line 138.
* user warning: query: SELECT node.nid AS nid, node.changed AS node_changed, node.title AS node_title, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format, node.type AS node_type FROM (SELECT DISTINCT ON (nid) * FROM node) node LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE ((node.changed::ABSTIME + 'INTERVAL 28800 SECONDS') >= '2008-08-01 00:00:00' AND (node.changed::ABSTIME + 'INTERVAL 28800 SECONDS') <= '2008-08-31 23:59:59') in /srv/www/web7/web/drupal6/modules/views/includes/view.inc on line 681.

Date module: 6.x-2.0-beta4
Calendar module: 6.x-2.0-beta4
Drupal version: 6.3
PostgreSQL: 8.1.11

It seems working fine after modifying date_api_sql.inc under modules/date:
Line 193:
Change:
return "($field + 'INTERVAL $offset SECONDS')";;
to:
return "($field + INTERVAL '$offset SECONDS')";;

CommentFileSizeAuthor
#6 interval_pgsql_fix.patch569 bytesmikeprinsloo

Comments

karens’s picture

karens commented
Status: Active » Postponed (maintainer needs more info)

If you're having trouble in beta4, the first thing to try is upgrading to the latest -dev version to see if the problem has since been fixed. If you still have a problem there, report back.

thinkact’s picture

thinkact commented
Version: 6.x-2.0-beta4 » 6.x-2.0-rc1

I'm sorry that I do not have the chance to test the -dev version. However, it still exists in 6.x-2.0-rc1 version.

mikeprinsloo’s picture

mikeprinsloo commented
Version: 6.x-2.0-rc1 » 6.x-2.0-rc2

This problem still persists in 6.x-2.0-rc2 and I have just checked line 193 of date_api_sql.inc in the latest -dev version and, concluded that the problem will most likely exist in -dev as well. This issue can be replicated by installing rc1 version of Calendar module, enabling the calendar view in Views, and then by accessing the view. The solution as described by naminmo worked for me.

mikeprinsloo’s picture

mikeprinsloo commented

I have just tested this fix with MySQL and it doesn't seem to break anything. The following URLs have some useful information:

http://www.the-art-of-web.com/sql/postgres-mysql/
http://www.postgresql.org/docs/current/static/functions-datetime.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

mikeprinsloo’s picture

mikeprinsloo commented

I see now that the problem line isn't executed when using MySQL so the message above is irrelevant.

mikeprinsloo’s picture

mikeprinsloo commented
Status: Postponed (maintainer needs more info) » Needs work
StatusFileSize
new interval_pgsql_fix.patch569 bytes

Here's a patch to fix this issue, tested on both the -rc2 version as well as the 2008-Aug-26 -dev version without any problems.

karens’s picture

karens commented
Status: Needs work » Closed (duplicate)

Then this is the same as #300319: function sql_offset() has incorrect postgresql implementation, which I just fixed.