在sqlite中通過紀元時間轉換日期的夏令時問題

我正在處理一個非常大的資料，其中精度非常重要，并且正在觀察一天中不同時間的人類行為。我使用以下代碼從紀元日期中提取日期（使用 rsqlite - 基本上 sqlite 只允許在 R 中使用）查詢構建相同）：

dbGetQuery(db,"SELECT datetime(started_at, 'unixepoch', 'utc','-4 hours') FROM biketransactions limit 3")

我使用的資料記錄在紐約，但一個問題是紐約并不總是比 UTC 時區晚 4 小時。如果沒有夏令時，則相差 -5 小時。使用這種方法，我將在“夏季”期間獲得正確的時間戳，但“冬季”中的資料將始終假定在一小時后。這將極大地影響我的最終結果。考慮到夏令時，有哪些方法可以轉換紀元時間？

uj5u.com熱心網友回復：

這里有3種方法，

• 第一個使用帶有夏令時開始和結束日期和時間的表格。

• 第二個使用 CTE（通用表運算式）為當前年份動態構建單行表（可能不適合）

• 第三個在第二個的基礎上更進一步，因為它結合了 CTE（夏令時的動態確定）基于從start_at日期開始的年份。所以它可以處理任何日期（假設夏令時的確定仍然是從 3 月第 2 個星期日凌晨 2 點到 11 月第 1 個星期日 01:59:59）。

• 您不妨先查看第三種解決方案。

第一個解決方案

一種方法是有一個表格（演示中的dst），其中包含夏令時的開始和結束日期和時間。

然后，如果started_at 在相應年份的兩個日期之間，您可以根據-1（真）或-0（假）調整-5 小時偏移量。

例如datetime(started_at,'unixepoch','-'||(5 - (started_at BETWEEN dst_start AND dst_end))||' hours')

這是一個演示：-

DROP TABLE IF EXISTS dst;
DROP TABLE IF EXISTS bt;
/* Create and populate DST table  with start of ds and end of ds*/
CREATE TABLE IF NOT EXISTS dst (dst_start INTEGER, dst_end INTEGER);
INSERT INTO dst VALUES 
    (strftime('%s','2022-03-13 02:00:00'),strftime('%s','2022-11-06 01:59:59')),
    (strftime('%s','2023-03-12 02:00:00'),strftime('%s','2023-11-05 01:59:59')),
    (strftime('%s','2024-03-10 02:00:00'),strftime('%s','2024-11-03 01:59:59')),
    (strftime('%s','2025-03-09 02:00:00'),strftime('%s','2025-11-02 01:59:59'))
;
/* Create the biketransactions table and load with some test data */
CREATE TABLE IF NOT EXISTS bt (started_at INTEGER);
with cte(started_at) AS (
    SELECT strftime('%s','now') 
    UNION ALL SELECT strftime('%s',started_at,'unixepoch','10 days') FROM cte LIMIT 100
    )
INSERT INTO bt SELECT * FROM cte;



/* select 3 values based upon the bt started_at 
    1. The actual stored date
    2. Whether (1) or not (0) daylight savings is applicable
    3. the adjusted datetime    
*/
SELECT datetime(started_at,'unixepoch') AS UTC,
    started_at BETWEEN dst_start AND dst_end AS dstflag,
    datetime(started_at,'unixepoch','-'||(5 - (started_at BETWEEN dst_start AND dst_end))||' hours') AS adjusted
    
FROM bt 
/* join gets the applicable row from the dst table i.e. the row for the year */
JOIN dst ON strftime('%Y',started_at,'unixepoch') = strftime('%Y',dst_start,'unixepoch')
;

結果包括（突出顯示的 dst 日期時間）：-

第二種解決方案

另一種方法是根據從 3 月的第 2 個星期日開始到 11 月的第 1 個星期日結束的規則來確定當前的夏令時開始和結束。但是，如圖所示，這僅限于當年的日期：-

這是一個演示：-

DROP TABLE IF EXISTS bt;
/* Create the biketransactions table and load with some test data */
CREATE TABLE IF NOT EXISTS bt (started_at INTEGER);
with cte(started_at) AS (
    SELECT strftime('%s','now') 
    UNION ALL SELECT strftime('%s',started_at,'unixepoch','10 days') FROM cte LIMIT 200
    )
INSERT INTO bt SELECT * FROM cte;

/* 
    as a CTE (Commaon Tabel Expression) to determine the start and end dates and times of daylight savings for the current year
    note that this is restricted to only working for dates in the current year
*/
WITH ds AS (SELECT 
    strftime('%Y','now') AS year, /* YEAR */
    strftime('%s',strftime('%Y','now')||'-03-01 02:00:00',' '||(14 - strftime('%w',strftime('%Y','now')||'-03-01'))||' days') AS dst_start,
    strftime('%s',strftime('%Y','now')||'-11-01 01:59:59',' '||(7 - strftime('%w',strftime('%Y','now')||'-11-01'))||' days') AS dst_end
    )
SELECT 
    datetime(started_at,'unixepoch'), 
    started_at BETWEEN (SELECT dst_start FROM ds) AND (SELECT dst_end FROM ds),
    datetime(started_at,'unixepoch','-'||(5-(started_at BETWEEN (SELECT dst_start FROM ds) AND (SELECT dst_end FROM ds)))||' hours') AS adjusted 
    FROM bt 
;

結果是：-

• 從上面可以看出，當夏令時適用于除當年以外的任何其他年份時，將沒有正確的時間。

第三（也許是最好的解決方案）

第一個解決方案需要維護一個額外的表，第二個解決方案不能很好地滿足除當年以外的日期。這第三個選項通過使用從started_at日期開始的年份作為調整的基礎進一步采用了第二個選項。因此，這可能是更可取的解決方案：-

SELECT 
    datetime(started_at,'unixepoch') AS unadjusted,
    CASE 
        WHEN
            started_at 
            BETWEEN 
                strftime(
                    '%s',strftime('%Y',started_at,'unixepoch') /* Year according to the started_at date */
                        ||'-03-01 02:00:00',' ' /* 1st March */
                        ||(14 - strftime('%w',strftime('%Y',started_at,'unixepoch')||'-03-01'))||' days' /* 2nd Sunday */
                        )
            AND
                strftime(
                    '%s',strftime('%Y',started_at,'unixepoch') /* Year according to the started_at date */
                        ||'-11-01 01:59:59',' ' /* 1st Nov */
                        ||(7 - strftime('%w',strftime('%Y',started_at,'unixepoch')||'-11-01'))||' days' /* 1st Sunday */
                        )
        THEN /* i.e. if started_at is between daylight savings date thus 1 */ datetime(started_at,'unixepoch','-4 hours')
        ELSE /* if not between the daylight savings dates thus 0*/ datetime(started_at,'unixepoch','-5 hours')
    END as adjusted
FROM bt;

這使用相同的方式將資料加載到biketransactions（為簡潔起見）表中，結果：-

后來：-

• 只是實際的和調整后的日期和時間。
• 夏令時期間的日期位于突出顯示的塊中

標籤：sqlite约会时间夏令时rsqlite

上一篇：插入的資料未顯示在資料庫中

下一篇：在c#中以特定格式顯示當前日期