How to implement the DATE_PART function of Postgres in Druid sql?

Question

I have a sql query that I use to do some modification on time series data. Here is my sql below (This is based on Postgres. I am just highlighting the date modification part)

## 6 days interval
"date_trunc('month', created_at) + (date_part('day', created_at)::int - 1) / 6 * interval '6 day'"

## 10 min interval
"date_trunc('hour', created_at) + date_part('minute', created_at)::int / 10 * interval '10 min'"

This works fine but I can't replicate the date_part function in Druid. I looked up the documentation of Druid and found some related functions like

EXTRACT(unit FROM timestamp_expr)

TIMESTAMPADD(unit, count, timestamp)

TIME_SHIFT(timestamp_expr, period, step, [timezone])

I also looked up in stack overflow and I found an example for time truncation and extraction

https://stackoverflow.com/a/65913014/5550284

https://stackoverflow.com/a/65075393/5550284

I tried doing something like below

SELECT (date_trunc('hour', __time) + EXTRACT(MINUTE FROM __time) / 5)

But this gives me some weird result. I still can't connect the truncation with the specific extraction at n intervals. Can anyone help?

Answer 1

Add 6 Days

& retain time of day

"expression": "timestamp_shift(created_at, 'P6D', 1, 'UTC')"

& set time of day to midnight (truncate to day)

"expression": "timestamp_floor(timestamp_shift(created_at, 'P6D', 1, 'UTC'), 'P1D', null, 'UTC')"

Add 6 Minutes

"expression": "timestamp_shift(created_at, 'PT6M', 1, 'UTC')"