add_days produces erroring DATEADD

[Guy_Srinivasan]

I’m trying to add a custom dimension to bucket days by groups of three and still have the column values be dates, not a number.

add_days(-round(diff_days(${investigations.created_date}, now()) / 3, 0) * 3, now())

This produces the following sql (for Redshift).

CASE WHEN DATE(DATEADD(day,(-ROUND(CAST(CAST(EXTRACT(day from CAST(GETDATE() AS TIMESTAMP) - CAST((DATE(CAST(investigations.created_at as TIMESTAMP) )) AS TIMESTAMP)) AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION) AS FLOAT), 0)) * 3, GETDATE() )) = DATEADD(day,(-ROUND(CAST(CAST(EXTRACT(day from CAST(GETDATE() AS TIMESTAMP) - CAST((DATE(CAST(investigations.created_at as TIMESTAMP) )) AS TIMESTAMP)) AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION) AS FLOAT), 0)) * 3, GETDATE() ) THEN TO_CHAR(DATEADD(day,(-ROUND(CAST(CAST(EXTRACT(day from CAST(GETDATE() AS TIMESTAMP) - CAST((DATE(CAST(investigations.created_at as TIMESTAMP) )) AS TIMESTAMP)) AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION) AS FLOAT), 0)) * 3, GETDATE() ), 'YYYY-MM-DD') ELSE TO_CHAR(DATEADD(day,(-ROUND(CAST(CAST(EXTRACT(day from CAST(GETDATE() AS TIMESTAMP) - CAST((DATE(CAST(investigations.created_at as TIMESTAMP) )) AS TIMESTAMP)) AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION) AS FLOAT), 0)) * 3, GETDATE() ), 'YYYY-MM-DD HH24:MI:SS') END

Which has this error:

ERROR: function pg_catalog.date_add(“unknown”, double precision, timestamp without time zone) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

Suggestions on how to work around this?
My preferred output is something like the list (‘2020-03-03’, ‘2020-02-29’, ‘2020-02-26’, …)

[Quinn_Wildman]

Your example actually works for me. I’ve pasted the SQL I get and it is a little different. I’d be inclined to experiment with SQL Runner and compare what you got and what I got and see if you can make any inferences.

This all said, this feels like some sort of timezone conversion issue which has bitten me in the past. I’d also suggest you experiment with convert_tz in your dimension (although I tried and it did not make a difference), and timezone settings in admin (if you can).

CASE WHEN DATE(DATEADD(day,(-CAST(ROUND(CAST(CAST(EXTRACT(day from CAST(GETDATE() AS TIMESTAMP) - CAST((DATE(newtransaction.date )) AS TIMESTAMP)) AS BIGINT) AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION), 0) AS BIGINT)) * 3, GETDATE() )) = DATEADD(day,(-CAST(ROUND(CAST(CAST(EXTRACT(day from CAST(GETDATE() AS TIMESTAMP) - CAST((DATE(newtransaction.date )) AS TIMESTAMP)) AS BIGINT) AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION), 0) AS BIGINT)) * 3, GETDATE() ) THEN TO_CHAR(DATEADD(day,(-CAST(ROUND(CAST(CAST(EXTRACT(day from CAST(GETDATE() AS TIMESTAMP) - CAST((DATE(newtransaction.date )) AS TIMESTAMP)) AS BIGINT) AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION), 0) AS BIGINT)) * 3, GETDATE() ), 'YYYY-MM-DD') ELSE TO_CHAR(DATEADD(day,(-CAST(ROUND(CAST(CAST(EXTRACT(day from CAST(GETDATE() AS TIMESTAMP) - CAST((DATE(newtransaction.date )) AS TIMESTAMP)) AS BIGINT) AS DOUBLE PRECISION) / CAST(3 AS DOUBLE PRECISION), 0) AS BIGINT)) * 3, GETDATE() ), 'YYYY-MM-DD HH24:MI:SS') END