BigQuery将字串转换为日期时间 [英] BigQuery converting string to datetime

问题描述

我正在使用BigQuery尝试创建一个表，该表的字符串列名为"DATAUTILIZACAO"，具有以下示例值:

I'm using BigQuery to try I have a table with a string column called 'DATAUTILIZACAO' that has the following sample values:

02/11/16 12:19:08,000000

02/11/16 12:19:08,000000

02/11/16 17:39:41,000000

02/11/16 17:39:41,000000

文本的格式设置为"DD/MM/YY HH:mm:ss"，我需要创建一个 DATETIME 类型的新列，其中包含 DATAUTILIZACAO 的值.

The text is formatted as "DD/MM/YY HH:mm:ss" I need to create a new column of type DATETIME containing the value of DATAUTILIZACAO.

如何从 DATAUTILIZACAO 获取值，将其格式设置为"YYYY-MM-DD HH:MI:SS"；并将其保存到新列中?

How can I get the value from DATAUTILIZACAO format it as "YYYY-MM-DD HH:MI:SS" and save it to the new column?

我可以直接使用Query + UDF吗?

Can I do that using Query+UDF directly ?

谢谢

狮子座

推荐答案

尝试以下操作(对于标准SQL，请参见

Try below (for Standard SQL - see Enabling Standard SQL and Migrating from legacy SQL)

WITH TheTable AS (
  SELECT '02/11/16 12:19:08,000000' AS DATAUTILIZACAO UNION ALL
  SELECT '02/11/16 17:39:41,000000' AS DATAUTILIZACAO 
)
SELECT DATAUTILIZACAO, PARSE_TIMESTAMP('%d/%m/%y %H:%M:%S,000000', DATAUTILIZACAO) AS parsedDATAUTILIZACAO
FROM TheTable

请参见 PARSE_TIMESTAMP()

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