一种方法是创建一个表(演示中的dst),其中包含夏令时的开始和结束日期以及时间。 如果started_at在相应年份的两个日期之间,则可以根据-1(true)或-0(false)调整-5小时偏移量。 例如datetime(started_at,'unixepoch','-'||(5 - (started_at BETWEEN dst_start AND dst_end))||' hours') 下面是一个demo:
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')
;
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
;
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;
SELECT
datetime(started_at, 'unixepoch') AS unadjusted,
CASE
WHEN
strftime('%s', started_at, 'unixepoch') /* helps with some locals issues */
BETWEEN
strftime(
'%s', strftime('%Y', started_at, 'unixepoch') /* Year according to the started_at date */
|| '-03-31 01:00:00', /* 31st March 1am UTC */
'-' || (strftime('%w', strftime('%Y', started_at, 'unixepoch') || '-03-31')) || ' days' /* go back to the last Sunday */
)
AND
strftime(
'%s', strftime('%Y', started_at, 'unixepoch') /* Year according to the started_at date */
|| '-10-31 01:00:00', /* 31st Oct 1am UTC */
'-' || (strftime('%w', strftime('%Y', started_at, 'unixepoch') || '-10-31')) || ' days' /* go back to the last Sunday */
)
/* i.e. if started_at is between daylight saving dates */
THEN datetime(started_at, 'unixepoch', '+2 hours')
/* if not between the daylight saving dates */
ELSE datetime(started_at, 'unixepoch', '+1 hours')
END
AS adjusted
FROM bt;
2条答案
按热度按时间oo7oh9g91#
有三种方法,
*您可能希望先查看第三个解决方案。
第一个解决方案
一种方法是创建一个表(演示中的dst),其中包含夏令时的开始和结束日期以及时间。
如果started_at在相应年份的两个日期之间,则可以根据-1(true)或-0(false)调整-5小时偏移量。
例如
datetime(started_at,'unixepoch','-'||(5 - (started_at BETWEEN dst_start AND dst_end))||' hours')
下面是一个demo:
字符串
结果包括(突出显示的dst日期时间):-
x1c 0d1x的数据
的
第二种解决方案
另一种方法是根据从3月的第二个星期日开始到11月的第一个星期日结束的规则来确定当前的夏令时开始和结束。然而,如图所示,这仅限于本年度的日期:
下面是一个demo:
型
结果是:
的
第三个(也许是最好的解决方案)
第一个解决方案需要维护一个额外的表,第二个解决方案不能很好地满足本年度以外的日期。第三种选择是第二种选择的进一步发展,使用started_at日期起的年份作为调整的基础。因此,这可能是更好的解决方案:
型
这使用相同的方式将数据加载到biketransactions(bt)表中,导致:
的
后来:
x1c4d 1x的
jobtbby32#
@MikeT的解决方案#3工作得很好,但我想发布我必须为欧洲做出的调整(在欧洲夏令时开始和结束于3月/10月的最后一个星期日)。
字符串