CODEWITHSUNDEEP
mysql
hello_harry
hello_harry

Reputation: 1305

How to change CURRENT_TIMESTAMP temporarily to another time in MySQL

I have a fairly large and complex mysql query to blackbox test and this query is time sensitive, it has a lot of conditions based on current_timestamp.

My goal is to make some tests so it always passes or fails. I'm thinking of mocking the value of current_timestamp temporarily to a fixed date before running the query and set it back to original value after the query.

Is it something thats doable?

I cannot modify the query itself (i.e.: find replace current_timestamp to something else)

Thanks

Upvotes: 5

Views: 3389

Answers (1)

Dylan Su
Dylan Su

Reputation: 6065

By setting system variable 'timestamp'. To restore it to current timestamp again, set it to DEFAULT.

mysql> SET TIMESTAMP = UNIX_TIMESTAMP('2015-01-01');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SET TIMESTAMP = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-03-08 09:50:16 |
+---------------------+
1 row in set (0.00 sec)

Upvotes: 6

Related Questions