一个sqlserver数据库的系统时间错误的问题解决

你的名字 2023-01-19 04:34 7阅读 0赞

有个sqlserver数据库是架在一个docker里面的ubuntu系统上的，

在数据库里运行这个命令：

select getdate() as time

发现它的时间比正确的时间早8小时。

sqlserver的时间是随着操作系统的，只能改操作系统时间，

在ubuntu里执行date命令，可以看到它的时区是UTC的，要改为CST

运行 tzselect

> /usr/bin/tzselect: line 171:/home/ubuntu/iso3166.tab: No such file or directory
> 
> /usr/bin/tzselect: time zone files are not set up correctly

### 要安装tzdata ###

> ### **apt-get install tzdata** ###

安装tzdata后，tzselect即可正常运行。

然后 tzselect

root@sql2:/# tzselect
    Please identify a location so that time zone rules can be set correctly.
    Please select a continent, ocean, "coord", or "TZ".
     1) Africa
     2) Americas
     3) Antarctica
     4) Asia
     5) Atlantic Ocean
     6) Australia
     7) Europe
     8) Indian Ocean
     9) Pacific Ocean
    10) coord - I want to use geographical coordinates.
    11) TZ - I want to specify the time zone using the Posix TZ format.

填4，

Please select a country whose clocks agree with yours.
     1) Afghanistan           18) Israel                35) Palestine
     2) Armenia               19) Japan                 36) Philippines
     3) Azerbaijan            20) Jordan                37) Qatar
     4) Bahrain               21) Kazakhstan            38) Russia
     5) Bangladesh            22) Korea (North)         39) Saudi Arabia
     6) Bhutan                23) Korea (South)         40) Singapore
     7) Brunei                24) Kuwait                41) Sri Lanka
     8) Cambodia              25) Kyrgyzstan            42) Syria
     9) China                 26) Laos                  43) Taiwan
    10) Cyprus                27) Lebanon               44) Tajikistan
    11) East Timor            28) Macau                 45) Thailand
    12) Georgia               29) Malaysia              46) Turkmenistan
    13) Hong Kong             30) Mongolia              47) United Arab Emirates
    14) India                 31) Myanmar (Burma)       48) Uzbekistan
    15) Indonesia             32) Nepal                 49) Vietnam
    16) Iran                  33) Oman                  50) Yemen
    17) Iraq                  34) Pakistan

填9

Please select one of the following time zone regions.
    1) Beijing Time
    2) Xinjiang Time

填1

Therefore TZ='Asia/Shanghai' will be used.
    Local time is now:      Fri May  7 20:54:23 CST 2021.
    Universal Time is now:  Fri May  7 12:54:23 UTC 2021.
    Is the above information OK?
    1) Yes
    2) No

填1

You can make this change permanent for yourself by appending the line
            TZ='Asia/Shanghai'; export TZ
    to the file '.profile' in your home directory; then log out and log in again.
    
    Here is that TZ value again, this time on standard output so that you
    can use the /usr/bin/tzselect command in shell scripts:
    Asia/Shanghai

可以按照上面的做，也可以

> cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

这时再运行命令date，发现操作系统的时间正常了，但是sqlserver的时间仍然是错的，重启一下docker镜像

> docker stop my\_mssql2 // 我的sqlserver数据库docker镜像
> 
> docker start my\_mssql2

这时 sqlserver的时间也正确了