3 namespace Drupal\views\Plugin\views\query;
5 use Drupal\Core\Database\Connection;
8 * SQLite-specific date handling.
11 * This class should only be used by the Views SQL query plugin.
13 * @see \Drupal\views\Plugin\views\query\Sql
15 class SqliteDateSql implements DateSqlInterface {
18 * The database connection.
20 * @var \Drupal\Core\Database\Connection
25 * An array of PHP-to-SQLite date replacement patterns.
29 protected static $replace = [
31 // No format for 2 digit year number.
33 // No format for 3 letter month name.
36 // No format for month number without leading zeros.
38 // No format for full month name.
40 // No format for 3 letter day name.
43 // No format for full day name.
45 // no format for day of month number without leading zeros.
49 // No format for 12 hour hour with leading zeros.
53 // No format for AM/PM.
58 * Constructs the SQLite-specific date sql class.
60 * @param \Drupal\Core\Database\Connection $database
61 * The database connection.
63 public function __construct(Connection $database) {
64 $this->database = $database;
70 public function getDateField($field, $string_date) {
72 $field = "strftime('%s', $field)";
80 public function getDateFormat($field, $format) {
81 $format = strtr($format, static::$replace);
83 // SQLite does not have a ISO week substitution string, so it needs special
85 // @see http://wikipedia.org/wiki/ISO_week_date#Calculation
86 // @see http://stackoverflow.com/a/15511864/1499564
87 if ($format === '%W') {
88 $expression = "((strftime('%j', date(strftime('%Y-%m-%d', $field, 'unixepoch'), '-3 days', 'weekday 4')) - 1) / 7 + 1)";
91 $expression = "strftime('$format', $field, 'unixepoch')";
93 // The expression yields a string, but the comparison value is an integer in
94 // case the comparison value is a float, integer, or numeric. All of the
95 // above SQLite format tokens only produce integers. However, the given
96 // $format may contain 'Y-m-d', which results in a string.
97 // @see \Drupal\Core\Database\Driver\sqlite\Connection::expandArguments()
98 // @see http://www.sqlite.org/lang_datefunc.html
99 // @see http://www.sqlite.org/lang_expr.html#castexpr
100 if (preg_match('/^(?:%\w)+$/', $format)) {
101 $expression = "CAST($expression AS NUMERIC)";
109 public function setTimezoneOffset($offset) {
110 // Nothing to do here.
116 public function setFieldTimezoneOffset(&$field, $offset, $string_date = FALSE) {
117 if (!empty($offset)) {
118 $field = "($field + $offset)";