DateDiff formula in Redshift

EMart

Hi Team,

The formula below was built as a beastmode but would like to move it to dimensions, the backend. However, "DATEDIFF()" isn't recognized in Redshift, any workarounds? Thanks

CASE

when
("date","published_date") >0 and
DATEDIFF("date","published_date") <=29 then '01 Month'

when
DATEDIFF("date","published_date") >29 and
DATEDIFF("date","published_date") <=59 then '02 Months'

when
DATEDIFF("date","published_date") >59 and
DATEDIFF("date","published_date") <=89 then '03 Months'

when
DATEDIFF("date","published_date") >89 and
DATEDIFF("date","published_date") <=119 then '04 Months'

when
DATEDIFF("date","published_date") >119 and
DATEDIFF("date","published_date") <=149 then '05 Months'

when
DATEDIFF("date","published_date") >149 and
DATEDIFF("date","published_date") <=179 then '06 Months'

when
DATEDIFF("date","published_date") >179 and
DATEDIFF("date","published_date") <=209 then '07 Months'

when
DATEDIFF("date","published_date") >209 and
DATEDIFF("date","published_date") <=239 then '08 Months'

when
DATEDIFF("date","published_date") >239 and
DATEDIFF("date","published_date") <=269 then '09 Months'

when
DATEDIFF("date","published_date") >269 and
DATEDIFF("date","published_date") <=299 then '10 Months'

when
DATEDIFF("date","published_date") >299 and
DATEDIFF("date","published_date") <=329 then '11 Months'

when
DATEDIFF("date","published_date") >329 and
DATEDIFF("date","published_date") <=359 then '12 Months'

ELSE '>13 Months'

 

end)

MarkSnodgrass

Here is your case statement with the DAY parameter added:

SELECT DATEDIFF(DAY,"date","published_date") df,

CASE

When DATEDIFF(DAY,"date","published_date") >0 and DATEDIFF(DAY,"date","published_date") <=29 then '01 Month'

when DATEDIFF(DAY,"date","published_date") >29 and DATEDIFF(DAY,"date","published_date") <=59 then '02 Months'

when DATEDIFF(DAY,"date","published_date") >59 and DATEDIFF(DAY,"date","published_date") <=89 then '03 Months'

when DATEDIFF(DAY,"date","published_date") >89 and DATEDIFF(DAY,"date","published_date") <=119 then '04 Months'

when DATEDIFF(DAY,"date","published_date") >119 and DATEDIFF(DAY,"date","published_date") <=149 then '05 Months'

when DATEDIFF(DAY,"date","published_date") >149 and DATEDIFF(DAY,"date","published_date") <=179 then '06 Months'

when DATEDIFF(DAY,"date","published_date") >179 and DATEDIFF(DAY,"date","published_date") <=209 then '07 Months'

when DATEDIFF(DAY,"date","published_date") >209 and DATEDIFF(DAY,"date","published_date") <=239 then '08 Months'

when DATEDIFF(DAY,"date","published_date") >239 and DATEDIFF(DAY,"date","published_date") <=269 then '09 Months'

when DATEDIFF(DAY,"date","published_date") >269 and DATEDIFF(DAY,"date","published_date") <=299 then '10 Months'

when DATEDIFF(DAY,"date","published_date") >299 and DATEDIFF(DAY,"date","published_date") <=329 then '11 Months'

when DATEDIFF(DAY,"date","published_date") >329 and DATEDIFF(DAY,"date","published_date") <=359 then '12 Months'

ELSE '>13 Months'

END monthstring

FROM "sample_dates"

I tested in my Redshift and it worked.

jessdoe

Hi EMart,

DATEDIFF works in Redshift, it just requires more arguments than MySQL. The below link should help when translating between the two languages:
https://docs.aws.amazon.com/redshift/latest/dg/r_DATEDIFF_function.html

MarkSnodgrass

You need to add the day parameter as the first part. Like this:

 DATEDIFF(DAY, "Date", CURRENT_DATE) 

You could also simplify your case statement if you used the month parameter instead, like this:

CASE WHEN  DATEDIFF(MONTH, "Date", CURRENT_DATE) <= 12 THEN LPAD(DATEDIFF(MONTH, "Date", CURRENT_DATE),2,'0') + ' Months'

ELSE '> 13 Months'

END

EMart

Hi there, Day or Month were not recognized parameters in the syntax. Greatly would appreciate to hear any additional alternatives I could take, thank you!

MarkSnodgrass

I would check your syntax because I tested it in Redshift and it worked.

EMart

Hi @MarkSnodgrass thank you! I tested the code you provided but it doesn't match with the data I ran with the initial code shared. Any way I can use the original code but with the proper syntax?

CASE

When DATEDIFF("date","published_date") >0 and DATEDIFF("date","published_date") <=29 then '01 Month'
when DATEDIFF("date","published_date") >29 and DATEDIFF("date","published_date") <=59 then '02 Months'
when DATEDIFF("date","published_date") >59 and DATEDIFF("date","published_date") <=89 then '03 Months'
when DATEDIFF("date","published_date") >89 and DATEDIFF("date","published_date") <=119 then '04 Months'
when DATEDIFF("date","published_date") >119 and DATEDIFF("date","published_date") <=149 then '05 Months'
when DATEDIFF("date","published_date") >149 and DATEDIFF("date","published_date") <=179 then '06 Months'
when DATEDIFF("date","published_date") >179 and DATEDIFF("date","published_date") <=209 then '07 Months'
when DATEDIFF("date","published_date") >209 and DATEDIFF("date","published_date") <=239 then '08 Months'
when DATEDIFF("date","published_date") >239 and DATEDIFF("date","published_date") <=269 then '09 Months'
when DATEDIFF("date","published_date") >269 and DATEDIFF("date","published_date") <=299 then '10 Months'
when DATEDIFF("date","published_date") >299 and DATEDIFF("date","published_date") <=329 then '11 Months'
when DATEDIFF("date","published_date") >329 and DATEDIFF("date","published_date") <=359 then '12 Months'
ELSE '>13 Months'

MarkSnodgrass

Here is your case statement with the DAY parameter added:

SELECT DATEDIFF(DAY,"date","published_date") df,

CASE

When DATEDIFF(DAY,"date","published_date") >0 and DATEDIFF(DAY,"date","published_date") <=29 then '01 Month'

when DATEDIFF(DAY,"date","published_date") >29 and DATEDIFF(DAY,"date","published_date") <=59 then '02 Months'

when DATEDIFF(DAY,"date","published_date") >59 and DATEDIFF(DAY,"date","published_date") <=89 then '03 Months'

when DATEDIFF(DAY,"date","published_date") >89 and DATEDIFF(DAY,"date","published_date") <=119 then '04 Months'

when DATEDIFF(DAY,"date","published_date") >119 and DATEDIFF(DAY,"date","published_date") <=149 then '05 Months'

when DATEDIFF(DAY,"date","published_date") >149 and DATEDIFF(DAY,"date","published_date") <=179 then '06 Months'

when DATEDIFF(DAY,"date","published_date") >179 and DATEDIFF(DAY,"date","published_date") <=209 then '07 Months'

when DATEDIFF(DAY,"date","published_date") >209 and DATEDIFF(DAY,"date","published_date") <=239 then '08 Months'

when DATEDIFF(DAY,"date","published_date") >239 and DATEDIFF(DAY,"date","published_date") <=269 then '09 Months'

when DATEDIFF(DAY,"date","published_date") >269 and DATEDIFF(DAY,"date","published_date") <=299 then '10 Months'

when DATEDIFF(DAY,"date","published_date") >299 and DATEDIFF(DAY,"date","published_date") <=329 then '11 Months'

when DATEDIFF(DAY,"date","published_date") >329 and DATEDIFF(DAY,"date","published_date") <=359 then '12 Months'

ELSE '>13 Months'

END monthstring

FROM "sample_dates"

I tested in my Redshift and it worked.

EMart

Hi @MarkSnodgrass Amazing, it worked! Thank you!