英文:
BigQuery: invalid time zone with UTC+5.5
问题
我们的数据包含一个时间列和一个时区列。不幸的是，一些时区是不规则的UTC偏移，如下所示：
SELECT DATETIME(TIMESTAMP '2008-12-25 15:30:00', "UTC+5.5") AS my_time;
无效的时区：UTC+5.5
去掉0.5可行，但这并不理想：
SELECT DATETIME(TIMESTAMP '2008-12-25 15:30:00', "UTC+5") AS my_time;
2008-12-25T20:30:00
有关如何将时间戳转换为具有0.5偏移的时区感知时间戳的想法吗？
英文:
The data we have involves a Time column and a timezone column. Unfortunately, some of the timezones are UTC-offsets that are funky, as below:
SELECT DATETIME(TIMESTAMP '2008-12-25 15:30:00', "UTC+5.5") AS my_time;
Invalid time zone: UTC+5.5
Removing the 0.5 works, but this is not ideal...
SELECT DATETIME(TIMESTAMP '2008-12-25 15:30:00', "UTC+5") AS my_time;
2008-12-25T20:30:00
Any thoughts on how to convert a timestamp to a timezone aware timestamp with a 0.5 Offset?
答案1
得分: 2
以下是翻译好的部分：
-- 示例数据
WITH sample_data AS (
SELECT TIMESTAMP '2008-12-25 15:30:00' time, "UTC+5" timezone
UNION ALL
SELECT TIMESTAMP '2008-12-25 15:30:00' time, "UTC+5.5" timezone
)
-- 查询从这里开始
SELECT SAFE.DATETIME(time, REGEXP_REPLACE(timezone, r'\.5$', r':30')) my_time
FROM sample_data
-- 查询结果
+---------------------+
| my_time |
+---------------------+
| 2008-12-25T20:30:00 |
| 2008-12-25T21:00:00 |
+---------------------+
英文:
You might consider below.
-- sample data
WITH sample_data AS (
SELECT TIMESTAMP '2008-12-25 15:30:00' time, "UTC+5" timezone
UNION ALL
SELECT TIMESTAMP '2008-12-25 15:30:00' time, "UTC+5.5" timezone
)
-- query starts here
SELECT SAFE.DATETIME(time, REGEXP_REPLACE(timezone, r'\.5$', r':30')) my_time
FROM sample_data
-- query result
+---------------------+
| my_time |
+---------------------+
| 2008-12-25T20:30:00 |
| 2008-12-25T21:00:00 |
+---------------------+
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论