Helper function to create cross-database SQL date formatting.

Parameters

$format: A format string for the result, like 'Y-m-d H:i:s'.

$field: The real table and field name, like 'tablename.fieldname'.

$field_type: The type of date field, 'int' or 'datetime'.

$set_offset: The name of a field that holds the timezone offset or a fixed timezone offset value. If not provided, the normal Drupal timezone handling will be used, i.e. $set_offset = 0 will make no timezone adjustment.

Return value

An appropriate SQL string for the db type and field type.

File

includes/handlers.inc, line 1283

Defines the various handler objects to help build and display views.

Code

function views_date_sql_format($format, $field, $field_type = 'int', $set_offset = NULL) {
  $db_type = Database::getConnection()->databaseType();
  $field = views_date_sql_field($field, $field_type, $set_offset);
  switch ($db_type) {
    case 'mysql':
      $replace = array(
        'Y' => '%Y',
        'y' => '%y',
        'M' => '%b',
        'm' => '%m',
        'n' => '%c',
        'F' => '%M',
        'D' => '%a',
        'd' => '%d',
        'l' => '%W',
        'j' => '%e',
        'W' => '%v',
        'H' => '%H',
        'h' => '%h',
        'i' => '%i',
        's' => '%s',
        'A' => '%p',
      );
      $format = strtr($format, $replace);
      return "DATE_FORMAT($field, '$format')";
    case 'pgsql':
      $replace = array(
        'Y' => 'YYYY',
        'y' => 'YY',
        'M' => 'Mon',
        'm' => 'MM',
        'n' => 'MM', // no format for Numeric representation of a month, without leading zeros
        'F' => 'Month',
        'D' => 'Dy',
        'd' => 'DD',
        'l' => 'Day',
        'j' => 'DD', // no format for Day of the month without leading zeros
        'W' => 'WW',
        'H' => 'HH24',
        'h' => 'HH12',
        'i' => 'MI',
        's' => 'SS',
        'A' => 'AM',
      );
      $format = strtr($format, $replace);
      return "TO_CHAR($field, '$format')";
    case 'sqlite':
      $replace = array(
        'Y' => '%Y', // 4 digit year number
        'y' => '%Y', // no format for 2 digit year number
        'M' => '%m', // no format for 3 letter month name
        'm' => '%m', // month number with leading zeros
        'n' => '%m', // no format for month number without leading zeros
        'F' => '%m', // no format for full month name
        'D' => '%d', // no format for 3 letter day name
        'd' => '%d', // day of month number with leading zeros
        'l' => '%d', // no format for full day name
        'j' => '%d', // no format for day of month number without leading zeros
        'W' => '%W', // ISO week number
        'H' => '%H', // 24 hour hour with leading zeros
        'h' => '%H', // no format for 12 hour hour with leading zeros
        'i' => '%M', // minutes with leading zeros
        's' => '%S', // seconds with leading zeros
        'A' => '', // no format for  AM/PM
      );
      $format = strtr($format, $replace);
      return "strftime('$format', $field, 'unixepoch')";
  }
}