Menu
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
651
The PostgreSQL function LOCALTIMESTAMP returns the current date and time (of the machine running that instance of PostgreSQL) as a timestamp value. It uses the 'YYYY-MM-DD hh:mm:ss. nnnnnnn' format, where: YYYY is a 4-digit year.
How to get last 3 months records from the table. SELECT * from table where month > CURRENT_DATE-120 and month < CURRENT_DATE order by month; I have used the above query is it correct?
You can select the last day of month. SELECT (date_trunc('MONTH', ('201608'||'01')::date) + INTERVAL '1 MONTH - 1 day')::DATE; 201608 is replaceable with a variable.
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.
98
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
