sql - Get timestamp of one month ago in PostgreSQL

Question

I have a PostgreSQL database in which one table rapidly grows very large (several million rows every month or so) so I'd like to periodically archive the contents of that table into a separate table.

I'm intending to use a cron job to execute a .sql file nightly to archive all rows that are older than one month into the other table.

I have the query working fine, but I need to know how to dynamically create a timestamp of one month prior.

The time column is stored in the format 2013-10-27 06:53:12 and I need to know what to use in an SQL query to build a timestamp of exactly one month prior. For example, if today is October 27, 2013, I want the query to match all rows where time < 2013-09-27 00:00:00

question from:https://stackoverflow.com/questions/19615187/get-timestamp-of-one-month-ago-in-postgresql

Answer 1

Question was answered by a friend in IRC:

'now'::timestamp - '1 month'::interval

Having the timestamp return 00:00:00 wasn't terrible important, so this works for my intentions.