Date Trunc in Redshift

dmoney1

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.

Unknown

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) 

 

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

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

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

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)