How can I use the now function in a Redshift psql query?

Question

"Specified types or functions (one per INFO message) not supported on Redshift tables."

I got this error. When it happens through PyCharm I don't get the other part of the message. But when I ran it directly in Terminal I got messages just before the error saying "INFO: Function "now()" not supported."

It seems that I can use "now()", but only in a static query. If I include a database table in the query then it doesn't work???

This works:

    WITH cteDates AS (
         SELECT
            (now() - interval '7 days')::date as start_date,
            (now() - interval '1 day')::date as end_date
    ) select * from cteDates;

But this doesn't:

     WITH cteDates AS (
        SELECT
            (now() - interval '7 days')::date as start_date,
            (now() - interval '1 day')::date as end_date
    ),

    cteMain AS (
            SELECT
                extract(dow from  el.created_on) as day_number,
                COUNT(*) as myCount
            FROM mySchema.myTable1 e
            JOIN mySchema.myTable2 el on e.id = el.e_id
            JOIN mySchema.myTable3 s on el.s_id = s.id
            WHERE e.a_id = 7
                  AND el.created_on::date >= (select d.start_date from cteDates as d)
                AND el.created_on::date <= (select d.end_date from cteDates as d)
           Group by day_number
               )
          Select * from cteMain;

How can I make the second one work?

I have to have a completely static query. I can't use %s, or have any other kind of variable.

Answer 1

Now() has been deprecated. Getdate() works.