I'm currently working with prototyping some stuff with a dump of a MySQL (5.7) database where much of the data is time dependent. Most of the queries I write use the NOW() function and the results change over time as we get further away from the time the dump was taken.

Is there a way to "lock the database in time" to the time of the dump or is there some other way to deal with this sort of situation, other than replacing all references to NOW() with a hard coded time stamp?

New contributor
Johan Falkenjack is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

You can use set timestamp <unix_epoch> which will make now() be fixed -- always return that value. See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_timestamp

Note that this will not affect sysdate(). To fix sysdate, you can start MySQL with the --sysdate-is-now option which will make sysdate() return the same value as now(), so in conjunction with above sysdate() will now be fixed. See https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sysdate-is-now

(People coming from the Oracle world know this parameter as fixed_date.)

Your Answer

Johan Falkenjack is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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