hook_civicrm_relativeDateSql

Summary

Provide the SQL to/from expressions for relative date filters.

Relative date filters are an option list extensions can add to (typically via a managed entity file). When doing so, they should implement this hook to render the values. They then apply wherever relative dates are available in CiviCRM (Search, Reports, Mailing, and APIv4.

Availability

This hook was introduced in CiviCRM 6.20 as a replacement for the deprecated hook_civicrm_relativeDate.

Definition

hook_civicrm_relativeDateSql(string $relativeTerm, ?string $unit, array &$sql): void

Parameters

• string $relativeTerm: The relative timeframe term or token prefix (e.g. 'this', 'previous', 'previous_2', 'ending_30', 'starting'). If the option value does not contain a dot separator, the entire option name is passed here.
• ?string $unit: The date unit if specified via dot notation (e.g. 'day', 'week', 'month', 'quarter', 'year', 'fiscal_year', or a custom unit such as 'academic_year', 'semester'). If no dot separator was present in the filter token, this is NULL.
• array &$sql: Associative array passed by reference: ['from' => NULL, 'to' => NULL]. Extensions should set 'from' and 'to' to valid MySQL date/datetime expressions (e.g. "CURDATE()", "STR_TO_DATE(...)", "DATE_SUB(...)"). Either bound can remain NULL for open-ended ranges (e.g. "earlier than" or "greater than").

Returns

• void: Output expressions are assigned directly to the &$sql parameter passed by reference. If the hook does not handle the given term or unit, it should leave $sql unchanged.

How It Works: Terms and Units via Dot Notation

Relative date filter options in CiviCRM conventionally follow the format <term>.<unit> (for example, this.week, previous.month, ending_30.day).

When CiviCRM resolves a relative date: 1. CRM_Utils_Date::relativeToSql() inspects the filter name. 2. If the filter name contains a dot (.), it is split into: - $relativeTerm: the string before the dot (e.g., 'this', 'previous'). - $unit: the string after the dot (e.g., 'academic_year', 'semester'). 3. If no dot is present (e.g., 'custom_deadline'), the entire string is passed as $relativeTerm, and $unit is NULL. 4. hook_civicrm_relativeDateSql is invoked with $relativeTerm, $unit, and &$sql. 5. If the hook populates $sql['from'] or $sql['to'], CiviCRM uses those SQL expressions. If $sql is left empty, CiviCRM falls back to its built-in terms and units.

This dot separation allows your extension to handle multiple terms (e.g., this, previous) across custom units (e.g., academic_year, semester) with clean, modular logic.

Full Working Example

In this example, an extension called academicdates defines two custom date units: - academic_year: Runs September 1 to August 31. - semester: Fall semester runs September 1 to January 31; Spring semester runs February 1 to August 31.

The extension supports two terms for each unit: this and previous.

1. Register Option Values (managed/OptionValue_academic_dates.mgd.php)

To make the options appear in UI dropdowns, SearchKit, Reports, and APIv4, add the records to the relative_date_filters option group using a managed entity file:

<?php
use CRM_Academicdates_ExtensionUtil as E;

return [
  [
    'name' => 'OptionValue_relative_date_filters_this_academic_year',
    'entity' => 'OptionValue',
    'params' => [
      'version' => 4,
      'values' => [
        'option_group_id.name' => 'relative_date_filters',
        'label' => E::ts('Current Academic Year'),
        'value' => 'this.academic_year',
        'name' => 'this.academic_year',
      ],
    ],
  ],
  [
    'name' => 'OptionValue_relative_date_filters_previous_academic_year',
    'entity' => 'OptionValue',
    'params' => [
      'version' => 4,
      'values' => [
        'option_group_id.name' => 'relative_date_filters',
        'label' => E::ts('Previous Academic Year'),
        'value' => 'previous.academic_year',
        'name' => 'previous.academic_year',
      ],
    ],
  ],
  [
    'name' => 'OptionValue_relative_date_filters_this_semester',
    'entity' => 'OptionValue',
    'params' => [
      'version' => 4,
      'values' => [
        'option_group_id.name' => 'relative_date_filters',
        'label' => E::ts('Current Academic Semester'),
        'value' => 'this.semester',
        'name' => 'this.semester',
      ],
    ],
  ],
  [
    'name' => 'OptionValue_relative_date_filters_previous_semester',
    'entity' => 'OptionValue',
    'params' => [
      'version' => 4,
      'values' => [
        'option_group_id.name' => 'relative_date_filters',
        'label' => E::ts('Previous Academic Semester'),
        'value' => 'previous.semester',
        'name' => 'previous.semester',
      ],
    ],
  ],
];

2. Implement the Hook (academicdates.php)

In your extension's main PHP file or hook listener class, implement hook_civicrm_relativeDateSql:

<?php

/**
 * Implements hook_civicrm_relativeDateSql().
 *
 * Provides SQL expressions for custom academic year and semester relative dates.
 *
 * @param string $relativeTerm
 *   The term part (e.g. 'this', 'previous').
 * @param string|null $unit
 *   The unit part (e.g. 'academic_year', 'semester').
 * @param array $sql
 *   Output expressions: ['from' => string|null, 'to' => string|null] passed by reference.
 */
function academicdates_civicrm_relativeDateSql(string $relativeTerm, ?string $unit, array &$sql): void {
  // Only handle units registered by this extension
  if (!in_array($unit, ['academic_year', 'semester'], TRUE)) {
    return;
  }

  // Handle the 'academic_year' unit (September 1 - August 31)
  if ($unit === 'academic_year') {
    // Current academic start year: If month >= 9, start year is this year; otherwise last year
    $startYear = "IF(MONTH(CURDATE()) >= 9, YEAR(CURDATE()), YEAR(CURDATE()) - 1)";

    switch ($relativeTerm) {
      case 'this':
        $sql = [
          'from' => "STR_TO_DATE(CONCAT($startYear, '-09-01'), '%Y-%m-%d')",
          'to' => "STR_TO_DATE(CONCAT($startYear + 1, '-08-31 23:59:59'), '%Y-%m-%d %H:%i:%s')",
        ];
        break;

      case 'previous':
        $sql = [
          'from' => "STR_TO_DATE(CONCAT($startYear - 1, '-09-01'), '%Y-%m-%d')",
          'to' => "STR_TO_DATE(CONCAT($startYear, '-08-31 23:59:59'), '%Y-%m-%d %H:%i:%s')",
        ];
        break;
    }
  }

  // Handle the 'semester' unit (Fall: Sep 1 - Jan 31; Spring: Feb 1 - Aug 31)
  elseif ($unit === 'semester') {
    switch ($relativeTerm) {
      case 'this':
        $sql = [
          'from' => "IF(MONTH(CURDATE()) BETWEEN 2 AND 8,
            STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-02-01'), '%Y-%m-%d'),
            STR_TO_DATE(CONCAT(IF(MONTH(CURDATE()) >= 9, YEAR(CURDATE()), YEAR(CURDATE()) - 1), '-09-01'), '%Y-%m-%d')
          )",
          'to' => "IF(MONTH(CURDATE()) BETWEEN 2 AND 8,
            STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-08-31 23:59:59'), '%Y-%m-%d %H:%i:%s'),
            STR_TO_DATE(CONCAT(IF(MONTH(CURDATE()) >= 9, YEAR(CURDATE()) + 1, YEAR(CURDATE())), '-01-31 23:59:59'), '%Y-%m-%d %H:%i:%s')
          )",
        ];
        break;

      case 'previous':
        $sql = [
          'from' => "IF(MONTH(CURDATE()) BETWEEN 2 AND 8,
            STR_TO_DATE(CONCAT(IF(MONTH(CURDATE()) >= 9, YEAR(CURDATE()), YEAR(CURDATE()) - 1), '-09-01'), '%Y-%m-%d'),
            STR_TO_DATE(CONCAT(IF(MONTH(CURDATE()) >= 9, YEAR(CURDATE()), YEAR(CURDATE()) - 1), '-02-01'), '%Y-%m-%d')
          )",
          'to' => "IF(MONTH(CURDATE()) BETWEEN 2 AND 8,
            STR_TO_DATE(CONCAT(YEAR(CURDATE()), '-01-31 23:59:59'), '%Y-%m-%d %H:%i:%s'),
            STR_TO_DATE(CONCAT(IF(MONTH(CURDATE()) >= 9, YEAR(CURDATE()), YEAR(CURDATE()) - 1), '-08-31 23:59:59'), '%Y-%m-%d %H:%i:%s')
          )",
        ];
        break;
    }
  }
}

See Also

• hook_civicrm_relativeDate (Deprecated)
• Managed Entities
• Relative Date Formats (User Guide)