Forcing INTERVAL days display, even if the interval is less than one day - Mailing list pgsql-general

From Ron Johnson
Subject Forcing INTERVAL days display, even if the interval is less than one day
Date
Msg-id CANzqJaCt-mX2-AzF1j8mRr1xj95_y3JJk=0JHuvn24XDwW3jhg@mail.gmail.com
Whole thread Raw
Responses Re: Forcing INTERVAL days display, even if the interval is less than one day
Re: Forcing INTERVAL days display, even if the interval is less than one day
List pgsql-general
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)

pgsql-general by date:

Previous
From: "Frans Simmelvuo (Papula-Nevinpat)"
Date:
Subject: pg_basebackup "Permission denied" error when initiating streaming replication
Next
From: Adrian Klaver
Date:
Subject: Re: Restore of a reference database kills the auto analyze processing.