0

I've read that SQL Server:

  • uses system time zone on Windows (reg key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\TimeZoneKeyName)
  • uses TZ env var on Linux or /etc/timezone.

I wonder what happens with data stored in DB if I change system time zone on the server and restart it.

Should I see the same dates/time returned by columns of type datetime2 via JDBC / ODBC API?

Will there be a gap equals to TZ difference after the change?

Doesn't that ruin technique of storing data in UTC? Like you are obligated not only to "normalize" dates (to UTC) but also keep SQL Server instances running in UTC...

1 Answer 1

2

As mentioned in DATETIME2 documentation, there is no time zone information stored, so DATETIME2 (as well as DATETIME, DATE and TIME) data is not effected if server's time zone is changed. If you insert '2023-01-01 14:15' while the server's time zone is UTC, a SELECT on the same record will return '2023-01-01 14:15' even if server's time zone is changed to Eastern Standard Time (UTC-5).

If you need to keep track of time zone of each record, you should use DATETIMEOFFSET datatype. Again, even if you change server's time zone, DATETIMEOFFSET data will keep the time zone that was specified when inserted/updated.

However, changing server's time zone will affect result of GETDATE()/SYSDATETIME()/SYSDATETIMEOFFSET() functions: the first two will always return current time based on server's time zone, while SYSDATETIMEOFFSET() also includes the time zone information.

If you want to work with UTC time zone, it would be better to use GETUTCDATE() or SYSUTCDATE() to get current UTC time, regardless of server's time zone.

3
  • Are JDBC / ODBC drivers affected by SQL Server time zone? I see that changing client time zone changes datetime2 saved/retrieved value. Does SQL Server communication protocol insensitive to a server timezone?
    – gavenkoa
    Mar 7, 2023 at 15:05
  • 1
    Depending on specific JDBC or ODBC drivers, platform or framework, there could be options to automatically translate datetime value to client's time zone, but it's not a feature part of JDBC/ODBC drivers provided by Microsoft, as far as I know. Could you give more details about the setup where you experienced that behaviour? Mar 7, 2023 at 16:06
  • I didn't see translation options for JDBC in SQL Server: learn.microsoft.com/en-us/sql/connect/jdbc/… Haven't checked jTDS and other libs. I'm looking to guaranties SQL Server provides, related to TZ settings...
    – gavenkoa
    Mar 7, 2023 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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