public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Forcing INTERVAL days display, even if the interval is less than one day
Date: Tue, 7 May 2024 11:08:47 -0400
Message-ID: <CANzqJaCt-mX2-AzF1j8mRr1xj95_y3JJk=0JHuvn24XDwW3jhg@mail.gmail.com> (raw)
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)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Forcing INTERVAL days display, even if the interval is less than one day
In-Reply-To: <CANzqJaCt-mX2-AzF1j8mRr1xj95_y3JJk=0JHuvn24XDwW3jhg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox