Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Browse files

Merge pull request #1811 from elinw/datemath

Add a date addition/subtraction method to JDatabaseQuery
  • Loading branch information...
commit d0ec4626ffb1597d10b96da32165348fab01009f 2 parents 5fbac7b + 74dbaaf
@eddieajau eddieajau authored
View
23 libraries/joomla/database/query.php
@@ -1755,4 +1755,27 @@ public function format($format)
*/
return preg_replace_callback('#%(((([\d]+)\$)?([aeEnqQryYmMdDhHiIsStzZ]))|(%))#', $func, $format);
}
+
+ /**
+ * Add to the current date and time.
+ * Usage:
+ * $query->select($query->dateAdd());
+ * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
+ * Note: Not all drivers support all units.
+ *
+ * @param datetime $date The date to add to. May be date or datetime
+ * @param string $interval The string representation of the appropriate number of units
+ * @param string $datePart The part of the date to perform the addition on
+ *
+ * @return sring The string with the appropriate sql for addition of dates
+ *
+ * @since 13.1
+ *
+ * @see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add
+
+ */
+ public function dateAdd($date, $interval, $datePart)
+ {
+ return trim("DATE_ADD('" . $date . "', INTERVAL " . $interval . ' ' . $datePart . ')');
+ }
}
View
27 libraries/joomla/database/query/postgresql.php
@@ -599,4 +599,31 @@ public function processLimit($query, $limit, $offset = 0)
return $query;
}
+ /**
+ * Add to the current date and time in Postgresql.
+ * Usage:
+ * $query->select($query->dateAdd());
+ * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
+ *
+ * @param datetime $date The date to add to
+ * @param string $interval The string representation of the appropriate number of units
+ * @param string $datePart The part of the date to perform the addition on
+ *
+ * @return sring The string with the appropriate sql for addition of dates
+ *
+ * @since 13.1
+ * @note Not all drivers support all units. Check appropriate references
+ * @link http://www.postgresql.org/docs/9.0/static/functions-datetime.html.
+ */
+ public function dateAdd($date, $interval, $datePart)
+ {
+ if (substr($interval, 0, 1) != '-')
+ {
+ return "timestamp '" . $date . "' + interval '" . $interval . " " . $datePart . "'";
+ }
+ else
+ {
+ return "timestamp '" . $date . "' - interval '" . ltrim($interval, '-') . " " . $datePart . "'";
+ }
+ }
}
View
32 libraries/joomla/database/query/sqlite.php
@@ -180,4 +180,36 @@ public function setLimit($limit = 0, $offset = 0)
return $this;
}
+ /**
+ * Add to the current date and time.
+ * Usage:
+ * $query->select($query->dateAdd());
+ * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
+ *
+ * @param datetime $date The date or datetime to add to
+ * @param string $interval The string representation of the appropriate number of units
+ * @param string $datePart The part of the date to perform the addition on
+ *
+ * @return sring The string with the appropriate sql for addition of dates
+ *
+ * @since 13.1
+ * @link http://www.sqlite.org/lang_datefunc.html
+ */
+ public function dateAdd($date, $interval, $datePart)
+ {
+ // SQLite does not support microseconds as a separate unit. Convert the interval to seconds
+ if (strcasecmp($datePart, 'microseconds') == 0)
+ {
+ $interval = .001 * $interval;
+ $datePart = 'seconds';
+ }
+ if (substr($interval, 0, 1) != '-')
+ {
+ return "datetime('" . $date . "', '+" . $interval . " " . $datePart . "')";
+ }
+ else
+ {
+ return "datetime('" . $date . "', '" . $interval . " " . $datePart . "')";
+ }
+ }
}
View
21 libraries/joomla/database/query/sqlsrv.php
@@ -175,4 +175,25 @@ public function length($value)
{
return 'LEN(' . $value . ')';
}
+
+ /**
+ * Add to the current date and time.
+ * Usage:
+ * $query->select($query->dateAdd());
+ * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
+ *
+ * @param datetime $date The date to add to; type may be time or datetime.
+ * @param string $interval The string representation of the appropriate number of units
+ * @param string $datePart The part of the date to perform the addition on
+ *
+ * @return sring The string with the appropriate sql for addition of dates
+ *
+ * @since 13.1
+ * @note Not all drivers support all units.
+ * @link http://msdn.microsoft.com/en-us/library/ms186819.aspx for more information
+ */
+ public function dateAdd($date, $interval, $datePart)
+ {
+ return "DATEADD('" . $datePart . "', '" . $interval . "', '" . $date . "'" . ')';
+ }
}
View
38 tests/suites/unit/joomla/database/JDatabaseQueryTest.php
@@ -1834,4 +1834,42 @@ protected function setUp()
$this->_instance = new JDatabaseQueryInspector($this->dbo);
}
+
+ /**
+ * Data for the testDateAdd test.
+ *
+ * @return array
+ *
+ * @since 13.1
+ */
+ public function seedDateAdd()
+ {
+ return array(
+ // date, interval, datepart, expected
+ 'Add date' => array('2008-12-31', '1', 'DAY', "DATE_ADD('2008-12-31', INTERVAL 1 DAY)"),
+ 'Subtract date' => array('2008-12-31', '-1', 'DAY', "DATE_ADD('2008-12-31', INTERVAL -1 DAY)"),
+ 'Add datetime' => array('2008-12-31 23:59:59', '1', 'DAY', "DATE_ADD('2008-12-31 23:59:59', INTERVAL 1 DAY)"),
+ );
+ }
+
+ /**
+ * Tests the JDatabaseQuery::DateAdd method
+ *
+ * @param datetime $date The date or datetime to add to.
+ * @param string $interval The maximum length of the text.
+ * @param string $datePart The part of the date to be added to (such as day or micosecond)
+ * @param string $expected The expected result.
+ *
+ * @return void
+ *
+ * @dataProvider seedDateAdd
+ * @since 13.1
+ */
+ public function testDateAdd($date, $interval, $datePart, $expected)
+ {
+ $this->assertThat(
+ $this->_instance->dateAdd($date, $interval, $datePart),
+ $this->equalTo($expected)
+ );
+ }
}
View
49 tests/suites/unit/joomla/database/database/JDatabasePostgresqlQueryTest.php
@@ -6,6 +6,8 @@
* @copyright Copyright (C) 2005 - 2013 Open Source Matters. All rights reserved.
* @license GNU General Public License version 2 or later; see LICENSE.txt
*/
+require_once __DIR__ . '/JDatabaseQueryPostgresqlInspector.php';
+require_once JPATH_PLATFORM . '/joomla/database/query/postgresql.php';
/**
* Test class for JDatabasePostgresqlQuery.
@@ -23,6 +25,14 @@ class JDatabasePostgresqlQueryTest extends TestCase
protected $dbo;
/**
+ * The instance of the object to test.
+ *
+ * @var JDatabasePostgresqlQuery
+ * @since 12.3
+ */
+ private $_instance;
+
+ /**
* Data for the testNullDate test.
*
* @return array
@@ -118,6 +128,8 @@ protected function setUp()
$this->dbo = TestMockDatabaseDriver::create($this, '1970-01-01 00:00:00', 'Y-m-d H:i:s');
+ $this->_instance = new JDatabaseQueryPostgresqlInspector($this->dbo);
+
// Mock the escape method to ensure the API is calling the DBO's escape method.
$this->assignMockCallbacks(
$this->dbo,
@@ -1246,4 +1258,41 @@ public function testReturning()
'Tests rendered value.'
);
}
+ /**
+ * Data for the testDateAdd test.
+ *
+ * @return array
+ *
+ * @since 13.1
+ */
+ public function seedDateAdd()
+ {
+ return array(
+ // date, interval, datepart, expected
+ 'Add date' => array('2008-12-31', '1', 'day', "timestamp '2008-12-31' + interval '1 day'"),
+ 'Subtract date' => array('2008-12-31', '-1', 'day', "timestamp '2008-12-31' - interval '1 day'"),
+ 'Add datetime' => array('2008-12-31 23:59:59', '1', 'day', "timestamp '2008-12-31 23:59:59' + interval '1 day'"),
+ );
+ }
+
+ /**
+ * Tests the JDatabasePostgresqlQuery::DateAdd method
+ *
+ * @param datetime $date The date or datetime to add to.
+ * @param string $interval The maximum length of the text.
+ * @param string $datePart The part of the date to be added to (such as day or micosecond).
+ * @param string $expected The expected result.
+ *
+ * @return void
+ *
+ * @dataProvider seedDateAdd
+ * @since 13.1
+ */
+ public function testDateAdd($date, $interval, $datePart, $expected)
+ {
+ $this->assertThat(
+ $this->_instance->dateAdd($date, $interval, $datePart),
+ $this->equalTo($expected)
+ );
+ }
}
View
50 tests/suites/unit/joomla/database/database/JDatabaseQueryPostgresqlInspector.php
@@ -0,0 +1,50 @@
+<?php
+/**
+ * @package Joomla.UnitTest
+ * @subpackage Database
+ *
+ * @copyright Copyright (C) 2005 - 2012 Open Source Matters. All rights reserved.
+ * @license GNU General Public License version 2 or later; see LICENSE.txt
+ */
+
+require_once JPATH_PLATFORM . '/joomla/database/query/postgresql.php';
+
+/**
+ * Class to expose protected properties and methods in JDatabaseQuery for testing purposes.
+ *
+ * @package Joomla.UnitTest
+ * @subpackage Database
+ *
+ * @since 11.1
+ */
+class JDatabaseQueryPostgresqlInspector extends JDatabaseQueryPostgresql
+{
+ /**
+ * Sets any property from the class.
+ *
+ * @param string $property The name of the class property.
+ * @param string $value The value of the class property.
+ *
+ * @return void
+ *
+ * @since 11.1
+ */
+ public function __set($property, $value)
+ {
+ return $this->$property = $value;
+ }
+
+ /**
+ * Gets any property from the class.
+ *
+ * @param string $property The name of the class property.
+ *
+ * @return mixed The value of the class property.
+ *
+ * @since 11.1
+ */
+ public function get($property)
+ {
+ return $this->$property;
+ }
+}
View
50 tests/suites/unit/joomla/database/database/JDatabaseQuerySqliteInspector.php
@@ -0,0 +1,50 @@
+<?php
+/**
+ * @package Joomla.UnitTest
+ * @subpackage Database
+ *
+ * @copyright Copyright (C) 2005 - 2012 Open Source Matters. All rights reserved.
+ * @license GNU General Public License version 2 or later; see LICENSE.txt
+ */
+
+require_once JPATH_PLATFORM . '/joomla/database/query/sqlite.php';
+
+/**
+ * Class to expose protected properties and methods in JDatabaseQuery for testing purposes.
+ *
+ * @package Joomla.UnitTest
+ * @subpackage Database
+ *
+ * @since 13.1
+ */
+class JDatabaseQuerySqliteInspector extends JDatabaseQuerySqlite
+{
+ /**
+ * Sets any property from the class.
+ *
+ * @param string $property The name of the class property.
+ * @param string $value The value of the class property.
+ *
+ * @return void
+ *
+ * @since 13.1
+ */
+ public function __set($property, $value)
+ {
+ return $this->$property = $value;
+ }
+
+ /**
+ * Gets any property from the class.
+ *
+ * @param string $property The name of the class property.
+ *
+ * @return mixed The value of the class property.
+ *
+ * @since 13.1
+ */
+ public function get($property)
+ {
+ return $this->$property;
+ }
+}
View
51 tests/suites/unit/joomla/database/database/JDatabaseQuerySqlsrvInspector.php
@@ -0,0 +1,51 @@
+<?php
+/**
+ * @package Joomla.UnitTest
+ * @subpackage Database
+ *
+ * @copyright Copyright (C) 2005 - 2012 Open Source Matters. All rights reserved.
+ * @license GNU General Public License version 2 or later; see LICENSE.txt
+ */
+
+require_once JPATH_PLATFORM . '/joomla/database/query/sqlsrv.php';
+
+/**
+ * Class to expose protected properties and methods in JDatabaseQuery for testing purposes.
+ *
+ * @package Joomla.UnitTest
+ * @subpackage Database
+ *
+ * @since 13.1
+ */
+class JDatabaseQuerySqlsrvInspector extends JDatabaseQuerySqlsrv
+{
+ /**
+ * Sets any property from the class.
+ *
+ * @param string $property The name of the class property.
+ * @param string $value The value of the class property.
+ *
+ * @return void
+ *
+ * @since 13.1
+ */
+ public function __set($property, $value)
+ {
+ return $this->$property = $value;
+ }
+
+ /**
+ * Gets any property from the class.
+ *
+ * @param string $property The name of the class property.
+ *
+ * @return mixed The value of the class property.
+ *
+ * @since 13.1
+ */
+ public function get($property)
+ {
+ return $this->$property;
+ }
+}
+
View
93 tests/suites/unit/joomla/database/database/JDatabaseSqliteQueryTest.php
@@ -0,0 +1,93 @@
+<?php
+/**
+ * @package Joomla.UnitTest
+ * @subpackage Database
+ *
+ * @copyright Copyright (C) 2005 - 2013 Open Source Matters. All rights reserved.
+ * @license GNU General Public License version 2 or later; see LICENSE.txt
+ */
+
+require_once __DIR__ . '/JDatabaseQuerySqliteInspector.php';
+require_once JPATH_PLATFORM . '/joomla/database/query/sqlite.php';
+
+/**
+ * Test class for JDatabaseSqliteQuery.
+ *
+ * @package Joomla.UnitTest
+ * @subpackage Database
+ *
+ * @since 11.3
+ */
+class JDatabaseSqliteQueryTest extends TestCase
+{
+ /**
+ * @var JDatabase A mock of the JDatabase object for testing purposes.
+ */
+ protected $dbo;
+
+ /**
+ * The instance of the object to test.
+ *
+ * @var JDatabaseSqliteQuery
+ * @since 12.3
+ */
+ private $_instance;
+
+
+ /**
+ * Sets up the fixture.
+ *
+ * This method is called before a test is executed.
+ *
+ * @return void
+ *
+ * @since 13.1
+ */
+ protected function setUp()
+ {
+ parent::setUp();
+
+ $this->dbo = $this->getMockDatabase();
+
+ $this->_instance = new JDatabaseQuerySqliteInspector($this->dbo);
+ }
+
+/**
+ * Data for the testDateAdd test.
+ *
+ * @return array
+ *
+ * @since 13.1
+ */
+ public function seedDateAdd()
+ {
+ return array(
+ // date, interval, datepart, expected
+ 'Add date' => array('2008-12-31', '1', 'DAY', "datetime('2008-12-31', '+1 DAY')"),
+ 'Subtract date' => array('2008-12-31', '-1', 'DAY', "datetime('2008-12-31', '-1 DAY')"),
+ 'Add datetime' => array('2008-12-31 23:59:59', '1', 'DAY', "datetime('2008-12-31 23:59:59', '+1 DAY')"),
+ 'Add microseconds' => array('2008-12-31 23:59:59', '53', 'microseconds', "datetime('2008-12-31 23:59:59', '+0.053 seconds')"),
+ );
+ }
+
+ /**
+ * Tests the JDatabaseSqliteQuery::DateAdd method
+ *
+ * @param datetime $date The date or datetime to add to.
+ * @param string $interval The maximum length of the text.
+ * @param string $datePart The part of the date to be added to (such as day or micosecond)
+ * @param string $expected The expected result.
+ *
+ * @return void
+ *
+ * @dataProvider seedDateAdd
+ * @since 13.1
+ */
+ public function testDateAdd($date, $interval, $datePart, $expected)
+ {
+ $this->assertThat(
+ $this->_instance->dateAdd($date, $interval, $datePart),
+ $this->equalTo($expected)
+ );
+ }
+}
View
91 tests/suites/unit/joomla/database/database/JDatabaseSqlsrvQueryTest.php
@@ -0,0 +1,91 @@
+<?php
+/**
+ * @package Joomla.UnitTest
+ * @subpackage Database
+ *
+ * @copyright Copyright (C) 2005 - 2013 Open Source Matters. All rights reserved.
+ * @license GNU General Public License version 2 or later; see LICENSE.txt
+ */
+
+require_once __DIR__ . '/JDatabaseQuerySqlsrvInspector.php';
+require_once JPATH_PLATFORM . '/joomla/database/query/sqlsrv.php';
+
+/**
+ * Test class for JDatabaseSqlsrvQuery.
+*
+* @package Joomla.UnitTest
+* @subpackage Database
+*
+* @since 11.3
+*/
+class JDatabaseSqlsrvQueryTest extends TestCase
+{
+ /**
+ * @var JDatabase A mock of the JDatabase object for testing purposes.
+ */
+ protected $dbo;
+
+ /**
+ * The instance of the object to test.
+ *
+ * @var JDatabaseSqlsrvQuery
+ * @since 12.3
+ */
+ private $_instance;
+
+ /**
+ * Sets up the fixture.
+ *
+ * This method is called before a test is executed.
+ *
+ * @return void
+ *
+ * @since 13.1
+ */
+ protected function setUp()
+ {
+ parent::setUp();
+
+ $this->dbo = $this->getMockDatabase();
+
+ $this->_instance = new JDatabaseQuerySqlsrvInspector($this->dbo);
+ }
+
+ /**
+ * Data for the testDateAdd test.
+ *
+ * @return array
+ *
+ * @since 13.1
+ */
+ public function seedDateAdd()
+ {
+ return array(
+ // date, interval, datepart, expected
+ 'Add date' => array('2008-12-31', '1', 'day', "DATEADD('day', '1', '2008-12-31')"),
+ 'Subtract date' => array('2008-12-31', '-1', 'day', "DATEADD('day', '-1', '2008-12-31')"),
+ 'Add datetime' => array('2008-12-31 23:59:59', '1', 'day', "DATEADD('day', '1', '2008-12-31 23:59:59')"),
+ );
+ }
+
+ /**
+ * Tests the JDatabaseSqlsrvQuery::DateAdd method
+ *
+ * @param datetime $date The date or datetime to add to.
+ * @param string $interval The maximum length of the text.
+ * @param string $datePart The part of the date to be added to (such as day or micosecond)
+ * @param string $expected The expected result.
+ *
+ * @return void
+ *
+ * @dataProvider seedDateAdd
+ * @since 13.1
+ */
+ public function testDateAdd($date, $interval, $datePart, $expected)
+ {
+ $this->assertThat(
+ $this->_instance->dateAdd($date, $interval, $datePart),
+ $this->equalTo($expected)
+ );
+ }
+}
Please sign in to comment.
Something went wrong with that request. Please try again.