function views_date_sql_extract in Views (for Drupal 7) 7.3

Same name and namespace in other branches

1. 6.3 includes/handlers.inc \views_date_sql_extract()
2. 6.2 includes/handlers.inc \views_date_sql_extract()

Helper function to create cross-database SQL date extraction.

Parameters

string $extract_type: The type of value to extract from the date, like 'MONTH'.

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

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

string $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

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

4 calls to views_date_sql_extract()

views_handler_argument_node_created_day::construct in modules/node/views_handler_argument_dates_various.inc

Views handlers use a special construct function.

views_handler_argument_node_created_month::construct in modules/node/views_handler_argument_dates_various.inc

Views handlers use a special construct function.

views_handler_argument_node_created_week::construct in modules/node/views_handler_argument_dates_various.inc

Views handlers use a special construct function.

views_handler_argument_node_created_year::construct in modules/node/views_handler_argument_dates_various.inc

Views handlers use a special construct function.

File

includes/handlers.inc, line 1462

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

Code

function views_date_sql_extract($extract_type, $field, $field_type = 'int', $set_offset = NULL) {
  $db_type = Database::getConnection()
    ->databaseType();
  $field = views_date_sql_field($field, $field_type, $set_offset);

  // Note there is no space after FROM to avoid db_rewrite problems
  // @see http://drupal.org/node/79904.
  switch ($extract_type) {
    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':

          // 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) {

        // MySQL returns 1 for Sunday through 7 for Saturday.
        case 'mysql':
          return "INTEGER(DAYOFWEEK({$field}) - 1)";

        // PHP date functions and postgres use 0 for Sunday and 6 for Saturday.
        case 'pgsql':
          return "EXTRACT(DOW FROM({$field}))";
      }
    case 'DOY':
      switch ($db_type) {
        case 'mysql':
          return "DAYOFYEAR({$field})";
        case 'pgsql':
          return "EXTRACT(DOY FROM({$field}))";
      }
  }
}