CODEWITHSUNDEEP
postgresql-11
Amira Shaker
Amira Shaker

Reputation: 11

subtract 1 month interval from date?

SELECT CONCAT(EXTRACT(MONTH
                        FROM '{{ date.start }}'::timestamp ),'/',
                       EXTRACT(YEAR
                        FROM '{{ date.start }}'::timestamp )) - interval '1' MONTH

I get an error when I run this query

Error running query: operator does not exist: text - interval LINE 25: ... FROM '2019-01-01'::timestamp )) - interval... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

How to solve it?

Upvotes: 0

Views: 8568

Answers (2)

ambianBeing
ambianBeing

Reputation: 3529

From the comments and the query in OP I think you are trying to convert a timestamp to customized format of MM/YYYY minus the 1 month interval.

Following is just one of few approaches to achieve that: Using concatenation operator ||

SELECT (extract(month FROM (input_date - interval '1 month')))::text 
                  || '/' 
                  || (extract(year FROM (input_date - interval '1 month')))::text 
    AS formatted_string;

Upvotes: 0

user330315
user330315

Reputation:

You need to remove the concat() as it turns the timstamp into a varchar.

If you want to get the start of the month of the "timestamp" value, there are easier way to do that:

date_trunc('month', '{{ date.start }}'::timestamp)

The result of that is a timestamp from which you can subtract the interval:

date_trunc('month', '{{ date.start }}'::timestamp) - interval '1 month'

The following sample query:

with sample_data (input_date) as (
  values 
    (timestamp '2019-01-01 17:18:19'), 
    (timestamp '2019-02-07 16:30:40'), 
    (timestamp '2019-03-02 23:30:42')
)    
select input_date, 
       (date_trunc('month', input_date) - interval '1 month')::date as previous_month_start
from sample_data;

returns the following result:

input_date          | previous_month_start
--------------------+---------------------
2019-01-01 17:18:19 | 2018-12-01          
2019-02-07 16:30:40 | 2019-01-01          
2019-03-02 23:30:42 | 2019-02-01

If you want to display the result of that in a different format, apply to_char() on the result:

to_char(date_trunc('month', input_date) - interval '1 month', 'mm/yyyy')

Online example

Upvotes: 2

Related Questions