function date_sql in Date 5

Cross-database date SQL wrapper function allows use of normalized native date functions in both mysql and postgres. Designed to be extensible to other databases.

Parameters

$result_type - NOW, DATE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW, DOY, WEEK: @param $field - the name of the date field to be analyzed @param $date_type - the type of date field being analyzed, int or iso @param $offset - timezone offset in seconds, can be either a value or fieldname @param $offset_op - the operation to perform on the offset, + or - @return a SQL statement appropriate for the $db_type

example: date_sql('WEEK', 'MYFIELD', 'int', 'MYOFFSETFIELD', '+') mysql returns: WEEK(FROM_UNIXTIME(MYFIELD) + INTERVAL MYOFFSETFIELD SECOND, 3) postgres returns: EXTRACT(WEEK FROM(TIMESTAMP(MYFIELD::ABSTIME::INT4) + INTERVAL MYOFFSETFIELD SECONDS))

3 calls to date_sql()

_date_views_argument_range_handler in ./date_views.inc

_date_views_filter_handler in ./date_views.inc

_date_views_query_alter in ./date_views.inc

Implementation of hook_views_query() Used to make sure view defaults to current date if no date selected

File

./date.inc, line 2032

Date/time API functions

Code

function date_sql($result_type, $field, $date_type = 'int', $offset = '', $offset_op = '+') {
  global $db_type;

  // NOW() is timezone-adjusted by OS, adjust only for the server adj,
  // correct offset will get added back in later step.
  if ($date_type == 'NOW' || $field == 'NOW()') {
    switch ($db_type) {
      case 'mysql':
      case 'mysqli':
        if (date_server_zone_adj()) {
          $field = "(NOW() - INTERVAL " . date_server_zone_adj() . " SECOND)";
        }
        break;
      case 'pgsql':
        if (date_server_zone_adj()) {
          $field = "(NOW() - INTERVAL '" . date_server_zone_adj() . " SECONDS')";
        }
        break;
    }
  }
  elseif ($date_type == 'int' && $field) {
    switch ($db_type) {
      case 'mysql':
      case 'mysqli':
        $field = "FROM_UNIXTIME({$field})";
        if (date_server_zone_adj()) {
          $field = "({$field} - INTERVAL " . date_server_zone_adj() . " SECOND)";
        }
        break;
      case 'pgsql':
        $field = "({$field}::ABSTIME)";
        if (date_server_zone_adj()) {
          $field = "({$field} - INTERVAL '" . date_server_zone_adj() . " SECONDS')";
        }
        break;
    }
  }
  elseif ($date_type == 'iso' && $field) {
    $field = " REPLACE({$field},'T',' ')";
  }

  // Now apply requested offset to the adjusted query field.
  if ($offset) {
    switch ($db_type) {
      case 'mysql':
      case 'mysqli':
        $field = "({$field} {$offset_op} INTERVAL ({$offset}) SECOND)";
        break;
      case 'pgsql':
        $field = "({$field} {$offset_op} INTERVAL '{$offset} SECONDS')";
        break;
    }
  }

  // Return requested sql.
  // Note there is no space after FROM to avoid db_rewrite problems
  // see http://drupal.org/node/79904.
  switch ($result_type) {
    case 'NOW':
    case 'DATE':
      return $field;
    case 'YEAR':
      return "EXTRACT(YEAR FROM({$field}))";
    case 'MONTH':
      return "EXTRACT(MONTH FROM({$field}))";
    case 'DAY':
      return "EXTRACT(DAY FROM({$field}))";
    case 'HOUR':
      return "EXTRACT(HOUR FROM({$field}))";
    case 'MINUTE':
      return "EXTRACT(MINUTE FROM({$field}))";
    case 'SECOND':
      return "EXTRACT(SECOND FROM({$field}))";
    case 'WEEK':

      // ISO week number for date
      switch ($db_type) {
        case 'mysql':
        case 'mysqli':

          // WEEK using arg 3 in mysql should return the same value as postgres EXTRACT
          return "WEEK({$field}, 3)";
        case 'pgsql':
          return "EXTRACT(WEEK FROM({$field}))";
      }
    case 'DOW':
      switch ($db_type) {
        case 'mysql':
        case 'mysqli':

          // mysql returns 1 for Sunday through 7 for Saturday
          // php date functions and postgres use 0 for Sunday and 6 for Saturday
          return "INTEGER(DAYOFWEEK({$field}) - 1)";
        case 'pgsql':
          return "EXTRACT (DOW FROM({$field}))";
      }
    case 'DOY':
      switch ($db_type) {
        case 'mysql':
        case 'mysqli':
          return "DAYOFYEAR({$field})";
        case 'pgsql':
          return "EXTRACT (DOY FROM ({$field}))";
      }
  }
}