Skip to content

    • Beast Mode

    Beast Mode

    DateDiff formula in Redshift

    Member
    in Beast Mode

    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)

    Tagged:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      Here is your case statement with the DAY parameter added:

      1. SELECT DATEDIFF(DAY,"date","published_date") df,
      2. CASE
      3. When DATEDIFF(DAY,"date","published_date") >0 and DATEDIFF(DAY,"date","published_date") <=29 then '01 Month'
      4. when DATEDIFF(DAY,"date","published_date") >29 and DATEDIFF(DAY,"date","published_date") <=59 then '02 Months'
      5. when DATEDIFF(DAY,"date","published_date") >59 and DATEDIFF(DAY,"date","published_date") <=89 then '03 Months'
      6. when DATEDIFF(DAY,"date","published_date") >89 and DATEDIFF(DAY,"date","published_date") <=119 then '04 Months'
      7. when DATEDIFF(DAY,"date","published_date") >119 and DATEDIFF(DAY,"date","published_date") <=149 then '05 Months'
      8. when DATEDIFF(DAY,"date","published_date") >149 and DATEDIFF(DAY,"date","published_date") <=179 then '06 Months'
      9. when DATEDIFF(DAY,"date","published_date") >179 and DATEDIFF(DAY,"date","published_date") <=209 then '07 Months'
      10. when DATEDIFF(DAY,"date","published_date") >209 and DATEDIFF(DAY,"date","published_date") <=239 then '08 Months'
      11. when DATEDIFF(DAY,"date","published_date") >239 and DATEDIFF(DAY,"date","published_date") <=269 then '09 Months'
      12. when DATEDIFF(DAY,"date","published_date") >269 and DATEDIFF(DAY,"date","published_date") <=299 then '10 Months'
      13. when DATEDIFF(DAY,"date","published_date") >299 and DATEDIFF(DAY,"date","published_date") <=329 then '11 Months'
      14. when DATEDIFF(DAY,"date","published_date") >329 and DATEDIFF(DAY,"date","published_date") <=359 then '12 Months'
      15. ELSE '>13 Months'
      16. END monthstring
      17. FROM "sample_dates"

      I tested in my Redshift and it worked.

      image.png
      **Check out my Domo Tips & Tricks Videos

      **Make sure to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.

    Answers

    • Contributor

      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

    • Coach

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

      1.  DATEDIFF(DAY, "Date", CURRENT_DATE)

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

      1. CASE WHEN  DATEDIFF(MONTH, "Date", CURRENT_DATE) <= 12 THEN LPAD(DATEDIFF(MONTH, "Date", CURRENT_DATE),2,'0') + ' Months'
      2. ELSE '> 13 Months'
      3. END
      **Check out my Domo Tips & Tricks Videos

      **Make sure to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.
    • Member

      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!

    • Coach

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

      image.png
      **Check out my Domo Tips & Tricks Videos

      **Make sure to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.
    • Member

      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'

    • Coach
      Answer ✓

      Here is your case statement with the DAY parameter added:

      1. SELECT DATEDIFF(DAY,"date","published_date") df,
      2. CASE
      3. When DATEDIFF(DAY,"date","published_date") >0 and DATEDIFF(DAY,"date","published_date") <=29 then '01 Month'
      4. when DATEDIFF(DAY,"date","published_date") >29 and DATEDIFF(DAY,"date","published_date") <=59 then '02 Months'
      5. when DATEDIFF(DAY,"date","published_date") >59 and DATEDIFF(DAY,"date","published_date") <=89 then '03 Months'
      6. when DATEDIFF(DAY,"date","published_date") >89 and DATEDIFF(DAY,"date","published_date") <=119 then '04 Months'
      7. when DATEDIFF(DAY,"date","published_date") >119 and DATEDIFF(DAY,"date","published_date") <=149 then '05 Months'
      8. when DATEDIFF(DAY,"date","published_date") >149 and DATEDIFF(DAY,"date","published_date") <=179 then '06 Months'
      9. when DATEDIFF(DAY,"date","published_date") >179 and DATEDIFF(DAY,"date","published_date") <=209 then '07 Months'
      10. when DATEDIFF(DAY,"date","published_date") >209 and DATEDIFF(DAY,"date","published_date") <=239 then '08 Months'
      11. when DATEDIFF(DAY,"date","published_date") >239 and DATEDIFF(DAY,"date","published_date") <=269 then '09 Months'
      12. when DATEDIFF(DAY,"date","published_date") >269 and DATEDIFF(DAY,"date","published_date") <=299 then '10 Months'
      13. when DATEDIFF(DAY,"date","published_date") >299 and DATEDIFF(DAY,"date","published_date") <=329 then '11 Months'
      14. when DATEDIFF(DAY,"date","published_date") >329 and DATEDIFF(DAY,"date","published_date") <=359 then '12 Months'
      15. ELSE '>13 Months'
      16. END monthstring
      17. FROM "sample_dates"

      I tested in my Redshift and it worked.

      image.png
      **Check out my Domo Tips & Tricks Videos

      **Make sure to <3 any users posts that helped you.
      **Please mark as accepted the ones who solved your issue.
    • Member

      Hi @MarkSnodgrass Amazing, it worked! Thank you!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories