Date Trunc in Redshift

dmoney1
dmoney1 ⚪️
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 🟠

    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 ⚪️

    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 🟠

    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.

Categories