Date Trunc in Redshift

Reply
Highlighted
White Belt

Date Trunc in Redshift

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.


Accepted Solutions
Brown Belt

Re: Date Trunc in Redshift

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) 

 


All Replies
Green Belt

Re: Date Trunc in Redshift

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?

White Belt

Re: Date Trunc in Redshift

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. 

Green Belt

Re: Date Trunc in Redshift

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.

Brown Belt

Re: Date Trunc in Redshift

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) 

 

Announcements
We want your feedback to improve the Dojo Community! If you are coming to the Dojo Award Ceremony next week Click here to learn more!