從 Unix 日期獲取年份 DATEPART
在我的數據庫中,日期保留為自 1970 年 1 月 1 日以來/之前經過的天數,我理解為 Unix 日期格式,例如。今天是 2018 年 5 月 23 日,值為 17674。
我正在嘗試
select
針對日期表中的列執行語句以僅顯示年份。如果我執行這個:
datepart(year, c.cko_date)
我得到了 1940 年代的日期(它們應該是最近幾年)。
當我跑
select datepart(year, getdate())
它正確地告訴我日期是 2018 年。
關於我做錯了什麼的任何建議?
假設該列是一個
int
,而不是一個varchar
列,你可以這樣做:datepart(year, c.cko_date + 25567)
25567 是 1900-01-01 和 1970-01-01 之間的天數;本質上,這調整了 unix 和 SQL Server 日期時間之間的差異。
SQL Server
datetime
是在 1900 年 1 月 1 日左右設計的,即“紀元”。將 SQL Server 日期視為表示 1900 年 1 月 1 日之前或之後的時間量的十進制值可能會有所幫助。時間是小數部分,例如午夜是 0.0,中午是 0.5,下午 6 點是 0.75。可以儲存在datetime
列中的值的有效範圍是1753-01-01 00:00:00
to9999-01-01 23:59:59.997
。在數值上,這對應於從 開始到-53690.0000000
結束的範圍2958463.99999998
。Unix
1970-01-01 00:00:00
用作它的 epoch。按照慣例,紀元以通用協調時間或 UTC 開始。對於北美,中央夏令時時區比 UTC 晚 5:00 小時。因此,要將值從 Unix 紀元時間值準確轉換為CDT 中的日期時間值,需要針對 5 小時的差異進行調整。因此,從 Unix 時間轉換為以天數表示的稍微更準確的範例是:--declare an integer representing the number of days since 1970-01-01 DECLARE @days_since_unix_epoch int = 17674; --2018-05-23 --show the datetime value in UTC SELECT UTC = CONVERT(datetime, @days_since_unix_epoch + 25567) --show the datetime value in North American CDT , CDT = CONVERT(datetime, @days_since_unix_epoch + 25567) - CONVERT(datetime, '05:00:00');
結果:
╔═════════════════════════╦═════════════════════════╗ ║ UTC ║ CDT ║ ╠═════════════════════════╬═════════════════════════╣ ║ 2018-05-23 00:00:00.000 ║ 2018-05-22 19:00:00.000 ║ ╚═════════════════════════╩═════════════════════════╝
雖然我們是技術人員,但通常 Unix 紀元儲存為自 以來的毫秒數
1970-01-01 00:00:00
,而不是天數。如果考慮到極高的準確性,那麼毫秒數可能很難處理,因為自 1970 年以來為了處理閏秒已經對時間進行了許多調整。根據我剛剛連結到的維基百科文章,自 1972 年以來,時間已經增加了 26 個一秒;要準確處理 1970 年到今天之間的日期,您需要適當添加 0 到 26 秒。我不確定 SQL Server 的datetime
結構是否能準確理解這些調整。另請注意,SQL Server 的
datetime
結構具有 3 毫秒的解析度。考慮以下:SELECT [1ms] = CONVERT(datetime, '2018-05-22 00:00:00.001') , [2ms] = CONVERT(datetime, '2018-05-22 00:00:00.002') , [3ms] = CONVERT(datetime, '2018-05-22 00:00:00.003')
輸出:
╔═════════════════════════╦═════════════════════════╦═════════════════════════╗ ║ 1 毫秒 ║ 2 毫秒 ║ 3 毫秒 ║ ╠═════════════════════════╬═════════════════════════╬═════════════════════════╣ ║ 2018-05-22 00:00:00.000 ║ 2018-05-22 00:00:00.003 ║ 2018-05-22 00:00:00.003 ║ ╚═════════════════════════╩═════════════════════════╩═════════════════════════╝
如果您需要更好的解析度,您應該考慮使用
datetime2
數據類型,因為它支持微秒精度,以及可能更大的日期和時間範圍:日期範圍:0001-01-01 到 9999-12-31
時間範圍:00:00:00 到 23:59:59.9999999