Get data for previous month in postgresql

Solution 1:

Subtract one month from the current month, then "truncate" that to the beginning of that date. As you don't want to include rows from "this" month, you also need to add a condition for that

SELECT *
FROM Conference 
WHERE date_start >= date_trunc('month', current_date - interval '1' month)
  and date_start < date_trunc('month', current_date)

date_trunc('month', current_date - interval '1' month) will return the 1st day of the previous month and date_trunc('month', current_date) will return the first day of "this" month.

Solution 2:

SELECT * FROM Conference WHERE date_trunc('month', date_start)=
  date_trunc('month', current_date - interval '1 month')

be careful with timezone

date_trunc('month', (date_start at time zone 'UTC'))

also, you can add an index to make this faster

CREATE OR REPLACE FUNCTION fn_my_date_trunc(period text,
 some_time timestamp with time zone) 
  RETURNS timestamp with time zone
AS
$BODY$
    select date_trunc($1, $2);
$BODY$
LANGUAGE sql
IMMUTABLE;

CREATE INDEX "IdxConferenceDateStart" ON Conference
 (fn_my_date_trunc('month', (date_start at time zone 'UTC')));

and yes, of course you have to use declared function 'fn_my_date_trunc' in your request to use this index automatically(and cast datetime to time zone datetime)

Get data for previous month in postgresql

Solution 1:

Solution 2:

Related

Recent Posts