1

On my machine I have a service to synchronise the SYSTEM time (including Time zone), and would like the DB will synchronize with the machine time once in a while.

When I change SYSTEM time, MySQL keep giving the old time when I use CURRENT_TIMESTAMP, It synchronize it only when I restart the MySQL service.

I would like to ask: is it possible, by update some system variable, or anything else, to set the DB time, to be equal to the SYSTEM time, or any other time, without a restart of the service?

I don't want to stop the DB at all. I think that maybe a sceduled task, which run once in a while, can be set, but what should it do?

To accept answer, it must pass the following test:

# change system time, date or timezone on the machine
select CURRENT_TIMESTAMP from dual; #--still show old time
call sync_time; #or some sync date + time + timezone commands... 
select CURRENT_TIMESTAMP from dual; #--will show updated time

I just need the sync_time procedure, that will cause the last select to show the machine date and local time.

@rathishDBA answer didn't helped at all, it only changed the MySQL variables, not sync the DB time. I tried it as the 'root' user and both first and last select gave me the same unsynced result.

For example see this picture: screen shot

System time = 2:07
CURRENT_TIMESTAMP = 11:37
  • Windows server 2008 64 bit or above – SHR Oct 29 '17 at 14:24
  • What timezone is Windows set to? – Rick James Oct 29 '17 at 14:38
  • I changed it several times for tests, I guess in the sample it was set to my time zone +2.5 hrs. the point is that there is difference. should I recompile the DB to solve the problem? it is the same in MySQL and in MariaDB – SHR Oct 29 '17 at 14:48
  • What datatype are you using? DATETIME? Or TIMESTAMP? – Rick James Oct 29 '17 at 16:01
  • SHOW VARIABLES LIKE '%zone%'; – Rick James Oct 29 '17 at 16:02
1

This solution worked for me, I hope it will work for you too:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-10-26 15:13:16 |
+---------------------+
1 row in set (0.07 sec)

mysql> SET GLOBAL time_zone = 'SYSTEM';
Query OK, 0 rows affected (0.07 sec)

mysql> show global variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | SYSTEM              |
+------------------+---------------------+
2 rows in set (0.18 sec)

mysql>

You need SUPER privilege to set this configuration value on run time to avoid system restart.

  • sorry, it didn't worked for me, I see the old timestamp,it will affect only after MySQL restart. see the edit in my question. – SHR Oct 26 '17 at 11:09
0

I'm not sure I understand the question; you are correct that you can only set the timezone on startup, but if you set SYSTEM then that means any subsequent calls to get the time will simply be passed to the OS. So if you see the time not change in MySQL then whatever you are doing in the OS to change it isn't being applied at that level.

A quick browse of the source code shows calls to gettimeofday() to get the system time.

  • see the screen shot I've uploaded: timestamp is different from system time, any replace doesn't reflected by the db untill DB restart. maybe this problem is only on windows – SHR Oct 29 '17 at 9:59

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.