sqlite: Can I mock the current time `now()` for testing?

Question

I build a test suite for a program that runs a lot of SQL statements with now() against a sqlite data base. I would like to mock the sqlite clock --- to test behaviour that is designed to take several days within one second. I do not want to touch the system clock.

Is this possible with sqlite?

Answer 1

Example in Python:

import sqlite3
def mock(*_):
    return '1984-01-02 11:22:33'
connection = sqlite3.connect(':memory:')
print(connection.execute('SELECT DATETIME()').fetchone()[0])
connection.create_function('DATETIME', -1, mock)
print(connection.execute('SELECT DATETIME()').fetchone()[0])

Output

2022-03-01 11:23:05
1984-01-02 11:22:33

Answer 2

SQLite's built-in functions can be redefined with sqlite3_create_function.

[Edit by Yaakov Belch --- additional information:]

• In perl, you can access this API through the $dbh->sqlite_create_function method of a DBD::SQLite data base handle.
• For python or PHP see this stackoverflow answer.