PG 9.6.24, if relevant. (Hopefully we're migrating next month.) Displaying how long ago a date was is easy, but interval casts "helpfully" suppress "X days ago" if the interval is less than one day ago.
How do I make it display "days ago", even when days ago is zero? Explicitly casting "day to second" didn't work. CDSLBXW=# with tables as ( select schemaname||'.'||relname as table_name , greatest(last_vacuum, last_autovacuum) as latest_vacuum from pg_stat_user_tables ) select table_name, latest_vacuum, date_trunc('second', (current_timestamp - latest_vacuum))::interval day to second as vacuumed_ago from tables order by latest_vacuum desc limit 30; table_name | latest_vacuum | vacuumed_ago --------------------------------+-------------------------------+----------------- cds.x937_file | 2024-05-07 10:53:38.971431-04 | 00:01:45 cds.lockbox_end_of_day | 2024-05-07 10:53:38.758813-04 | 00:01:45 dba.index_bloat_2stg | 2024-05-07 10:49:09.196655-04 | 00:06:15 dba.index_bloat_1stg | 2024-05-07 10:49:03.153449-04 | 00:06:21 dba.table_bloat_2stg | 2024-05-07 10:48:56.681218-04 | 00:06:28 dba.table_bloat_1stg | 2024-05-07 10:48:50.233984-04 | 00:06:34 cds.x937_cash_letter | 2024-05-07 10:45:38.763453-04 | 00:09:45 tms.batch | 2024-05-07 10:37:50.758763-04 | 00:17:33 cds.cdslockbox | 2024-05-07 10:35:38.625663-04 | 00:19:46 tms.item_mapping | 2024-05-07 10:29:09.16413-04 | 00:26:15 public.job | 2024-05-07 10:03:38.270296-04 | 00:51:46 cds.mail_out_address | 2024-05-07 09:55:38.269805-04 | 00:59:46 cds.rebatching_rule | 2024-05-07 09:38:38.062069-04 | 01:16:46 cds.cds_job_history | 2024-05-07 09:16:40.071253-04 | 01:38:44 tms.document | 2024-05-07 08:01:15.545398-04 | 02:54:09 cds.cdsdocument | 2024-05-07 08:00:13.793372-04 | 02:55:10 cds.all_day_event_trigger | 2024-05-07 07:54:38.202722-04 | 03:00:46 public.job_history | 2024-05-07 01:45:25.265417-04 | 09:09:59 tms.chk_image | 2024-05-06 15:39:12.708045-04 | 19:16:12 tms.transaction | 2024-05-06 15:38:32.878078-04 | 19:16:51 tms.payment | 2024-05-06 14:10:17.76129-04 | 20:45:06 public.schedule | 2024-05-05 00:00:49.160792-04 | 2 days 10:54:35 tms.gl_ticket_image | 2024-05-04 23:55:05.632414-04 | 2 days 11:00:19 tms.alerted_watchlist | 2024-05-04 23:55:05.62597-04 | 2 days 11:00:19 cds.balancing_record_imagerps | 2024-05-04 23:55:05.625671-04 | 2 days 11:00:19 cds.balancing_record_publisher | 2024-05-04 23:55:05.618346-04 | 2 days 11:00:19 tms.credit_card | 2024-05-04 23:55:05.617497-04 | 2 days 11:00:19 tms.chk_original_image | 2024-05-04 23:55:05.607952-04 | 2 days 11:00:19 cds.billing_volume_header | 2024-05-04 23:55:05.60093-04 | 2 days 11:00:19 cds.balancing_publisher_batch | 2024-05-04 23:55:05.590679-04 | 2 days 11:00:19 (30 rows)