3

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
Adelin
  • 18,144
  • 26
  • 115
  • 175

2 Answers2

3

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
)
MaFF
  • 9,551
  • 2
  • 32
  • 41
0

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

Alex
  • 31
  • 2