How to make mysql ```UNIX_TIMESTAMP()``` return custom timestamp?

Question

For simplicity pytest launches my_server and mysql_server (inside docker). my_server connects to mysql_server (over host network), then he gets value of SELECT UNIX_TIMESTAMP(); from mysql_server process it to day of week, then prints number according to day of week:

('mon', 1667768400), # print 1
('tue', 1667854800), # print 2
('wed', 1667941200), # print 3
...

I'm looking for something like this, but I want to change it globally for all opened sessions:

-- one can connect to mysql and
-- change value for current session,
-- but I need to set it globally or
-- somehow change value for another
-- session.
-- for example for monday:

mysql> set session timestamp = 1667768400;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1667768400 |
+------------------+
1 row in set (0.00 sec)
mysql> set global timestamp = 1667768400;
ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL

Notes:

I can't change code inside my_server, it's very expensive;
I can't change host machine timestamp, because such an action would require to run tests with sudo, which is strange requirement for a test.

Kind regards,
Oleg

Your MySQL client opens a connection to MySQL. Each connection have a lot of properties. One of the properties is "additional command which is executed during the connection establishing". Find this property and add set session timestamp to its value. — Akina
– Akina, Commented Nov 7, 2022 at 18:04
just run tests with sudo; allow the command you need in a sudoers file. — ysth
– ysth, Commented Nov 7, 2022 at 18:16
No, I need this set session timestamp only for tests, my_server is a production service, which doesn't need this setting for a session. No, proposing any manipulations with host default time is a bad practice. I want to change database properties, I thought I made it clear. — oleg
– oleg, Commented Nov 7, 2022 at 18:22

itby · Accepted Answer · 2022-11-07 21:07:45Z

1

I dare to suggest the idea of MySQL-proxy for modifying queries transmitted to the SQL server. You can look at this as a starting point: https://github.com/mysql/mysql-proxy

answered Nov 7, 2022 at 21:07

itby

262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

oleg Over a year ago

proxy is a good, flexible solution

Collectives™ on Stack Overflow

How to make mysql ```UNIX_TIMESTAMP()``` return custom timestamp?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related