RisingWave Community - How to convert a timestamp string to a real timestamp in RisingWave?

RisingWave Community

risingwave-community.slack.com

all-things-risingwave

How to convert a timestamp string to a real timestamp in RisingWave?

I have a timestamp as a string in a JSONB column that looks like this '2023-11-20T14:11:23Z' and I want to convert it to a real timestamp to apply date-functions and calculations. In Postgres, it worked with the CAST function. However, when creating a view with this function in RisingWave, querying the view throws an error. How can I achieve my goal to convert this string to a timestamp?

Georg Boegerl

Asked on Nov 21, 2023

The value '2023-11-20T14:11:23Z' is actually a timestamp with time zone, where the suffix 'Z' stands for UTC.
To convert this string to a real timestamp in RisingWave, you can use the following syntax:

CAST(payload->>'createdTime' as timestamp with time zone)

Nov 21, 2023Edited by