Zend_Test: Dealing with Dates

I have been writing a lot of integration tests lately and wanted to share a quick tip about how to deal with datetime and timestamp columns in the table you are testing.  When comparing a dataset with the current state of a table it is hard to deal with modification dates, etc.  So  the best thing to do is to filter out those dates and not try to compare them.

Here is a simple example.

The database:

CREATE TABLE bugs (
  bug_id            INTEGER NOT NULL PRIMARY KEY,
  bug_description   VARCHAR(100),
  bug_status        VARCHAR(20),
  create_date       DATETIME,
  mod_date          TIMESTAMP
);

The expected result: (Note the missing mod_date column.)

<?xml version="1.0" encoding="UTF-8" ?>
<dataset>
  <table name="bugs">
    <column>bug_id</column>
    <column>bug_description</column>
    <column>bug_status</column>
   <column>create_date</column>
   <row>
	<value>1</value>
	<value>this is a new bug</value>
	<value>open</value>
	<value>2010-10-15 08:06:53</value>
  </row>
  </table>
</dataset>

Now for the test:

....
public function testSetAcl() {
	$model->addBug('this is a new bug');

	$expected = $this->createXmlDataSet('expected.xml');
	$actual = new PHPUnit_Extensions_Database_DataSet_QueryDataSet($this->getConnection());
	$actual->addTable('bugs', 'SELECT bug_id, bug_description, bug_status FROM bugs');
	$this->assertDataSetsEqual($expected, $actual);
}

The key to this test passing, is the 2nd paramter being passed to $actual->addTable. The default for $actual->addTable is to do a ‘SELECT * FROM bugs’ but that then brings back the mod_date, which you cannot deal with. So specifying the columns to return with ‘SELECT bug_id, bug_description, bug_status FROM bugs’ will remove mod_date from the test and make your life easier.