Get timestamp of one month ago in PostgreSQL

Scheduled archiving with cron

For automated, regular data archiving, consider setting up a cron job to transfer rows older than one month. This helps manage rapidly accumulating table data. Below is a script that could do this:

# Run job at midnight on the 1st day of every month
# Archive all old rows from last month
# Who said data janitors aren't heroic? 
0 0 1 * * /usr/bin/psql -d your_database -c "INSERT INTO archive_table SELECT * FROM main_table WHERE time < date_trunc('month', CURRENT_TIMESTAMP) - INTERVAL '1 month'"


Handling just the date

There's no need to fuss about hours, minutes, and seconds if your queries mainly care about the date. Truncate the timestamp to the day:

-- This truncates our time-travel precision to days only.
-- Who needs hours and minutes when you're a SQL Time Lord?
SELECT date_trunc('day', CURRENT_TIMESTAMP - INTERVAL '1 month');


Strategies for advanced archiving

For more intricate archiving strategies, execute a dynamic query that calculates the timestamp for one month ago and selects rows accordingly:

DO $$
DECLARE
  timestamp_a_month_ago TIMESTAMP;
BEGIN
  timestamp_a_month_ago := date_trunc('month', CURRENT_TIMESTAMP) - INTERVAL '1 month';
  -- Just another day for our SQL Time Compass
  PERFORM archive_data_older_than(timestamp_a_month_ago);
END $$;


The archive_data_older_than function would implement your logic for moving rows to archival storage.

Tackling massive data volumes

Archiving millions of rows can strain performance. Keep these tips in mind:

• Build an index on the timestamp column to boost row selection speed.
• Consider partitioning your table by time. This turns archiving into merely detaching partitions.
• Assess archiving operation performance and schedule it for off-peak hours.

Archiving considerations

For systematic archiving, remember to:

• Verify timestamp format consistency to prevent data selection hiccups.
• Make your script or function exception-resilient to handle missing data or interruptions.
• Keep ample documentation of archiving operations for easier maintenance and auditing.

Safe transactions

Make archiving operations transaction-safe to prevent incomplete data movement:

BEGIN;
  -- Starting data migration. Brace for takeoff!
  INSERT INTO archive_table
  SELECT * FROM main_table
  WHERE time < CURRENT_TIMESTAMP - INTERVAL '1 month';
  -- All passengers aboard. Time to blast off!
  DELETE FROM main_table
  WHERE time < CURRENT_TIMESTAMP - INTERVAL '1 month';
COMMIT;


Wrapping operations in a transaction ensures data consistency with an all-or-nothing approach.

Archiving for speedier access

If your app frequently queries recent data, consider archiving as a part of your database design strategy to make these operations faster. You can achieve this by strategically placing indexes, partitioning, and adept SQL planning.