Sql-Server

從 Unix 日期獲取年份 DATEPART

  • January 10, 2019

在我的數據庫中,日期保留為自 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 Serverdatetime是在 1900 年 1 月 1 日左右設計的,即“紀元”。將 SQL Server 日期視為表示 1900 年 1 月 1 日之前或之後的時間量的十進制值可能會有所幫助。時間是小數部分,例如午夜是 0.0,中午是 0.5,下午 6 點是 0.75。可以儲存在datetime列中的值的有效範圍是1753-01-01 00:00:00to 9999-01-01 23:59:59.997。在數值上,這對應於從 開始到-53690.0000000結束的範圍2958463.99999998

Unix1970-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

引用自:https://dba.stackexchange.com/questions/207616