CODEWITHSUNDEEP
amazon-web-servicesamazon-timestream
Adelin
Adelin

Reputation: 19001

How to convert a bigint in AWS timestream DB to timestamp?

I want to execute this query:

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

I get the error:

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

I also tried

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

I get the error:

line 1:73: Cannot cast bigint to timestamp

Upvotes: 3

Views: 1291

Answers (2)

MaFF
MaFF

Reputation: 10086

1. Unix epoch to timestamp

To convert a unix epoch time to timestamp in Timestream you can use Timestream function from_milliseconds:

from_milliseconds(unixtime_in_millisecond)

In your example:

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

2. Timestamp to unix epoch

For, the other way around - converting a timestamp to milliseconds since unix epoch origin - you can use function to_milliseconds:

to_milliseconds(CURRENT_TIME)

Full example:

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

Upvotes: 3

Alex
Alex

Reputation: 31

You can try these variants, actually they are almost same:

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

or

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

Just convert your BigInt value to timestamp, of ISO 8601 timestamp in advance

Upvotes: 0

Related Questions