RisingWave Community - How can I query data between current time and 7 days back with JSONB data containing an Epoch milliseconds time field?

RisingWave Community

risingwave-community.slack.com

all-things-risingwave

How can I query data between current time and 7 days back with JSONB data containing an Epoch milliseconds time field?

Sereena wants to query data between the current time and 7 days back from a table that contains JSONB data with an Epoch milliseconds time field. Tianxiao suggested casting the Epoch milliseconds to a timestamp and using temporal filters. Sereena confirmed that using '7 DAYS' as the filter worked for her.

Sereena Eldhose

Asked on Jul 17, 2023

To query data between the current time and 7 days back with JSONB data containing an Epoch milliseconds time field, you can follow these steps:

SELECT *
FROM your_table
WHERE your_jsonb_data->>'epoch_millis_field'::bigint >= EXTRACT(EPOCH FROM NOW() - INTERVAL '7 DAYS') * 1000
AND your_jsonb_data->>'epoch_millis_field'::bigint <= EXTRACT(EPOCH FROM NOW()) * 1000;

Cast the Epoch milliseconds to a timestamp using the following SQL query:

SELECT TO_TIMESTAMP(CAST(your_jsonb_data->>'epoch_millis_field' AS DECIMAL) / 1000);

For temporal filters, refer to the documentation provided by Tianxiao Shen: Temporal Filters.

Jul 17, 2023Edited by