Is there a feasible way of testing SQL queries that contain things like CURRENT_DATE and NOW() in a unit test?

For example:

public function deletePastEntries(): bool {
    $sql = "DELETE FROM queue WHERE period_end < NOW();";
    return $this->db->query($sql)->execute();

And in my unit test:

class QueueTest extends AbstractDatabaseTestCase
    // ...<snip>...boilerplate stuff

    public function testDeletePastEntries(): void {

        $actualTable = $this->getConnection()->createQueryTable('queue', 'SELECT id, user, period_start, period_end FROM queue ORDER BY id;');

        $expectedTable = $this->createArrayDataSet([
            'queue' => [/* stuff that should remain */]

        static::assertTablesEqual($expectedTable, $actualTable);

I have tagged PHPUnit but this question should be generic enough for any kind of database testing.

For mocking usages of things like new \DateTime() (defaulting to "now"), I can change the function to accept a parameter. I suppose I can change all the queries to take a parameter instead of NOW() or time-dependent functions, but I'm not sure if changing all the queries for this purpose is the right way going forward, especially for things that do not make sense to pass in arbitrary dates/times. In addition, using parameters would mean that I have to construct the values of those parameters in the application and pass them in.

  • 1
    What about generating the relevant database contents on the fly in preparation of the test? – πάντα ῥεῖ Mar 15 '18 at 19:10
  • @πάνταῥεῖ - That's a good answer. – JeffO Mar 16 '18 at 21:15
  • @JeffO Sure. But I'm not answering every obvious shit beyond comments. – πάντα ῥεῖ Mar 16 '18 at 21:22

Make the functions that should use "current date" receive a parameter for it, and default it to current date. Something like below:

public function deletePastEntries($now = null)
    if (! $now) {
        $now = time();

    $sql = "DELETE FROM queue WHERE period_end < ?;";
    return $this->db->query($sql)->execute($now);

Now you can pass a fixed date as "current date" in tests, or just pass nothing and run with the actual current date otherwise.

Another popular approach is to not use now() except in one place, and use your own myNow() everywhere instead:

public function deletePastEntries()
    $sql = "DELETE FROM queue WHERE period_end < ?;";
    return $this->db->query($sql)->execute(myNow());

// Elsewhere:
public function myNow() {
  return now();

// For tests, redefine it:
public function myNow() {

Depending on which version of myNow() is available in the global context, you have either current time, or predictable constant time.

Instead of topmost level function, you can use a more specific shared / top-level configuration object, which is also a popular pattern:

| improve this answer | |
  • It's not ideal to pass a parameter in a test that isn't passed in production, since then the code path being tested isn't the same one that runs in production and the test has limited value. I would probably inject an object representing either the current time or a clock into the constructor. If you're using a service container then it should be able to do that injection in production, and you'll pass a fixed value time or a fake clock manually for a unit test. For instance if we accidentally type $now = tiem(); the unit test won't fail, since that line isn't covered. – bdsl Mar 15 '18 at 21:45
  • @bdsl: If you have an object with a constructor, you can inject data into it. But please note that you'd have to have a parameter to inject to. But your critique does have a merit: instead of passing a clock to a particular function, you can replace the clock in the global context. I'm updating my answer. – 9000 Mar 15 '18 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.