Date Trunc in Redshift

dmoney1
dmoney1 Member
in Magic ETL

Domo Community,

 

I am trying to rewrite this calculation in Domo but keep getting a "FROM" error where the Dataflow is not recognizing the input data set. Have any of you ever encountered or used this type of statement?

 

SELECT

sum("Process Time") FILTER (WHERE
date_trunc('month', "Date (Lead Created)") >= date_trunc('month', current_date - INTERVAL '3 months') AND
date_trunc('month', "Date (Lead Created)") <= date_trunc('month', current_date))

 

FROM

master_dataset

 

It works on my database side perfectly.

Best Answer

  • Unknown
    Unknown
    Answer ✓

    Hi, @dmoney1,

     

    Looks like Redshift doesn't support interval literals using months or years. You might try the dateadd() function instead:

    dateadd(month,-3,current_date) 

     

Answers

  • dthierjung
    dthierjung Contributor

    I believe that you have to have RedShift enabled on your Domo instance by your sales rep, as it is not enabled by default. And I believe you need to have a use-case for it as well since it sounds like it costs Domo money to enable it.

     

    Do you know if it's enabled for you?

  • dmoney1
    dmoney1 Member

    Thanks for the advice. I am currently running this query in Redshift, but I think there are restrictions on the queries that can be called in Resdhift. 

  • dthierjung
    dthierjung Contributor

    It's definitely possible. 

     

    The best route probably is to reach out to your sales rep and have them confirm the details of what Domo's RedShift is capable of.

  • Unknown
    Unknown
    Answer ✓

    Hi, @dmoney1,

     

    Looks like Redshift doesn't support interval literals using months or years. You might try the dateadd() function instead:

    dateadd(month,-3,current_date) 

     

Categories