datetime - 获取本地化日期的毫秒数,考虑夏令时
问题描述
我在 Google BigQuery 中有如下所示的数据:
sample_date_time_UTC time_zone milliseconds_between_samples
-------- --------- ----------------------------
2019-03-31 01:06:03 UTC Europe/Paris 60000
2019-03-31 01:16:03 UTC Europe/Paris 60000
...
数据样本应定期进行,由milliseconds_between_samples
字段的值指示:
是time_zone
一个字符串,表示 Google Cloud支持的时区值
然后,我正在检查任何一天范围内的实际样本数与预期数量的比率(对于给定的日期,表示为本地日期time_zone
):
with data as
(
select
-- convert sample_date_time_UTC to equivalent local datetime for the timezone
DATETIME(sample_date_time_UTC,time_zone) as localised_sample_date_time,
milliseconds_between_samples
from `mytable`
where sample_date_time between '2019-03-31 00:00:00.000000+01:00' and '2019-04-01 00:00:00.000000+02:00'
)
select date(localised_sample_date_time) as localised_date, count(*)/(86400000/avg(milliseconds_between_samples)) as ratio_of_daily_sample_count_to_expected
from data
group by localised_date
order by localised_date
问题是这有一个错误,因为我已将一天中的预期毫秒数硬编码为86400000
. 这是不正确的,因为当夏令时在指定的time_zone
( Europe/Paris
) 开始时,一天会缩短 1 小时。夏令时结束时,白天增加 1 小时。
所以,上面的查询是不正确的。它在时区查询今年 3 月 31 日的数据Europe/Paris
(即在该时区开始夏令时)。那天的毫秒数应该是82800000
.
在查询中,如何获得指定的正确毫秒数localised_date
?
更新:
我尝试这样做以查看它返回的内容:
select DATETIME_DIFF(DATETIME('2019-04-01 00:00:00.000000+02:00', 'Europe/Paris'), DATETIME('2019-03-31 00:00:00.000000+01:00', 'Europe/Paris'), MILLISECOND)
那没用 - 我明白了86400000
解决方案
感谢@Juta,关于使用 UTC 时间进行计算的提示。当我按本地化日期对每天的数据进行分组时,我发现我可以使用以下逻辑通过获取“本地化”日期的开始和结束日期时间(UTC)来计算每天的毫秒数:
-- get UTC start datetime for localised date
-- get UTC end datetime for localised date
-- this then gives the milliseconds for that localised date:
datetime_diff(utc_end_datetime, utc_start_datetime, MILLISECOND);
因此,我的完整查询变为:
with daily_sample_count as (
with data as
(
select
-- get the date in the local timezone, for sample_date_time_UTC
DATE(sample_date_time_UTC,time_zone) as localised_date,
milliseconds_between_samples
from `mytable`
where sample_date_time between '2019-03-31 00:00:00.000000+01:00' and '2019-04-01 00:00:00.000000+02:00'
)
select
localised_date,
count(*) as daily_record_count,
avg(milliseconds_between_samples) as daily_avg_millis_between_samples,
datetime(timestamp(localised_date, time_zone)) as utc_start_datetime,
datetime(timestamp(date_add(localised_date, interval 1 day), time_zone)) as utc_end_datetime
from data
)
select
localised_date,
-- apply calculation for ratio_of_daily_sample_count_to_expected
-- based on the actual vs expected number of samples for the day
-- no. of milliseconds in the day changes, when transitioning in/out of daylight saving - so we calculate milliseconds in the day
daily_record_count/(datetime_diff(utc_end_datetime, utc_start_datetime, MILLISECOND)/daily_avg_millis_between_samples) as ratio_of_daily_sample_count_to_expected
from
daily_sample_count
推荐阅读
- css - CSS 使用 calc() 计算高度
- firebase - 由于 FirebaseInstanceId() 已弃用,如何重写以下代码?
- r - 尝试在 Linux 上安装 httpuv 包时出错
- c++ - 在 VSCode 中选择 C++ 编译器
- python - “from gremlin_python.process.graph_traversal import __”在 Python 中做了什么?
- java - 多个 thenReturn 如何在 Mockito 中工作?
- python - 如何获得可变 EMA 指标(熊猫、金融)?
- html - 如何在手机上更改字体大小?
- c - 运行测试时在我的 C 程序中出现分段错误
- javascript - Api 休息链接和 javascript 弹出窗口