Get data for previous month in postgresql
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)
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.