date_api_sql.inc

  1. nittany7 modules/contrib/date/date_api/date_api_sql.inc
  2. cis7 modules/contrib/date/date_api/date_api_sql.inc
  3. mooc7 modules/contrib/date/date_api/date_api_sql.inc

SQL helper for Date API.

@TODO Add experimental support for sqlite: http://www.sqlite.org/lang_datefunc.html and Oracle (http://psoug.org/reference/date_func.html and http://psoug.org/reference/datatypes.html) date/time functions.

Functions

Namesort descending Description
date_sql_coalesce Helper function to do cross-database NULL replacements
date_sql_concat A helper function to do cross-database concatation of date parts.
date_sql_pad A helper function to do cross-database padding of date parts.

Classes

Namesort descending Description
date_sql_handler A class to manipulate date SQL.

File

modules/contrib/date/date_api/date_api_sql.inc
View source
  1. <?php
  2. /**
  3. * @file
  4. * SQL helper for Date API.
  5. *
  6. * @TODO
  7. * Add experimental support for sqlite: http://www.sqlite.org/lang_datefunc.html
  8. * and Oracle (http://psoug.org/reference/date_func.html and
  9. * http://psoug.org/reference/datatypes.html) date/time functions.
  10. */
  11. /**
  12. * A helper function to do cross-database concatation of date parts.
  13. *
  14. * @param array $array
  15. * An array of values to be concatonated in sql.
  16. *
  17. * @return string
  18. * Correct sql string for database type.
  19. */
  20. function date_sql_concat($array) {
  21. switch (db_driver()) {
  22. case 'mysql':
  23. case 'mysqli':
  24. return "CONCAT(" . implode(",", $array) . ")";
  25. case 'pgsql':
  26. return implode(" || ", $array);
  27. }
  28. }
  29. /**
  30. * Helper function to do cross-database NULL replacements
  31. *
  32. * @param array $array
  33. * An array of values to test for NULL values.
  34. *
  35. * @return string
  36. * SQL statement to return the first non-NULL value in the list.
  37. */
  38. function date_sql_coalesce($array) {
  39. switch (db_driver()) {
  40. case 'mysql':
  41. case 'mysqli':
  42. case 'pgsql':
  43. return "COALESCE(" . implode(',', $array) . ")";
  44. }
  45. }
  46. /**
  47. * A helper function to do cross-database padding of date parts.
  48. *
  49. * @param string $str
  50. * A string to apply padding to
  51. * @param int $size
  52. * The size the final string should be
  53. * @param string $pad
  54. * The value to pad the string with
  55. * @param string $side
  56. * The side of the string to pad
  57. */
  58. function date_sql_pad($str, $size = 2, $pad = '0', $side = 'l') {
  59. switch ($side) {
  60. case 'r':
  61. return "RPAD($str, $size, '$pad')";
  62. default:
  63. return "LPAD($str, $size, '$pad')";
  64. }
  65. }
  66. /**
  67. * A class to manipulate date SQL.
  68. */
  69. class date_sql_handler {
  70. var $db_type = NULL;
  71. var $date_type = DATE_DATETIME;
  72. // A string timezone name.
  73. var $db_timezone = 'UTC';
  74. // A string timezone name.
  75. var $local_timezone = NULL;
  76. // Use if the db timezone is stored in a field.
  77. var $db_timezone_field = NULL;
  78. // Use if the local timezone is stored in a field.
  79. var $local_timezone_field = NULL;
  80. // Use if the offset is stored in a field.
  81. var $offset_field = NULL;
  82. /**
  83. * The object constuctor.
  84. */
  85. function __construct($date_type = DATE_DATETIME, $local_timezone = NULL, $offset = '+00:00') {
  86. $this->db_type = db_driver();
  87. $this->date_type = $date_type;
  88. $this->db_timezone = 'UTC';
  89. $this->local_timezone = isset($local_timezone) ? $local_timezone : date_default_timezone();
  90. $this->set_db_timezone($offset);
  91. }
  92. /**
  93. * See if the db has timezone name support.
  94. */
  95. function db_tz_support($reset = FALSE) {
  96. $has_support = variable_get('date_db_tz_support', -1);
  97. if ($has_support == -1 || $reset) {
  98. $has_support = FALSE;
  99. switch ($this->db_type) {
  100. case 'mysql':
  101. case 'mysqli':
  102. $test = db_query("SELECT CONVERT_TZ('2008-02-15 12:00:00', 'UTC', 'US/Central')")->fetchField();
  103. if ($test == '2008-02-15 06:00:00') {
  104. $has_support = TRUE;
  105. }
  106. break;
  107. case 'pgsql':
  108. $test = db_query("SELECT '2008-02-15 12:00:00 UTC' AT TIME ZONE 'US/Central'")->fetchField();
  109. if ($test == '2008-02-15 06:00:00') {
  110. $has_support = TRUE;
  111. }
  112. break;
  113. }
  114. variable_set('date_db_tz_support', $has_support);
  115. }
  116. return $has_support;
  117. }
  118. /**
  119. * Set the database timzone offset.
  120. *
  121. * Setting the db timezone to UTC is done to ensure consistency in date
  122. * handling whether or not the database can do proper timezone conversion.
  123. *
  124. * Views filters that not exposed are cached and won't set the timezone
  125. * so views date filters should add 'cacheable' => 'no' to their
  126. * definitions to ensure that the database timezone gets set properly
  127. * when the query is executed.
  128. *
  129. * @param string $offset
  130. * An offset value to set the database timezone to. This will only
  131. * set a fixed offset, not a timezone, so any value other than
  132. * '+00:00' should be used with caution.
  133. */
  134. function set_db_timezone($offset = '+00:00') {
  135. static $already_set = FALSE;
  136. $type = db_driver();
  137. if (!$already_set) {
  138. switch ($type) {
  139. case 'mysql':
  140. case 'mysqli':
  141. db_query("SET @@session.time_zone = '$offset'");
  142. break;
  143. case 'pgsql':
  144. db_query("SET TIME ZONE INTERVAL '$offset' HOUR TO MINUTE");
  145. break;
  146. case 'sqlsrv':
  147. // Issue #1201342, This is the wrong way to set the timezone, this
  148. // still needs to be fixed. In the meantime, commenting this out makes
  149. // SQLSRV functional.
  150. // db_query('TimeZone.setDefault(TimeZone.getTimeZone("GMT"))');
  151. break;
  152. }
  153. $already_set = TRUE;
  154. }
  155. }
  156. /**
  157. * Return timezone offset for the date being processed.
  158. */
  159. function get_offset($comp_date = NULL) {
  160. if (!empty($this->db_timezone) && !empty($this->local_timezone)) {
  161. if ($this->db_timezone != $this->local_timezone) {
  162. if (empty($comp_date)) {
  163. $comp_date = date_now($this->db_timezone);
  164. }
  165. $comp_date->setTimezone(timezone_open($this->local_timezone));
  166. return date_offset_get($comp_date);
  167. }
  168. }
  169. return 0;
  170. }
  171. /**
  172. * Helper function to create cross-database SQL dates.
  173. *
  174. * @param string $field
  175. * The real table and field name, like 'tablename.fieldname' .
  176. * @param string $offset
  177. * The name of a field that holds the timezone offset or an
  178. * offset value. If NULL, the normal Drupal timezone handling
  179. * will be used, if $offset = 0 no adjustment will be made.
  180. *
  181. * @return string
  182. * An appropriate SQL string for the db type and field type.
  183. */
  184. function sql_field($field, $offset = NULL, $comp_date = NULL) {
  185. if (strtoupper($field) == 'NOW') {
  186. // NOW() will be in UTC since that is what we set the db timezone to.
  187. $this->local_timezone = 'UTC';
  188. return $this->sql_offset('NOW()', $offset);
  189. }
  190. switch ($this->db_type) {
  191. case 'mysql':
  192. case 'mysqli':
  193. switch ($this->date_type) {
  194. case DATE_UNIX:
  195. $field = "FROM_UNIXTIME($field)";
  196. break;
  197. case DATE_ISO:
  198. $field = "STR_TO_DATE($field, '%Y-%m-%dT%T')";
  199. break;
  200. case DATE_DATETIME:
  201. break;
  202. }
  203. break;
  204. case 'pgsql':
  205. switch ($this->date_type) {
  206. case DATE_UNIX:
  207. $field = "$field::ABSTIME";
  208. break;
  209. case DATE_ISO:
  210. $field = "TO_DATE($field, 'FMYYYY-FMMM-FMDDTFMHH24:FMMI:FMSS')";
  211. break;
  212. case DATE_DATETIME:
  213. break;
  214. }
  215. break;
  216. case 'sqlite':
  217. switch ($this->date_type) {
  218. case DATE_UNIX:
  219. $field = "datetime($field, 'unixepoch')";
  220. break;
  221. case DATE_ISO:
  222. case DATE_DATETIME:
  223. $field = "datetime($field)";
  224. break;
  225. }
  226. break;
  227. case 'sqlsrv':
  228. switch ($this->date_type) {
  229. case DATE_UNIX:
  230. $field = "DATEADD(s, $field, '19700101 00:00:00:000')";
  231. break;
  232. case DATE_ISO:
  233. case DATE_DATETIME:
  234. $field = "CAST($field as smalldatetime)";
  235. break;
  236. }
  237. break;
  238. break;
  239. }
  240. // Adjust the resulting value to the right timezone/offset.
  241. return $this->sql_tz($field, $offset, $comp_date);
  242. }
  243. /**
  244. * Adjust a field value by an offset in seconds.
  245. */
  246. function sql_offset($field, $offset = NULL) {
  247. if (!empty($offset)) {
  248. switch ($this->db_type) {
  249. case 'mysql':
  250. case 'mysqli':
  251. return "ADDTIME($field, SEC_TO_TIME($offset))";
  252. case 'pgsql':
  253. return "($field + INTERVAL '$offset SECONDS')";;
  254. case 'sqlite':
  255. return "datetime($field, '$offset seconds')";
  256. case 'sqlsrv':
  257. return "DATEADD(second, $offset, $field)";
  258. }
  259. }
  260. return $field;
  261. }
  262. /**
  263. * Adjusts a field value by time interval.
  264. *
  265. * @param string $field
  266. * The field to be adjusted.
  267. * @param string $direction
  268. * Either ADD or SUB.
  269. * @param int $count
  270. * The number of values to adjust.
  271. * @param string $granularity
  272. * The granularity of the adjustment, should be singular,
  273. * like SECOND, MINUTE, DAY, HOUR.
  274. */
  275. function sql_date_math($field, $direction, $count, $granularity) {
  276. $granularity = strtoupper($granularity);
  277. switch ($this->db_type) {
  278. case 'mysql':
  279. case 'mysqli':
  280. switch ($direction) {
  281. case 'ADD':
  282. return "DATE_ADD($field, INTERVAL $count $granularity)";
  283. case 'SUB':
  284. return "DATE_SUB($field, INTERVAL $count $granularity)";
  285. }
  286. case 'pgsql':
  287. $granularity .= 'S';
  288. switch ($direction) {
  289. case 'ADD':
  290. return "($field + INTERVAL '$count $granularity')";
  291. case 'SUB':
  292. return "($field - INTERVAL '$count $granularity')";
  293. }
  294. case 'sqlite':
  295. $granularity .= 'S';
  296. switch ($direction) {
  297. case 'ADD':
  298. return "datetime($field, '+$count $granularity')";
  299. case 'SUB':
  300. return "datetime($field, '-$count $granularity')";
  301. }
  302. }
  303. return $field;
  304. }
  305. /**
  306. * Select a date value from the database, adjusting the value
  307. * for the timezone.
  308. *
  309. * Check whether database timezone conversion is supported in
  310. * this system and use it if possible, otherwise use an
  311. * offset.
  312. *
  313. * @param string $field
  314. * The field to be adjusted.
  315. * @param bool $offset
  316. * Set a fixed offset or offset field to use for the date.
  317. * If set, no timezone conversion will be done and the
  318. * offset will be used.
  319. */
  320. function sql_tz($field, $offset = NULL, $comp_date = NULL) {
  321. // If the timezones are values they need to be quoted, but
  322. // if they are field names they do not.
  323. $db_zone = !empty($this->db_timezone_field) ? $this->db_timezone_field : "'{$this->db_timezone}'";
  324. $localzone = !empty($this->local_timezone_field) ? $this->local_timezone_field : "'{$this->local_timezone}'";
  325. // If a fixed offset is required, use it.
  326. if ($offset !== NULL) {
  327. return $this->sql_offset($field, $offset);
  328. }
  329. // If the db and local timezones are the same, make no adjustment.
  330. elseif ($db_zone == $localzone) {
  331. return $this->sql_offset($field, 0);
  332. }
  333. // If the db has no timezone support, adjust by the offset,
  334. // could be either a field name or a value.
  335. elseif (!$this->db_tz_support() || empty($localzone)) {
  336. if (!empty($this->offset_field)) {
  337. return $this->sql_offset($field, $this->offset_field);
  338. }
  339. else {
  340. return $this->sql_offset($field, $this->get_offset($comp_date));
  341. }
  342. }
  343. // Otherwise make a database timezone adjustment to the field.
  344. else {
  345. switch ($this->db_type) {
  346. case 'mysql':
  347. case 'mysqli':
  348. return "CONVERT_TZ($field, $db_zone, $localzone)";
  349. case 'pgsql':
  350. // WITH TIME ZONE assumes the date is using the system
  351. // timezone, which should have been set to UTC.
  352. return "$field::timestamp with time zone AT TIME ZONE $localzone";
  353. }
  354. }
  355. }
  356. /**
  357. * Helper function to create cross-database SQL date formatting.
  358. *
  359. * @param string $format
  360. * A format string for the result, like 'Y-m-d H:i:s' .
  361. * @param string $field
  362. * The real table and field name, like 'tablename.fieldname' .
  363. *
  364. * @return string
  365. * An appropriate SQL string for the db type and field type.
  366. */
  367. function sql_format($format, $field) {
  368. switch ($this->db_type) {
  369. case 'mysql':
  370. case 'mysqli':
  371. $replace = array(
  372. 'Y' => '%Y',
  373. 'y' => '%y',
  374. 'M' => '%b',
  375. 'm' => '%m',
  376. 'n' => '%c',
  377. 'F' => '%M',
  378. 'D' => '%a',
  379. 'd' => '%d',
  380. 'l' => '%W',
  381. 'j' => '%e',
  382. 'W' => '%v',
  383. 'H' => '%H',
  384. 'h' => '%h',
  385. 'i' => '%i',
  386. 's' => '%s',
  387. 'A' => '%p',
  388. '\WW' => 'W%U',
  389. );
  390. $format = strtr($format, $replace);
  391. return "DATE_FORMAT($field, '$format')";
  392. case 'pgsql':
  393. $replace = array(
  394. 'Y' => 'YYYY',
  395. 'y' => 'YY',
  396. 'M' => 'Mon',
  397. 'm' => 'MM',
  398. // No format for Numeric representation of a month, without leading
  399. // zeros.
  400. 'n' => 'MM',
  401. 'F' => 'Month',
  402. 'D' => 'Dy',
  403. 'd' => 'DD',
  404. 'l' => 'Day',
  405. // No format for Day of the month without leading zeros.
  406. 'j' => 'DD',
  407. 'W' => 'WW',
  408. 'H' => 'HH24',
  409. 'h' => 'HH12',
  410. 'i' => 'MI',
  411. 's' => 'SS',
  412. 'A' => 'AM',
  413. '\T' => '"T"',
  414. // '\W' => // TODO, what should this be?
  415. );
  416. $format = strtr($format, $replace);
  417. return "TO_CHAR($field, '$format')";
  418. case 'sqlite':
  419. $replace = array(
  420. // 4 digit year number.
  421. 'Y' => '%Y',
  422. // No format for 2 digit year number.
  423. 'y' => '%Y',
  424. // No format for 3 letter month name.
  425. 'M' => '%m',
  426. // Month number with leading zeros.
  427. 'm' => '%m',
  428. // No format for month number without leading zeros.
  429. 'n' => '%m',
  430. // No format for full month name.
  431. 'F' => '%m',
  432. // No format for 3 letter day name.
  433. 'D' => '%d',
  434. // Day of month number with leading zeros.
  435. 'd' => '%d',
  436. // No format for full day name.
  437. 'l' => '%d',
  438. // No format for day of month number without leading zeros.
  439. 'j' => '%d',
  440. // ISO week number.
  441. 'W' => '%W',
  442. // 24 hour hour with leading zeros.
  443. 'H' => '%H',
  444. // No format for 12 hour hour with leading zeros.
  445. 'h' => '%H',
  446. // Minutes with leading zeros.
  447. 'i' => '%M',
  448. // Seconds with leading zeros.
  449. 's' => '%S',
  450. // No format for AM/PM.
  451. 'A' => '',
  452. // Week number.
  453. '\WW' => '',
  454. );
  455. $format = strtr($format, $replace);
  456. return "strftime('$format', $field)";
  457. case 'sqlsrv':
  458. $replace = array(
  459. // 4 digit year number.
  460. 'Y' => "' + CAST(DATEPART(year, $field) AS nvarchar) + '",
  461. // 2 digit year number.
  462. 'y' => "' + RIGHT(DATEPART(year, $field), 2) + '",
  463. // 3 letter month name.
  464. 'M' => "' + LEFT(DATENAME(month, $field), 3) + '",
  465. // Month number with leading zeros.
  466. 'm' => "' + RIGHT('0' + CAST(DATEPART(month, $field) AS nvarchar), 2) + '",
  467. // Month number without leading zeros.
  468. 'n' => "' + CAST(DATEPART(month, $field) AS nvarchar) + '",
  469. // Full month name.
  470. 'F' => "' + DATENAME(month, $field) + '",
  471. // 3 letter day name.
  472. 'D' => "' + LEFT(DATENAME(day, $field), 3) + '",
  473. // Day of month number with leading zeros.
  474. 'd' => "' + RIGHT('0' + CAST(DATEPART(day, $field) AS nvarchar), 2) + '",
  475. // Full day name.
  476. 'l' => "' + DATENAME(day, $field) + '",
  477. // Day of month number without leading zeros.
  478. 'j' => "' + CAST(DATEPART(day, $field) AS nvarchar) + '",
  479. // ISO week number.
  480. 'W' => "' + CAST(DATEPART(iso_week, $field) AS nvarchar) + '",
  481. // 24 hour with leading zeros.
  482. 'H' => "' + RIGHT('0' + CAST(DATEPART(hour, $field) AS nvarchar), 2) + '",
  483. // 12 hour with leading zeros.
  484. // Conversion to 'mon dd yyyy hh:miAM/PM' format (corresponds to style
  485. // 100 in MSSQL).
  486. // Hour position is fixed, so we use SUBSTRING to extract it.
  487. 'h' => "' + RIGHT('0' + LTRIM(SUBSTRING(CONVERT(nvarchar, $field, 100), 13, 2)), 2) + '",
  488. // Minutes with leading zeros.
  489. 'i' => "' + RIGHT('0' + CAST(DATEPART(minute, $field) AS nvarchar), 2) + '",
  490. // Seconds with leading zeros.
  491. 's' => "' + RIGHT('0' + CAST(DATEPART(second, $field) AS nvarchar), 2) + '",
  492. // AM/PM.
  493. // Conversion to 'mon dd yyyy hh:miAM/PM' format (corresponds to style
  494. // 100 in MSSQL).
  495. 'A' => "' + RIGHT(CONVERT(nvarchar, $field, 100), 2) + '",
  496. // Week number.
  497. '\WW' => "' + CAST(DATEPART(week, $field) AS nvarchar) + '",
  498. '\T' => 'T',
  499. // MS SQL uses single quote as escape symbol.
  500. '\'' => '\'\'',
  501. );
  502. $format = strtr($format, $replace);
  503. $format = "'$format'";
  504. return $format;
  505. }
  506. }
  507. /**
  508. * Helper function to create cross-database SQL date extraction.
  509. *
  510. * @param string $extract_type
  511. * The type of value to extract from the date, like 'MONTH'.
  512. * @param string $field
  513. * The real table and field name, like 'tablename.fieldname'.
  514. *
  515. * @return string
  516. * An appropriate SQL string for the db type and field type.
  517. */
  518. function sql_extract($extract_type, $field) {
  519. // Note there is no space after FROM to avoid db_rewrite problems
  520. // see http://drupal.org/node/79904.
  521. switch (strtoupper($extract_type)) {
  522. case 'DATE':
  523. return $field;
  524. case 'YEAR':
  525. return "EXTRACT(YEAR FROM($field))";
  526. case 'MONTH':
  527. return "EXTRACT(MONTH FROM($field))";
  528. case 'DAY':
  529. return "EXTRACT(DAY FROM($field))";
  530. case 'HOUR':
  531. return "EXTRACT(HOUR FROM($field))";
  532. case 'MINUTE':
  533. return "EXTRACT(MINUTE FROM($field))";
  534. case 'SECOND':
  535. return "EXTRACT(SECOND FROM($field))";
  536. // ISO week number for date.
  537. case 'WEEK':
  538. switch ($this->db_type) {
  539. case 'mysql':
  540. case 'mysqli':
  541. // WEEK using arg 3 in MySQl should return the same value as
  542. // Postgres EXTRACT.
  543. return "WEEK($field, 3)";
  544. case 'pgsql':
  545. return "EXTRACT(WEEK FROM($field))";
  546. }
  547. case 'DOW':
  548. switch ($this->db_type) {
  549. case 'mysql':
  550. case 'mysqli':
  551. // MySQL returns 1 for Sunday through 7 for Saturday, PHP date
  552. // functions and Postgres use 0 for Sunday and 6 for Saturday.
  553. return "INTEGER(DAYOFWEEK($field) - 1)";
  554. case 'pgsql':
  555. return "EXTRACT(DOW FROM($field))";
  556. }
  557. case 'DOY':
  558. switch ($this->db_type) {
  559. case 'mysql':
  560. case 'mysqli':
  561. return "DAYOFYEAR($field)";
  562. case 'pgsql':
  563. return "EXTRACT(DOY FROM($field))";
  564. }
  565. }
  566. }
  567. /**
  568. * Creates a where clause to compare a complete date field to a date value.
  569. *
  570. * @param string $type
  571. * The type of value we're comparing to, could be another field
  572. * or a date value.
  573. * @param string $field
  574. * The db table and field name, like "$table.$field".
  575. * @param string $operator
  576. * The db comparison operator to use, like '='.
  577. * @param int $value
  578. * The value to compare the extracted date part to, could be a field name or
  579. * a date string or NOW().
  580. *
  581. * @return string
  582. * SQL for the where clause for this operation.
  583. */
  584. function sql_where_date($type, $field, $operator, $value, $adjustment = NULL) {
  585. $type = strtoupper($type);
  586. if (strtoupper($value) == 'NOW') {
  587. $value = $this->sql_field('NOW', $adjustment);
  588. }
  589. elseif ($type == 'FIELD') {
  590. $value = $this->sql_field($value, $adjustment);
  591. }
  592. elseif ($type == 'DATE') {
  593. $date = new DateObject($value, date_default_timezone(), DATE_FORMAT_DATETIME);
  594. if (!empty($adjustment)) {
  595. date_modify($date, $adjustment . ' seconds');
  596. }
  597. // When comparing a field to a date we can avoid doing timezone
  598. // conversion by altering the comparison date to the db timezone.
  599. // This won't work if the timezone is a field instead of a value.
  600. if (empty($this->db_timezone_field) && empty($this->local_timezone_field) && $this->db_timezone_field != $this->local_timezone_field) {
  601. $date->setTimezone(timezone_open($this->db_timezone));
  602. $this->local_timezone = $this->db_timezone;
  603. }
  604. $value = "'" . $date->format(DATE_FORMAT_DATETIME, TRUE) . "'";
  605. }
  606. if ($this->local_timezone != $this->db_timezone) {
  607. $field = $this->sql_field($field);
  608. }
  609. else {
  610. $field = $this->sql_field($field, 0);
  611. }
  612. return "$field $operator $value";
  613. }
  614. /**
  615. * Creates a where clause comparing an extracted date part to an integer.
  616. *
  617. * @param string $part
  618. * The part to extract, YEAR, MONTH, DAY, etc.
  619. * @param string $field
  620. * The db table and field name, like "$table.$field".
  621. * @param string $operator
  622. * The db comparison operator to use, like '=' .
  623. * @param int $value
  624. * The integer value to compare the extracted date part to.
  625. *
  626. * @return string
  627. * SQL for the where clause for this operation.
  628. */
  629. function sql_where_extract($part, $field, $operator, $value, $adjustment = NULL) {
  630. if (empty($adjustment) && $this->local_timezone != $this->db_timezone) {
  631. $field = $this->sql_field($field);
  632. }
  633. else {
  634. $field = $this->sql_field($field, $adjustment);
  635. }
  636. return $this->sql_extract($part, $field) . " $operator $value";
  637. }
  638. /**
  639. * Create a where clause to compare a formated field to a formated value.
  640. *
  641. * @param string $format
  642. * The format to use on the date and the value when comparing them.
  643. * @param string $field
  644. * The db table and field name, like "$table.$field".
  645. * @param string $operator
  646. * The db comparison operator to use, like '=' .
  647. * @param string $value
  648. * The value to compare the extracted date part to, could be a
  649. * field name or a date string or NOW().
  650. *
  651. * @return string
  652. * SQL for the where clause for this operation.
  653. */
  654. function sql_where_format($format, $field, $operator, $value, $adjustment = NULL) {
  655. if (empty($adjustment) && $this->local_timezone != $this->db_timezone) {
  656. $field = $this->sql_field($field);
  657. }
  658. else {
  659. $field = $this->sql_field($field, $adjustment);
  660. }
  661. return $this->sql_format($format, $field) . " $operator '$value'";
  662. }
  663. /**
  664. * An array of all date parts,
  665. * optionally limited to an array of allowed parts.
  666. */
  667. function date_parts($limit = NULL) {
  668. $parts = array(
  669. 'year' => t('Year', array(), array('context' => 'datetime')),
  670. 'month' => t('Month', array(), array('context' => 'datetime')),
  671. 'day' => t('Day', array(), array('context' => 'datetime')),
  672. 'hour' => t('Hour', array(), array('context' => 'datetime')),
  673. 'minute' => t('Minute', array(), array('context' => 'datetime')),
  674. 'second' => t('Second', array(), array('context' => 'datetime')),
  675. );
  676. if (!empty($limit)) {
  677. $last = FALSE;
  678. foreach ($parts as $key => $part) {
  679. if ($last) {
  680. unset($parts[$key]);
  681. }
  682. if ($key == $limit) {
  683. $last = TRUE;
  684. }
  685. }
  686. }
  687. return $parts;
  688. }
  689. /**
  690. * Part information.
  691. *
  692. * @param string $op
  693. * 'min', 'max', 'format', 'sep', 'empty_now', 'empty_min', 'empty_max' .
  694. * Returns all info if empty.
  695. * @param string $part
  696. * 'year', 'month', 'day', 'hour', 'minute', or 'second.
  697. * returns info for all parts if empty.
  698. */
  699. function part_info($op = NULL, $part = NULL) {
  700. $info = array();
  701. $info['min'] = array(
  702. 'year' => 100,
  703. 'month' => 1,
  704. 'day' => 1,
  705. 'hour' => 0,
  706. 'minute' => 0,
  707. 'second' => 0,
  708. );
  709. $info['max'] = array(
  710. 'year' => 4000,
  711. 'month' => 12,
  712. 'day' => 31,
  713. 'hour' => 23,
  714. 'minute' => 59,
  715. 'second' => 59,
  716. );
  717. $info['format'] = array(
  718. 'year' => 'Y',
  719. 'month' => 'm',
  720. 'day' => 'd',
  721. 'hour' => 'H',
  722. 'minute' => 'i',
  723. 'second' => 's',
  724. );
  725. $info['sep'] = array(
  726. 'year' => '',
  727. 'month' => '-',
  728. 'day' => '-',
  729. 'hour' => ' ',
  730. 'minute' => ':',
  731. 'second' => ':',
  732. );
  733. $info['empty_now'] = array(
  734. 'year' => date('Y'),
  735. 'month' => date('m'),
  736. 'day' => min('28', date('d')),
  737. 'hour' => date('H'),
  738. 'minute' => date('i'),
  739. 'second' => date('s'),
  740. );
  741. $info['empty_min'] = array(
  742. 'year' => '1000',
  743. 'month' => '01',
  744. 'day' => '01',
  745. 'hour' => '00',
  746. 'minute' => '00',
  747. 'second' => '00',
  748. );
  749. $info['empty_max'] = array(
  750. 'year' => '9999',
  751. 'month' => '12',
  752. 'day' => '31',
  753. 'hour' => '23',
  754. 'minute' => '59',
  755. 'second' => '59',
  756. );
  757. if (!empty($op)) {
  758. if (!empty($part)) {
  759. return $info[$op][$part];
  760. }
  761. else {
  762. return $info[$op];
  763. }
  764. }
  765. return $info;
  766. }
  767. /**
  768. * Create a complete datetime value out of an
  769. * incomplete array of selected values.
  770. *
  771. * For example, array('year' => 2008, 'month' => 05) will fill
  772. * in the day, hour, minute and second with the earliest possible
  773. * values if type = 'min', the latest possible values if type = 'max',
  774. * and the current values if type = 'now' .
  775. */
  776. function complete_date($selected, $type = 'now') {
  777. if (empty($selected)) {
  778. return '';
  779. }
  780. // Special case for weeks.
  781. if (array_key_exists('week', $selected)) {
  782. $dates = date_week_range($selected['week'], $selected['year']);
  783. switch ($type) {
  784. case 'empty_now':
  785. case 'empty_min':
  786. case 'min':
  787. return date_format($dates[0], 'Y-m-d H:i:s');
  788. case 'empty_max':
  789. case 'max':
  790. return date_format($dates[1], 'Y-m-d H:i:s');
  791. default:
  792. return;
  793. }
  794. }
  795. $compare = array_merge($this->part_info('empty_' . $type), $selected);
  796. // If this is a max date, make sure the last day of
  797. // the month is the right one for this date.
  798. if ($type == 'max') {
  799. $compare['day'] = date_days_in_month($compare['year'], $compare['month']);
  800. }
  801. $value = '';
  802. $separators = $this->part_info('sep');
  803. foreach ($this->date_parts() as $key => $name) {
  804. $value .= $separators[$key] . (!empty($selected[$key]) ? $selected[$key] : $compare[$key]);
  805. }
  806. return $value;
  807. }
  808. /**
  809. * Converts a format string into help text, i.e. 'Y-m-d' becomes 'YYYY-MM-DD'.
  810. *
  811. * @param string $format
  812. * A date format string.
  813. *
  814. * @return string
  815. * The conveted help text.
  816. */
  817. function format_help($format) {
  818. $replace = array(
  819. 'Y' => 'YYYY',
  820. 'm' => 'MM',
  821. 'd' => 'DD',
  822. 'H' => 'HH',
  823. 'i' => 'MM',
  824. 's' => 'SS',
  825. '\T' => 'T',
  826. );
  827. return strtr($format, $replace);
  828. }
  829. /**
  830. * A function to test the validity of various date parts
  831. */
  832. function part_is_valid($value, $type) {
  833. if (!preg_match('/^[0-9]*$/', $value)) {
  834. return FALSE;
  835. }
  836. $value = intval($value);
  837. if ($value <= 0) {
  838. return FALSE;
  839. }
  840. switch ($type) {
  841. case 'year':
  842. if ($value < DATE_MIN_YEAR) {
  843. return FALSE;
  844. }
  845. break;
  846. case 'month':
  847. if ($value < 0 || $value > 12) {
  848. return FALSE;
  849. }
  850. break;
  851. case 'day':
  852. if ($value < 0 || $value > 31) {
  853. return FALSE;
  854. }
  855. break;
  856. case 'week':
  857. if ($value < 0 || $value > 53) {
  858. return FALSE;
  859. }
  860. break;
  861. }
  862. return TRUE;
  863. }
  864. /**
  865. * @todo.
  866. */
  867. function views_formats($granularity, $type = 'sql') {
  868. if (empty($granularity)) {
  869. return DATE_FORMAT_ISO;
  870. }
  871. $formats = array('display', 'sql');
  872. // Start with the site long date format and add seconds to it.
  873. $long = str_replace(':i', ':i:s', variable_get('date_format_long', 'l, F j, Y - H:i'));
  874. switch ($granularity) {
  875. case 'year':
  876. $formats['display'] = 'Y';
  877. $formats['sql'] = 'Y';
  878. break;
  879. case 'month':
  880. $formats['display'] = date_limit_format($long, array('year', 'month'));
  881. $formats['sql'] = 'Y-m';
  882. break;
  883. case 'day':
  884. $formats['display'] = date_limit_format($long, array('year', 'month', 'day'));
  885. $formats['sql'] = 'Y-m-d';
  886. break;
  887. case 'hour':
  888. $formats['display'] = date_limit_format($long, array('year', 'month', 'day', 'hour'));
  889. $formats['sql'] = 'Y-m-d\TH';
  890. break;
  891. case 'minute':
  892. $formats['display'] = date_limit_format($long, array('year', 'month', 'day', 'hour', 'minute'));
  893. $formats['sql'] = 'Y-m-d\TH:i';
  894. break;
  895. case 'second':
  896. $formats['display'] = date_limit_format($long, array('year', 'month', 'day', 'hour', 'minute', 'second'));
  897. $formats['sql'] = 'Y-m-d\TH:i:s';
  898. break;
  899. case 'week':
  900. $formats['display'] = 'F j Y (W)';
  901. $formats['sql'] = 'Y-\WW';
  902. break;
  903. }
  904. return $formats[$type];
  905. }
  906. /**
  907. * @todo.
  908. */
  909. function granularity_form($granularity) {
  910. $form = array(
  911. '#title' => t('Granularity'),
  912. '#type' => 'radios',
  913. '#default_value' => $granularity,
  914. '#options' => $this->date_parts(),
  915. );
  916. return $form;
  917. }
  918. /**
  919. * Parse date parts from an ISO date argument.
  920. *
  921. * Based on ISO 8601 date duration and time interval standards.
  922. *
  923. * Parses a value like 2006-01-01--2006-01-15, or 2006-W24, or @P1W.
  924. * Separate start and end dates or date and period with a double hyphen (--).
  925. *
  926. * The 'end' portion of the argument can be eliminated if it is the same as
  927. * the 'start' portion. Use @ instead of a date to substitute in the current
  928. * date and time.
  929. *
  930. * Use periods (P1H, P1D, P1W, P1M, P1Y) to get next hour/day/week/month/year
  931. * from now. Use date before P sign to get next hour/day/week/month/year from
  932. * that date. Use period then date to get a period that ends on the date.
  933. *
  934. * @see http://en.wikipedia.org/wiki/ISO_8601#Week_dates
  935. * @see http://en.wikipedia.org/wiki/ISO_8601#Duration
  936. */
  937. function arg_parts($argument) {
  938. $values = array();
  939. // Keep mal-formed arguments from creating errors.
  940. if (empty($argument) || is_array($argument)) {
  941. return array('date' => array(), 'period' => array());
  942. }
  943. $fromto = explode('--', $argument);
  944. foreach ($fromto as $arg) {
  945. $parts = array();
  946. if ($arg == '@') {
  947. $date = date_now();
  948. $parts['date'] = $date->toArray();
  949. }
  950. elseif (preg_match('/(\d{4})?-?(W)?(\d{1,2})?-?(\d{1,2})?[T\s]?(\d{1,2})?:?(\d{1,2})?:?(\d{1,2})?/', $arg, $matches)) {
  951. $date = array();
  952. if (!empty($matches[1])) {
  953. $date['year'] = $matches[1];
  954. }
  955. if (!empty($matches[3])) {
  956. if (empty($matches[2])) {
  957. $date['month'] = $matches[3];
  958. }
  959. else {
  960. $date['week'] = $matches[3];
  961. }
  962. }
  963. if (!empty($matches[4])) {
  964. $date['day'] = $matches[4];
  965. }
  966. if (!empty($matches[5])) {
  967. $date['hour'] = $matches[5];
  968. }
  969. if (!empty($matches[6])) {
  970. $date['minute'] = $matches[6];
  971. }
  972. if (!empty($matches[7])) {
  973. $date['second'] = $matches[7];
  974. }
  975. $parts['date'] = $date;
  976. }
  977. if (preg_match('/^P(\d{1,4}[Y])?(\d{1,2}[M])?(\d{1,2}[W])?(\d{1,2}[D])?([T]{0,1})?(\d{1,2}[H])?(\d{1,2}[M])?(\d{1,2}[S])?/', $arg, $matches)) {
  978. $period = array();
  979. if (!empty($matches[1])) {
  980. $period['year'] = str_replace('Y', '', $matches[1]);
  981. }
  982. if (!empty($matches[2])) {
  983. $period['month'] = str_replace('M', '', $matches[2]);
  984. }
  985. if (!empty($matches[3])) {
  986. $period['week'] = str_replace('W', '', $matches[3]);
  987. }
  988. if (!empty($matches[4])) {
  989. $period['day'] = str_replace('D', '', $matches[4]);
  990. }
  991. if (!empty($matches[6])) {
  992. $period['hour'] = str_replace('H', '', $matches[6]);
  993. }
  994. if (!empty($matches[7])) {
  995. $period['minute'] = str_replace('M', '', $matches[7]);
  996. }
  997. if (!empty($matches[8])) {
  998. $period['second'] = str_replace('S', '', $matches[8]);
  999. }
  1000. $parts['period'] = $period;
  1001. }
  1002. $values[] = $parts;
  1003. }
  1004. return $values;
  1005. }
  1006. /**
  1007. * Convert strings like '+1 day' to the ISO equivalent, like 'P1D' .
  1008. */
  1009. function arg_replace($arg) {
  1010. if (!preg_match('/([+|-])\s?([0-9]{1,32})\s?([day(s)?|week(s)?|month(s)?|year(s)?|hour(s)?|minute(s)?|second(s)?]{1,10})/', $arg, $results)) {
  1011. return str_replace('now', '@', $arg);
  1012. }
  1013. $direction = $results[1];
  1014. $count = $results[2];
  1015. $item = $results[3];
  1016. $replace = array(
  1017. 'now' => '@',
  1018. '+' => 'P',
  1019. '-' => 'P-',
  1020. 'years' => 'Y',
  1021. 'year' => 'Y',
  1022. 'months' => 'M',
  1023. 'month' => 'M',
  1024. 'weeks' => 'W',
  1025. 'week' => 'W',
  1026. 'days' => 'D',
  1027. 'day' => 'D',
  1028. 'hours' => 'H',
  1029. 'hour' => 'H',
  1030. 'minutes' => 'M',
  1031. 'minute' => 'M',
  1032. 'seconds' => 'S',
  1033. 'second' => 'S',
  1034. ' ' => '',
  1035. ' ' => '',
  1036. );
  1037. $prefix = in_array($item, array('hours', 'hour', 'minutes', 'minute', 'seconds', 'second')) ? 'T' : '';
  1038. return $prefix . strtr($direction, $replace) . $count . strtr($item, $replace);
  1039. }
  1040. /**
  1041. * Use the parsed values from the ISO argument to determine the
  1042. * granularity of this period.
  1043. */
  1044. function arg_granularity($arg) {
  1045. $granularity = '';
  1046. $parts = $this->arg_parts($arg);
  1047. $date = !empty($parts[0]['date']) ? $parts[0]['date'] : (!empty($parts[1]['date']) ? $parts[1]['date'] : array());
  1048. foreach ($date as $key => $part) {
  1049. $granularity = $key;
  1050. }
  1051. return $granularity;
  1052. }
  1053. /**
  1054. * Use the parsed values from the ISO argument to determine the
  1055. * min and max date for this period.
  1056. */
  1057. function arg_range($arg) {
  1058. // Parse the argument to get its parts.
  1059. $parts = $this->arg_parts($arg);
  1060. // Build a range from a period-only argument (assumes the min date is now.)
  1061. if (empty($parts[0]['date']) && !empty($parts[0]['period']) && (empty($parts[1]))) {
  1062. $min_date = date_now();
  1063. $max_date = clone($min_date);
  1064. foreach ($parts[0]['period'] as $part => $value) {
  1065. date_modify($max_date, "+$value $part");
  1066. }
  1067. date_modify($max_date, '-1 second');
  1068. return array($min_date, $max_date);
  1069. }
  1070. // Build a range from a period to period argument.
  1071. if (empty($parts[0]['date']) && !empty($parts[0]['period']) && !empty($parts[1]['period'])) {
  1072. $min_date = date_now();
  1073. $max_date = clone($min_date);
  1074. foreach ($parts[0]['period'] as $part => $value) {
  1075. date_modify($min_date, "+$value $part");
  1076. }
  1077. date_modify($min_date, '-1 second');
  1078. foreach ($parts[1]['period'] as $part => $value) {
  1079. date_modify($max_date, "+$value $part");
  1080. }
  1081. date_modify($max_date, '-1 second');
  1082. return array($min_date, $max_date);
  1083. }
  1084. if (!empty($parts[0]['date'])) {
  1085. $value = $this->complete_date($parts[0]['date'], 'min');
  1086. $min_date = new DateObject($value, date_default_timezone(), DATE_FORMAT_DATETIME);
  1087. // Build a range from a single date-only argument.
  1088. if (empty($parts[1]) || (empty($parts[1]['date']) && empty($parts[1]['period']))) {
  1089. $value = $this->complete_date($parts[0]['date'], 'max');
  1090. $max_date = new DateObject($value, date_default_timezone(), DATE_FORMAT_DATETIME);
  1091. return array($min_date, $max_date);
  1092. }
  1093. // Build a range from start date + period.
  1094. elseif (!empty($parts[1]['period'])) {
  1095. foreach ($parts[1]['period'] as $part => $value) {
  1096. $max_date = clone($min_date);
  1097. date_modify($max_date, "+$value $part");
  1098. }
  1099. date_modify($max_date, '-1 second');
  1100. return array($min_date, $max_date);
  1101. }
  1102. }
  1103. // Build a range from start date and end date.
  1104. if (!empty($parts[1]['date'])) {
  1105. $value = $this->complete_date($parts[1]['date'], 'max');
  1106. $max_date = new DateObject($value, date_default_timezone(), DATE_FORMAT_DATETIME);
  1107. if (isset($min_date)) {
  1108. return array($min_date, $max_date);
  1109. }
  1110. }
  1111. // Build a range from period + end date.
  1112. if (!empty($parts[0]['period'])) {
  1113. $min_date = date_now();
  1114. foreach ($parts[0]['period'] as $part => $value) {
  1115. date_modify($min_date, "$value $part");
  1116. }
  1117. return array($min_date, $max_date);
  1118. }
  1119. // Intercept invalid info and fall back to the current date.
  1120. $now = date_now();
  1121. return array($now, $now);
  1122. }
  1123. }