- $db_type = Database::getConnection()->databaseType();
- switch ($db_type) {
- case 'mysql':
- $replace = [
- '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 = [
- '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' => 'IW',
- 'H' => 'HH24',
- 'h' => 'HH12',
- 'i' => 'MI',
- 's' => 'SS',
- 'A' => 'AM',
- ];
- $format = strtr($format, $replace);
- if (!$string_date) {
- return "TO_CHAR($field, '$format')";
- }
- // In order to allow for partials (eg, only the year), transform to a
- // date, back to a string again.
- return "TO_CHAR(TO_TIMESTAMP($field, 'YYYY-MM-DD HH24:MI:SS'), '$format')";
- case 'sqlite':
- $replace = [
- 'Y' => '%Y',
- // No format for 2 digit year number.
- 'y' => '%Y',
- // No format for 3 letter month name.
- 'M' => '%m',
- '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',
- 'd' => '%d',
- // No format for full day name.
- 'l' => '%d',
- // no format for day of month number without leading zeros.
- 'j' => '%d',
- 'W' => '%W',
- 'H' => '%H',
- // No format for 12 hour hour with leading zeros.
- 'h' => '%H',
- 'i' => '%M',
- 's' => '%S',
- // No format for AM/PM.
- 'A' => '',
- ];
- $format = strtr($format, $replace);
-
- // Don't use the 'unixepoch' flag for string date comparisons.
- $unixepoch = $string_date ? '' : ", 'unixepoch'";
-
- // SQLite does not have a ISO week substitution string, so it needs
- // special handling.
- // @see http://wikipedia.org/wiki/ISO_week_date#Calculation
- // @see http://stackoverflow.com/a/15511864/1499564
- if ($format === '%W') {
- $expression = "((strftime('%j', date(strftime('%Y-%m-%d', $field" . $unixepoch . "), '-3 days', 'weekday 4')) - 1) / 7 + 1)";
- }
- else {
- $expression = "strftime('$format', $field" . $unixepoch . ")";
- }
- // The expression yields a string, but the comparison value is an
- // integer in case the comparison value is a float, integer, or numeric.
- // All of the above SQLite format tokens only produce integers. However,
- // the given $format may contain 'Y-m-d', which results in a string.
- // @see \Drupal\Core\Database\Driver\sqlite\Connection::expandArguments()
- // @see http://www.sqlite.org/lang_datefunc.html
- // @see http://www.sqlite.org/lang_expr.html#castexpr
- if (preg_match('/^(?:%\w)+$/', $format)) {
- $expression = "CAST($expression AS NUMERIC)";
- }
- return $expression;
- }