Converting Timestream data types such Interval to Seconds

Question

Is there a way to convert Interval type to total seconds, minutes, etc? I would like to be able to ouput the difference in seconds between two Timestamps

For instance (note I am just denoting the type with the :: syntax):

(2022-04-29 13:15:53::Timestamp) - (2022-04-29 12:58:30::Timestamp) = (0 00:17:23.000000000::Interval Day to Seconds)

Is there way to convert these values to other types?

I have tried cast(x as int), that gives "Cannot cast interval day to second to integer".

I am seeing little documentation on the actions that can be taken on  Interval types. Is there more significant documentation than the following?

https://docs.aws.amazon.com/timestream/latest/developerguide/supported-data-types.html
https://docs.aws.amazon.com/timestream/latest/developerguide/conversion-functions.html
https://docs.aws.amazon.com/timestream/latest/developerguide/date-time-functions.html

Having a complete SQL reference document for Timeseries syntax would be great!

Answer

I did manage to find one solution so far to this.

You can either:

1. Convert Timestamps to ms before doing your math with to_milliseconds()
 
        to_milliseconds( (2022-04-29 13:15:53::Timestamp)) - to_milliseconds((2022-04-29 12:58:30::Timestamp)) = 1043000::int?

2. Or apparently you can cast an Interval with the same function to a number

to_milliseconds((2022-04-29 13:15:53::Timestamp) - (2022-04-29 12:58:30::Timestamp)) = 1043000::int?

Again if someone could point me to a the entire supported SQL syntax for this service that would be awesome. I do not see the function to_milliseconds() even listed in the documentation I linked to above.

Thank you for any help offered.

Answer

Here is the final answer I was looking for, and now it's here for reference!

As vaguely mentioned [here](https://docs.aws.amazon.com/timestream/latest/developerguide/reference.html), but not explicitly stated as such the syntax is TrinoDB's with support for some? all? functions.

Here is a link to the datetime functions specifically: https://trino.io/docs/current/functions/datetime.html

Converting Timestream data types such Interval to Seconds

相關內容