function date_sql_handler::sql_extract in Date 6

Same name and namespace in other branches

1. 5.2 date_api_sql.inc \date_sql_handler::sql_extract()
2. 6.2 date_api_sql.inc \date_sql_handler::sql_extract()
3. 7.3 date_api/date_api_sql.inc \date_sql_handler::sql_extract()
4. 7 date_api/date_api_sql.inc \date_sql_handler::sql_extract()
5. 7.2 date_api/date_api_sql.inc \date_sql_handler::sql_extract()

Helper function to create cross-database SQL date extraction.

Parameters

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

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

Return value

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

1 call to date_sql_handler::sql_extract()

date_sql_handler::sql_where_extract in ./date_api_sql.inc

Create a where clause to compare an extracted part of a field to an integer value.

File

./date_api_sql.inc, line 297

Class

date_sql_handler

A class to manipulate date SQL.

Code

function sql_extract($extract_type, $field) {

  // Note there is no space after FROM to avoid db_rewrite problems
  // see http://drupal.org/node/79904.
  switch (strtoupper($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 ($this->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 ($this->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 ($this->db_type) {
        case 'mysql':
        case 'mysqli':
          return "DAYOFYEAR({$field})";
        case 'pgsql':
          return "EXTRACT(DOY FROM({$field}))";
      }
  }
}