字符串到日期时间的转换Bigquery [英] String to date time conversion Bigquery

问题描述

我有一个源表，其中的日期以字符串格式存储，如下所示:

1984-10-28 00:00:00
1988-11-22 00:00:00 
1990-01-08 00:00:00
1983-06-22 00:00:00 

然后创建一个表test1，如下所示:

mk -t xyz.test birthdate:timestamp

当我编写查询以将源表中的所有日期转换为时间戳并将其保存到test1时，会出现错误:

SELECT FORMAT_UTC_USEC(birthdate) AS birthdate FROM [sourcetable] LIMIT 10

Error: Invalid schema update. Field birthdate has changed type
Job ID: [Project]:job_5P4gFVl4wiYuyXL0vY5VZNtCZOU

如果我做错了任何事情，请提供帮助

解决方案

没关系.我找到了.我忽略了bigquery的查询参考文档的大部分内容.

TIMESTAMP()
将日期字符串转换为TIMESTAMP数据类型.

SELECT TIMESTAMP("2012-10-01 01:02:03");

返回时间:UTC 2012-10-01 01:02:03

因此正确的查询如下: 从[sourcetable] LIMIT 10中选择SELECT TIMESTAMP(birthdate)作为生日

I have a source table which has dates stored in string format as below:

1984-10-28 00:00:00
1988-11-22 00:00:00 
1990-01-08 00:00:00
1983-06-22 00:00:00 

Then created a table test1 as below:

mk -t xyz.test birthdate:timestamp

When I write a query to convert all the dates from source table to timestamp and save it to test1 it gives an error:

SELECT FORMAT_UTC_USEC(birthdate) AS birthdate FROM [sourcetable] LIMIT 10

Error: Invalid schema update. Field birthdate has changed type
Job ID: [Project]:job_5P4gFVl4wiYuyXL0vY5VZNtCZOU

Please help if I am doing anything wrong

解决方案

Never mind. I found it. I was overlooking a big part of bigquery's query reference document.

TIMESTAMP()
Convert a date string to a TIMESTAMP data type.

SELECT TIMESTAMP("2012-10-01 01:02:03");

Returns: 2012-10-01 01:02:03 UTC

So the correct query as below: SELECT TIMESTAMP(birthdate) AS birthdate FROM [sourcetable] LIMIT 10

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