如何将AWS时间流数据库中的biint转换为时间戳

Question

我想执行这个查询：

select *  FROM "data-api-timestream-test"."table_test" where time = 1637339664248

我得到错误：

line 1:71: '=' cannot be applied to timestamp, bigint

我也试过

select *  FROM "data-api-timestream-test"."table_test" where time = cast(1637339664248 as timestamp)

我得到错误：

line 1:73: Cannot cast bigint to timestamp

Answer 1

1。Unix epoch到时间戳

要在Timestream中将unix epoch时间转换为时间戳，可以使用Timestream函数from_milliseconds:

from_milliseconds(unixtime_in_millisecond)

在您的示例中：

SELECT
*
FROM "data-api-timestream-test"."table_test"
WHERE
time = from_milliseconds(1637339664248)

2.到unix epoch的时间戳

对于，另一种方法——将时间戳转换为自unix epoch起源以来的毫秒——可以使用函数to_milliseconds:

to_milliseconds(CURRENT_TIME)

完整示例：

SELECT 
time, 
to_milliseconds(time) AS unixtime,
from_milliseconds(to_milliseconds(time)) AS unixtime_to_time
FROM (
SELECT 
CURRENT_TIMESTAMP AS time
)

Answer 2

您可以尝试这些变体，实际上它们几乎相同：

select *  FROM "data-api-timestream-test"."table_test" where time = from_iso8601_timestamp('2021-11-19T16:34:24.248Z')

或

select *  FROM "data-api-timestream-test"."table_test" where time = '2021-11-19 16:34:55.248000000'

只需将您的BigInt值转换为时间戳，即提前的ISO 8601时间戳