CODEWITHSUNDEEP
sqlpostgresql
user11492726
user11492726

Reputation:

postgresql : how to get previous month?

In my db, I have date column where the format is like 2019-04-01 or 2019-05-01 etc. If it's 2019-04-01, I'd like to get 2019-03-01 , and when it's 2019-01-01, I need to have 2018-12-01 as result. I already did some researches however didn't find an exact solution for me. Can anyone help me?

Upvotes: 1

Views: 3678

Answers (1)

S-Man
S-Man

Reputation: 23726

demo:db<>fiddle

You can use the interval addition/subtraction:

SELECT 
    my_date - interval '1 month'

Note, that this subtraction gives out a type timestamp. This can be casted into a date of course:

SELECT 
    (my_date - interval '1 month')::date AS prev_month

The solution above always subtracts one month. So, if you had 2019-04-04 you would get 2019-03-04. If you want to get the the first of previous month regardless of the current day, you can strip the day to the first of the current month before using date_trunc():

demo:db<>fiddle

SELECT
    date_trunc('month', my_date)

results in 2019-04-01 for given date 2019-04-05, e.g. And with that result, you are able to subtract one month to get the first of the previous month:

SELECT 
    my_date,
    (date_trunc('month', my_date) - interval '1 month')::date as prev_month
FROM
    my_table

Upvotes: 5

Related Questions