Get first date of previous month

user_11644069 0
Viewed 63

Is there any way to get the first day of previous month other than 

date_trunc('month', current_date - interval '1 month') ?

I'm trying to save a query in a Report Designer software (DBxtra) but the software freezes while using the "interval" feature of PostgreSQL.

date postgresql
2 Answers

You can try to calculate the previous month manually.

One approach is extract the month and subtract 1 if it is not 12(in this case you return 1):

SELECT to_timestamp(concat(EXTRACT(YEAR from current_date), '-',CASE (EXTRACT(MONTH from current_date)) WHEN 12 THEN 1 ELSE (EXTRACT(MONTH from current_date)-1) END,'-', 1), 'YYYY-MM-DD');

If you need it without timezone:

SELECT to_timestamp(concat(EXTRACT(YEAR from current_date), '-',CASE (EXTRACT(MONTH from current_date)) WHEN 12 THEN 1 ELSE (EXTRACT(MONTH from current_date)-1) END,'-', 1), 'YYYY-MM-DD')::timestamp without time zone;
user_43258780

Try:

make_interval(month := 1)
'1 month'::interval
cast('1 month' as interval)

instead of interval '1 month'

user_5931440
Related