Обсуждение: Forcing INTERVAL days display, even if the interval is less than one day
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)
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)
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> 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.
to_char() could be your friend here.
regards, tom lane
Re: Forcing INTERVAL days display, even if the interval is less than one day
От
Greg Sabino Mullane
Дата:
Could just use a CASE for that particular case:
CASE WHEN now()-latest_vacuum < '1 day'
THEN '0 days '
ELSE '' END
|| DATE_TRUNC('second', now()-latest_vacuum) AS vacuumed_ago
Cheers,
Greg