Update documentation for the datedifff macro: add another set of quotes

[louisguitton]

Hey everyone,
it is my understanding from using dbt_utils.dateadd this morning on a Redshift cluster and failing that the macro is not supported for Redshfit.

Error

Completed with 1 errors:

Database Error in model labelling_set (models/marts/ml-entities/labelling_set.sql)
  function date_add("unknown", integer, integer) does not exist
  HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
  compiled SQL at target/compiled/of_models/marts/ml-entities/labelling_set.sql

Code https://github.com/fishtown-analytics/dbt-utils/blob/master/macros/cross_db_utils/dateadd.sql

I'm willing to open a PR for this and dig deeper into the logic of adapter_macro etc. if that sounds like something that could be useful (for my own usecase I just wrote manually the date_add redshift statement).

[jtcohen6]

Hi @louisguitton, the macro is supported on Redshift via the default implementation. Often, when Redshift returns an error like

function date_add("unknown", integer, integer) does not exist

This indicates an issue with the datatypes of the arguments passed. Specifically, the third argument is from_date_or_timestamp, and it should be of type date or timestamp, not integer.

[louisguitton]

You're right about the datatype of the argument. I realised that shortly after writing the issue.

But I still can't make the macro work unfortunately.

Working dbt code without the macro

Select
...
where p.published_at between dateadd(day, -5, '2019-09-24') and '2019-09-24'

With the macro - not working

Select
...
where p.published_at between {{ dbt_utils.dateadd('day', -5, '2019-09-24') }} and '2019-09-24'

When I look at the compiled SQL for that latter version, I get

Select
...
where p.published_at between
    dateadd(
        day,
        -5,
        2019-09-24   -- instead of '2019-09-24'
        )
 and '2019-09-24'

which explains the integer datatype.

[drewbanin]

this macro doesn't automatically add quotes to the from_date_or_timestamp argument. This allows you to specify a column name (which you would not want to quote) or a date literal, like 2019-09-24. You can use a second set of quotes to make sure that that your from_date_or_timestamp argument is quoted appropriately, eg:

-- note: there are single-quotes inside of the double-quotes here
{{ dbt_utils.dateadd('day', -5, "'2019-09-24'") }}

There's another macro in this package, literal, that will add this second set of quotes for you. That would look like:

{{ dbt_utils.dateadd('day', -5, dbt_utils.literal("2019-09-24")) }}

Both of these are equivalent, so sort of up to your preference which one you use!

Let's update the documentation here to correctly show usage information for the dateadd macro. You can see a correct example which uses two sets of quotes in the datediff macro right below it.