Subtract month from date column

4
votes

I would like to subtract one month from a date column in Amazon Redshift (a fork of PostgreSQL 8.0.2).

So for each date column in a table, it will add another column date_minus_a_month.

I tried this code

Select date,date::date -interval '1 month'
from table

and received an error:

Interval values with month or year parts are not supported.

Does anyone have a solution for this?

1
dateamazon-redshiftintervals
What is the type of the date column in your table? – Tim Biegeleisen
Missing v ==> use interval but not interal word. Also use interval '1' month but not interal '1 month' – krokodilko
What database server are you using? I guess this is not a pure Postgres but a derived database. – klin
solved by add_month redshift function – Elad Mazor
If you solved it, add that as an answer and accept that answer so that this question is marked as resolved – a_horse_with_no_name

1 Answers

4
votes

You can use datesub, although I just use dateadd for everything and use negative numbers.

eg

SELECT getdate() as right_now, dateadd(month, -1, getdate()) as last_month

Docs: https://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html