BigQuery 自动将时间戳时区转换为 UTC [英] BigQuery automatically converts timestamp timezone to UTC

问题描述

我有一张这样的桌子:

和这样的文件:

然后我使用 python 将文件加载到大查询中:

from google.cloud import bigquery as bqgs_path = 'gs://test_share_file/testTimestamp.csv'bq_client = bq.Client.from_service_account_json(gcp_creds_fp)ds = bq_client.dataset('test1')tbl = ds.table('testTimestamp')job_config = bq.LoadJobConfig()job_config.write_disposition = bq.job.WriteDisposition.WRITE_APPENDjob_config.skip_leading_rows = 1 # 跳过标题load_job = bq_client.load_table_from_uri(gs_path, tbl, job_config=job_config)res = load_job.result()

然而在表中，两个时间戳都是 UTC 时间！

如何让第二列在东部时间?

解决方案

您可以即时将第一列转换"为东部时间 - 类似于下面的示例

#standardSQL与 t AS (选择时间戳 '2018-05-07 22:40:00+00:00' 作为 ts)SELECT ts, STRING(ts, '-04:00') timestamp_eastern从T

<块引用>

我正在处理......固执......

您可以创建包含您需要的所有逻辑的视图，以便客户端查询该视图而不是原始表

#standardSQL创建视图`project.dataset.your_view` ASSELECT ts, STRING(ts, '-04:00') timestamp_eastern从`project.dataset.your_table`

<块引用>

我确实认为大查询无法显示时区中的时间很奇怪

时间戳表示绝对时间点，独立于任何时区或约定，例如夏令时.
解析时间戳或格式化时间戳以供显示时使用时区.时间戳值本身不存储特定时区.字符串格式的时间戳可能包括时区.如果未明确指定时区，则使用默认时区 UTC.

查看有关时间戳的更多信息输入

I have a table as such:

and a file as such: https://storage.googleapis.com/test_share_file/testTimestamp.csv

which looks like:

and I load the file to big query using python as such:

from google.cloud import bigquery as bq

gs_path = 'gs://test_share_file/testTimestamp.csv'
bq_client = bq.Client.from_service_account_json(gcp_creds_fp)
ds = bq_client.dataset('test1')
tbl = ds.table('testTimestamp')

job_config = bq.LoadJobConfig()
job_config.write_disposition = bq.job.WriteDisposition.WRITE_APPEND
job_config.skip_leading_rows = 1 # skip header
load_job = bq_client.load_table_from_uri(gs_path, tbl, job_config=job_config)
res = load_job.result()

and yet in the table, both timestamps are in UTC time!

How do I get the second column to be in eastern time?

解决方案

You can "transform" first column into eastern time on-fly - something like in below example

#standardSQL
WITH t AS (
  SELECT TIMESTAMP '2018-05-07 22:40:00+00:00' AS ts
)
SELECT ts, STRING(ts, '-04:00') timestamp_eastern
FROM t

I am dealing with ... stubbornness ...

You can create view which will consists of all the logic you need in place so client will query that view instead of original table

#standardSQL
CREATE VIEW `project.dataset.your_view` AS 
SELECT ts, STRING(ts, '-04:00') timestamp_eastern 
FROM `project.dataset.your_table`

I do think it odd that big query can't display a time in a timezone

A timestamp represents an absolute point in time, independent of any time zone or convention such as Daylight Savings Time.
Time zones are used when parsing timestamps or formatting timestamps for display. The timestamp value itself does not store a specific time zone. A string-formatted timestamp may include a time zone. When a time zone is not explicitly specified, the default time zone, UTC, is used.

See more about Timestamp type

这篇关于BigQuery 自动将时间戳时区转换为 UTC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！