BigQuery时间戳记采用什么格式? [英] What format does BigQuery timestamp take?

问题描述

我正在尝试将时间戳类型作为RFC3339字符串输入到BigQuery中:

 "2019-07-25T11:07:41-04:00" 

它似乎不起作用.BigQuery需要使用哪种格式的时间戳?该文档未指定输入.

解决方案

这是强制转换或强制转换的有效格式，但请注意，前导和尾随空格(或许多系统添加的时区偏移之前的空格)将导致强制失败.

我会建议一些类似的用法(使用 TIMESTAMP() ):

  TIMESTAMP(TRIM(ts_string)) 

...或使用 CAST() :

  CAST(TRIM(ts_string)AS TIMESTAMP) 

如果字符串中包含空格，则可以使用

...或更彻底地，请使用 REGEXP_REPLACE() :

  TIMESTAMP(REGEXP_REPLACE("2019-07-25T11:07:41 -04:00"，r"\ s +"，")) 

然后查看是否适合您.可能还需要在十六进制或文本编辑器中查看该值，您可以在其中查看字符串中是否有任何隐藏的字符.

如果您需要解决的问题与简单的替换方法搭配得不好，则可以使用

It doesn't seem to be working. What format does Timestamp type expect in BigQuery? The documentation doesn't specify input.

解决方案

That is a valid format for casting or coercion, but be mindful that leading and trailing whitespace (or whitespace before the timezone offset, which many systems add) will cause the coercion to fail.

I would suggest something along the lines of (using TIMESTAMP()):

TIMESTAMP(TRIM(ts_string))

. . . or using CAST():

CAST(TRIM(ts_string) AS TIMESTAMP)

If there's whitespace within the string, you could just replace it using REPLACE():

TIMESTAMP(REPLACE(ts_string," ",""))

. . . or more thoroughly, replace all types of whitespace including tab using REGEXP_REPLACE():

TIMESTAMP(REGEXP_REPLACE(" 2019-07-25T11:07:41 -04:00 ",r"\s+",""))

And see if that works for you. It may also be worth looking at the value in a hex or text editor where you can see if there are any hidden characters in the string.

If there are things you need to work around that don't play nicely with simple replacements, you can use PARSE_TIMESTAMP() and provide a pattern mapping the elements of the source string to a valid TIMESTAMP.

If you're still having issues, it would also help to know how you're trying to load this string—is this via an import/load job, or a SQL INSERT or UPDATE? Are you using the BQ console, API, bq command line tool, or a language-specific SDK? What's the source format of the data? All of these may be helpful clarifications on the question to get you a better answer. What error message are you being shown, and is it for a single value or all values?

It's possible that some of the values are in an invalid format and are causing the operation to fail. You could test this by importing a few rows of the data you know are in a valid format and seeing if you still get an error.

这篇关于BigQuery时间戳记采用什么格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！