Problem/Motivation

Method date_sql_handler->sql_date_math() initially supports only integers in $count parameter. But some other modules (like Signup) use this method with field reference in $count. Perhaps such calls work with MySQL, but not with PgSQL. Anyway the idea is worth to be implemented.

Proposed resolution

Allow the method to work with field reference and not only integers.

Remaining tasks

Write a patch.

User interface changes

None.

API changes

Additional possibilities in date_sql_handler->sql_date_math().

Original report by @maikeru

Hi,

When using date 6.x-2.7 and signup-6.x-1.0, I receive the following error for each cron run:

query: SELECT n.title, n.nid, n.type, s.reminder_email, s.forwarding_email, field_close_date_value FROM node n
 INNER JOIN signup s ON s.nid = n.nid 
 INNER JOIN content_field_close_date ON content_field_close_date.vid = n.vid 
 WHERE (s.send_reminder = 1) 
 AND (n.type = 'event') 
 AND ('2011-03-29 05:19:36' >= (field_close_date_value - INTERVAL 's.reminder_days_before DAYS')) 
 AND ('2011-03-29 05:19:36' <= (field_close_date_value + INTERVAL '1 HOURS')) 
 in /var/vhost/www.example.com/modules/signup/includes/cron.inc on line 46.

Related issue here:
http://drupal.org/node/549018

Signup now calls sql_date_math in the date module, which has the same INTERVAL issue.

CommentFileSizeAuthor
#4 date-field-reference-interval-value-1112576-4.patch1.79 KBpingwin4eg
#1 date-postgres-interval-1112576.patch578 bytesmaikeru
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

maikeru’s picture

maikeru CreditAttribution: maikeru commented
FileSize
date-postgres-interval-1112576.patch578 bytes

See attached patch against 2.7.

John Franklin’s picture

John Franklin CreditAttribution: John Franklin commented
Status: Active » Needs review

Works here.

Status: Needs review » Needs work

The last submitted patch, date-postgres-interval-1112576.patch, failed testing.

pingwin4eg’s picture

pingwin4eg
Location Zaporizhia 🇺🇦
CreditAttribution: pingwin4eg commented
Title: Postgres Error with Signup - sql_date_math » SQL: Field reference as adjusting value of time interval
Version: 6.x-2.7 » 7.x-2.x-dev
Category: Bug report » Feature request
Priority: Normal » Major
Issue summary: View changes
Status: Needs work » Needs review
FileSize
date-field-reference-interval-value-1112576-4.patch1.79 KB

I rewrote the patch of @maikeru for Date 7.x-2.x, including doc changes.

The patch is for pgsql only. Tests needed for mysql & sqlite.

P.S.: I changed the issue tracker from bug to feature, because the Date API clearly states (in method's docblock) that the method date_sql_handler->sql_date_math() takes integer as $count parameter, so it is not its fault. But increased the priority, because other modules already use this feature.

Chris Matthews’s picture

Chris Matthews CreditAttribution: Chris Matthews commented
Status: Needs review » Needs work
Issue tags: +Needs reroll, +Needs rework

The 4 year old patch in #4 to date_api_sql.inc does not apply to the latest 7.x-2.x-dev and may be too old to reroll, but I went ahead and tagged the issue accordingly.

Checking patch date_api/date_api_sql.inc...
error: while searching for:
   *   The field to be adjusted.
   * @param string $direction
   *   Either ADD or SUB.
   * @param int $count
   *   The number of values to adjust.
   * @param string $granularity
   *   The granularity of the adjustment, should be singular,
   *   like SECOND, MINUTE, DAY, HOUR.
   */
  function sql_date_math($field, $direction, $count, $granularity) {
    $granularity = strtoupper($granularity);

error: patch failed: date_api/date_api_sql.inc:291
error: date_api/date_api_sql.inc: patch does not apply