Dante Dante - 1 month ago 7
SQL Question

Retrieve previous months data

I am in the process of automating some reports and I have hit a bit of a wall attempting to automate a particular monthly report. The date/time is stored in epoch values.

Below Query:

SELECT
Column1,Column2,Column3,Column4
FROM
Table1,Table2
WHERE
summarydate >= extract(epoch from to_timestamp('01 oct 2016', 'DD Mon YYYY'))::bigint and
summaydate < extract(epoch from to_timestamp('01 nov 2016', 'DD Mon YYYY'))::bigint and
GROUP BY 1,2
ORDER BY 1,2


Currently the query is run manually after adjustments to date ranges.

With the extract from epoch in mind, is it possible to have the date changed to previous month automatically?

The goal is to set an automated report with this query to run on the first of every month for the previous months data.

I couldn't find a solution for Postgres in other questions related to 'Obtaining last month data'

Answer

Try using DATE_TRUNC('month',DATE) which will output the first of the month:

SELECT
    Column1,Column2,Column3,Column4
FROM
    Table1,Table2
WHERE
    summarydate >= extract(epoch from date_trunc('month', current_date) - interval '1 month')::bigint and
    summaydate < extract(epoch from date_trunc('month', current_date))::bigint and
GROUP BY 1,2
ORDER BY 1,2
Comments