SQL Server Timezone – Handling Clock Changes

sql servertimetimezone

With the clock change ahead in Europe – how does SQL Server deal with the clock change? Are there known issues related to a clock change?

Best Answer

SQL Server just uses the operating system's time. However, if your application code relies on functions like GETDATE(), then your code needs to be able to handle jumps forward (or backward) in time.

For example, if your have code that does something like this:

SET @StartDate = GETDATE()
(Do some work)
IF DATEDIFF(MM, @StartDate, GETDATE()) > 60 ....

That code can fail. Long term, consider using GETUTCDATE() instead and storing dates with DATETIMEOFFSET - but that's a big code change that you can't really do quickly before changing the date on a server.

Related Solutions

Mysql – My MySQL server time is not the same as the Server

I had this exact same problem after I had updated my server time. I just had to restart the MySql server then it reset the time to match my server time.

/etc/init.d/mysqld restart

Reference - https://major.io/2007/07/01/mysql-time-zone-different-from-system-time-zone/

MySQL – Risks of Changing Clock Time on Server Host

Binary logs will record the events sequentially with whatever timestamp it has. Using any binary log around the time of a clock change makes the particular log unusable for PITR (Point-in-Time Recovery).

If the DB Server is a Slave, Replication may skip processing certain events.

If you have Binary Logging or Replication Enabled, I would do the following

On DB1, stop writes to the database
On DB2, run SHOW SLAVE STATUS\G multiple times to make sure there are no new commands being processed and Seconds_Behind_Master is 0
On DB2, STOP SLAVE;
On DB1, RESET MASTER; (Erases all binlogs)
Stop MySQL Shutdown on DB1
Stop MySQL Shutdown on DB2
Set the Linux Time on DB1 and DB2 (Make sure times match)
Start MySQL Shutdown on DB2
Start MySQL Shutdown on DB1
On DB1, RESET MASTER; (Erases all binlogs again)
On DB2, CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=4;
On DB2, START SLAVE;

The idea here is to make sure all binary logs and relay logs are time synchronized. The NOW() function could then be correctly interpreted from these logs.

CAVEAT

Be very careful about changing clock times when it may not be necessary when it comes to Daylight Saving Time. Since the clock time is written in binary logs in seconds since Jan 1, 1970, it may not be necessary. Since the clock is 75 minutes off, do this ASAP. Don't wait until DST kicks in.

Best Answer

Related Solutions

Mysql – My MySQL server time is not the same as the Server

MySQL – Risks of Changing Clock Time on Server Host

CAVEAT

Related Question