function views_date_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'.

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

Code

function views_date_sql_extract($extract_type, $field, $field_type = 'int', $set_offset = NULL) {
    $db_type = $GLOBALS['db_type'];
    $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':
                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}))";
            }
    }
}