bigquery标准sql错误，时间戳无效 [英] bigquery standard sql error, invalid timestamp

问题描述

我正在使用bigquery中的某些表，但收到此错误:

I'm playing with some tables in bigquery and I receive this error:

相对于Unix时代，无法返回-62169990264000000微秒的无效时间戳值.
有效时间戳记值的范围是[0001-01-1 00:00:00，9999-12-31 23:59:59.999999]

Cannot return an invalid timestamp value of -62169990264000000 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]

在旧版SQL中进行查询并按升序排序，它显示为0001-11-29 22:15:36 UTC

Doing the query in legacy sql and sorting ascending, it displays as 0001-11-29 22:15:36 UTC

如何转换为微秒?

这是查询:

#standardSQL
SELECT
  birthdate
FROM
  X
WHERE
  birthdate IS NOT NULL
ORDER BY
  birthdate ASC

推荐答案

** 强文本 **确认，即在BigQuery旧版SQL中

**strong text**Confirming , that in BigQuery Legacy SQL

SELECT USEC_TO_TIMESTAMP(-62169990264000000)

产生0001-11-29 22:15:36 UTC时间戳

produces 0001-11-29 22:15:36 UTC timestamp

而在BigQuery标准SQL中

whereas in BigQuery Standard SQL

SELECT TIMESTAMP_MICROS(-62169990264000000)  

产生错误:TIMESTAMP值超出允许范围:从0001-01-01 00:00:00.000000 + 00到9999-12-31 23:59:59.999999 + 00.

produces error: TIMESTAMP value is out of allowed range: from 0001-01-01 00:00:00.000000+00 to 9999-12-31 23:59:59.999999+00.

如何以微秒为单位进行转换?

How does it get transformed in microseconds?

时间戳
您可以将TIMESTAMP数据类型描述为UNIX时间戳或日历日期时间.BigQuery在内部将TIMESTAMP数据存储为UNIX时间戳，具有 microsecond精度 .

TIMESTAMP
You can describe TIMESTAMP data types as either UNIX timestamps or calendar datetimes. BigQuery stores TIMESTAMP data internally as a UNIX timestamp with microsecond precision.

详细了解时间戳类型

这篇关于bigquery标准sql错误，时间戳无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！