尝试将时代代码字段(字符串类型)转换为时间戳

Question

试图从时代代码转换为时间戳，在遗留SQL中，它工作得很好，而在标准SQL中，我得到了一个错误。字段名custom_field_6 (字符串类型字段)表示划时代的时间，我希望将它转换为时间戳(在标准SQL中)。

在旧版SQL中，我执行了以下操作：select custom_field_6 as custom_field_6, timestamp(custom_field_6) as custom_field_6_convert FROM [yellowhead-visionbi-rivery:yellowhead_prod.affise_conversions]

查询输出

在标准sql中：

select custom_field_6 as custom_field_6, cast(custom_field_6 as date) as custom_field_6_converted FROMyellowhead-visionbi-rivery.yellowhead_prod.affise_conversions

我所犯的错误：“无效日期”

Answer 1

下面是BigQuery标准SQL的id

#standardSQL
SELECT 
  custom_field_6, 
  TIMESTAMP_SECONDS(CAST(custom_field_6 AS INT64)) custom_field_6_convert
FROM `yellowhead-visionbi-rivery.yellowhead_prod.affise_conversions`  

可以使用虚拟数据进行测试，如下所示

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '1540051185' custom_field_6 UNION ALL
  SELECT '1540252572' 
)
SELECT 
  custom_field_6, 
  TIMESTAMP_SECONDS(CAST(custom_field_6 AS INT64)) custom_field_6_convert
FROM `project.dataset.table`   

有结果

Row custom_field_6  custom_field_6_convert   
1   1540051185      2018-10-20 15:59:45 UTC  
2   1540252572      2018-10-22 23:56:12 UTC