function views_date_sql_format

Helper function to create cross-database SQL date formatting.

Parameters

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

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.

File

includes/handlers.inc, line 1361

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',
                // No format for Numeric representation of a month, without leading
                // zeros.
'n' => 'MM',
                'F' => 'Month',
                'D' => 'Dy',
                'd' => 'DD',
                'l' => 'Day',
                // No format for Day of the month without leading zeros.
'j' => 'DD',
                '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(
                // 4 digit year number.
'Y' => '%Y',
                // No format for 2 digit year number.
'y' => '%Y',
                // No format for 3 letter month name.
'M' => '%m',
                // Month number with leading zeros.
'm' => '%m',
                // No format for month number without leading zeros.
'n' => '%m',
                // No format for full month name.
'F' => '%m',
                // No format for 3 letter day name.
'D' => '%d',
                // Day of month number with leading zeros.
'd' => '%d',
                // No format for full day name.
'l' => '%d',
                // No format for day of month number without leading zeros.
'j' => '%d',
                // ISO week number.
'W' => '%W',
                // 24 hour hour with leading zeros.
'H' => '%H',
                // No format for 12 hour hour with leading zeros.
'h' => '%H',
                // Minutes with leading zeros.
'i' => '%M',
                // Seconds with leading zeros.
's' => '%S',
                // No format for AM/PM.
'A' => '',
            );
            $format = strtr($format, $replace);
            return "strftime('{$format}', {$field}, 'unixepoch')";
    }
}