How do I get the date from seven days ago?

Question

I'd like to make a chart that gives me data from last week.

I tried to use this code but it errors out.

where (time_stamp::date > dateadd(day, -7, now()::date))

I also tried

(ud.time_stamp::date between now() and dateadd(day, -7, now()::date))

It gives me this error

Error running query: Specified types or functions (one per INFO message) not supported on Redshift tables.

user_1144035 · Answer

In Redshift, I would write this as:

where time_stamp >= current_date - interval '6 day'

I suspect that time_stamp might be used for partitioning and you want to be careful about comparisons involving functions on it.

user_1863229 · Answer

Try this version:

WHERE time_stamp > DATEADD(day, -7, current_date)

If you also wanted to limit the upper bound of data to today at midnight (i.e. data from last week, ending in midnight of today), you could use:

WHERE time_stamp >= DATEADD(day, -7, current_date) AND time_stamp < current_date

user_3652345 · Answer

You can try this below option-

where (time_stamp::date > NOW() - interval '7 day')

user_46239 · Answer

You haven't mentioned what the data type of the time_stamp column is, but assuming it's something that can be cast to date then I think the issue could be due to your use of the NOW() function which is deprecated on Redshift. Try replacing this with either GETDATE() or SYSDATE as per the documentation.