BigQuery标准SQL错误，完全不触摸日期字段时的时间戳无效 [英] BigQuery standard SQL error, invalid timestamp when date field is not touched at all

问题描述

此处的答案对我没有帮助.尽管我在一个相当简单的更新查询中遇到了类似的问题，如下所示(想法是在旧记录中添加sha-256编码).

The answer here doesn't help me. Although I am having similar issue in a rather simple update query, which looks like below (idea is add sha-256 encoding to legacy records).

UPDATE 
'customer.customers` 
SET emailHashCode = (SELECT 
TO_HEX(SHA256(e.emailAddress))
FROM
`customer.customers`,
UNNEST(emailAddresses) AS e LIMIT 1)
WHERE emailHashCode IS NULL

我收到此错误:

无法返回相对于Unix时期的-62135600400000000微秒的无效时间戳值.有效时间戳值的范围是[0001-01-1 00:00:00、9999-12-31 23:59:59.999999]；写入字段dateOfBirth时出错

Cannot return an invalid timestamp value of -62135600400000000 microseconds relative to the Unix epoch. The range of valid timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]; error in writing field dateOfBirth

表中有一个dataOfBirth字段，但是没有被触及.好奇是否缺少基本的东西?感谢您提供任何线索！

There's a dataOfBirth field in the table, but its not being touched. Curious if something basic is missing? Thanks for any clue!

推荐答案

在转到带有时间戳的问题之前-首先，我想提一下您的初始查询很可能是不正确的-我建议您考虑考虑以下问题(假设emailAddresses重复记录且其中包含emailAddress字段)

Before jumping to your issue with timestamp - first, I wanted to mention that most likely your initial query is not correct - I would recommend you to consider below one instead (assuming that emailAddresses is repeated record with emailAddress field in it)

UPDATE 'customer.customers` 
SET emailHashCode = (
  SELECT TO_HEX(SHA256(e.emailAddress))
  FROM UNNEST(emailAddresses) AS e 
  LIMIT 1
)
WHERE emailHashCode IS NULL

因此，现在回到您的问题:如Elliott所述-您需要在同一查询中更新无效时间戳.我尚未测试-但请尝试以下

So, now back to your question: as Elliott has mentioned - you need to update the invalid timestamps as part of the same query. I haven't tested - but try below

UPDATE 'customer.customers` 
SET emailHashCode = (
  SELECT TO_HEX(SHA256(e.emailAddress))
  FROM UNNEST(emailAddresses) AS e 
  LIMIT 1
), dateOfBirth = SAFE_CAST(dateOfBirth AS TIMESTAMP)
WHERE emailHashCode IS NULL

这篇关于BigQuery标准SQL错误，完全不触摸日期字段时的时间戳无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！