Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s4MRJ-00389b-Bu for pgsql-general@arkaria.postgresql.org; Tue, 07 May 2024 15:09:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s4MRF-003VQl-4X for pgsql-general@arkaria.postgresql.org; Tue, 07 May 2024 15:09:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s4MRE-003VQd-M5 for pgsql-general@lists.postgresql.org; Tue, 07 May 2024 15:09:05 +0000 Received: from mail-oa1-x31.google.com ([2001:4860:4864:20::31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s4MRB-0021Gc-VA for pgsql-general@postgresql.org; Tue, 07 May 2024 15:09:04 +0000 Received: by mail-oa1-x31.google.com with SMTP id 586e51a60fabf-23fb052ca41so1277948fac.0 for ; Tue, 07 May 2024 08:09:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1715094538; x=1715699338; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=Dc1eFyuBdATofvfY4uA3zA4vukg394hUxenFBl0HHCg=; b=bKVo5bR5Z0xZlKtnf9q5wdCUCwnCKn5BH4L/bVUCIjDgebjVeYy7XrzslYBCKjzhOS 1qkz4mmcCCHW3/vwhAZRWn8GiYN48f7T/9P4XyIrMSnrHOnsC31kx18WMhoCN1FFzbTp RYdnU7W5rjcD4gehTVx2Esp+Uvqa179jaxzjUU13+vjMtgKeu/WGbB3lcdQ388Cfg9z2 n5+6Ojpa1C6LH3pxwO/MExzQP5UMMfrj+vbx78LEtC24XzOcypeiOyGPDH0mtPm2ieSw Dmh1PNcnms+5h9qzemzUa7FyRR/3S2vmmIGx5l5VY/1fiPClw15NGTx4whCmx/KBTwYJ 2l+Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715094538; x=1715699338; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=Dc1eFyuBdATofvfY4uA3zA4vukg394hUxenFBl0HHCg=; b=AFu8bhsV/sKY8sxyZ5in/c9+mnVCbjlF+iMvTl5ijYx91mXnpCxws5EtU1KyNq51qJ 1vnjbl+2Ity6hCGT/35uWKOrXIAMbeN6ZtE2OaVpxqQisGZcFOLGHIelHaQSwgmf9JTt vW9eWrO09sujLlC0RutnstaLZ3qDOLIoBrpsXa1ASGUFP9VOKdWHk7yg9mKelivo12uJ 6k4f9UL4skcLJS0n7eFyHkSMT5PGKA5eY3CgFb7rAzP9b2hHLm+EdZLs0EePUWYzsOPU vuyQxnX2eeNpNtLLC8E/LazXNXJMUkkIdipTHY2yjx2htSrKHinz2yeNQkTYB51iK2k4 i4WA== X-Gm-Message-State: AOJu0YzcE86CxQqX9YhTO7u/+y8Xui0fVJrjgkrT+XkiePqQmSso+dOv dkA2OjksA/veLLFOCGL/TESMnnNJfxVY/kmoUTw5UQg01mTME+Blh7myGpdl6+bk8UzmVCfZsy+ MfFo64ZWOgOeWXhajwuUkhM/ZEUMJkA2g X-Google-Smtp-Source: AGHT+IGJpS4JvR0WZrhOvWArQEqxabKi/HmK2jHYWokqvARb8mEKstNPV+InOJIOCA6eswWRXwfLJpUJ/ajWiVfZH1s= X-Received: by 2002:a05:6870:7e0d:b0:240:3a4e:504e with SMTP id wx13-20020a0568707e0d00b002403a4e504emr2297516oab.58.1715094538201; Tue, 07 May 2024 08:08:58 -0700 (PDT) MIME-Version: 1.0 From: Ron Johnson Date: Tue, 7 May 2024 11:08:47 -0400 Message-ID: Subject: Forcing INTERVAL days display, even if the interval is less than one day To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000b4b1da0617de90b7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b4b1da0617de90b7 Content-Type: text/plain; charset="UTF-8" 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) --000000000000b4b1da0617de90b7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
PG 9.6.24, if relevant.=C2=A0 (Hopefully we're migrati= ng next month.)

Displaying how long ago a date was is ea= sy, but interval casts "helpfully" suppress=C2=A0"X days ago= " if the interval is less than one day ago.

H= ow do I make it display "days ago", even when days ago is zero?= =C2=A0 Explicitly casting "day to second" didn't work.
<= div>
CDSLBXW=3D# with
tables as
(=C2=A0 =C2=A0 select schemaname||'.'||relname as table_name
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0, greatest(last_vacuum, last_autovacuum) as = latest_vacuum
=C2=A0 =C2=A0 from pg_stat_user_tables
)
select tabl= e_name, latest_vacuum,
=C2=A0 =C2=A0 =C2=A0 =C2=A0date_trunc('second= ', (current_timestamp - latest_vacuum))::interval day to second as vacu= umed_ago
from tables
order by latest_vacuum desc
limit 30;
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0table_name =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 latest_vacuum =C2=A0 =C2=A0 =C2=A0= =C2=A0 | =C2=A0vacuumed_ago =C2=A0
--------------------------------+--= -----------------------------+-----------------
=C2=A0cds.x937_file =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2024-05-07 10:= 53:38.971431-04 | 00:01:45
=C2=A0cds.lockbox_end_of_day =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | 2024-05-07 10:53:38.758813-04 | 00:01:45
=C2=A0dba.index= _bloat_2stg =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2024-05-07 10:49:09.196655= -04 | 00:06:15
=C2=A0dba.index_bloat_1stg =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | 2024-05-07 10:49:03.153449-04 | 00:06:21
=C2=A0dba.table_bloat_= 2stg =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2024-05-07 10:48:56.681218-04 | 0= 0:06:28
=C2=A0dba.table_bloat_1stg =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = 2024-05-07 10:48:50.233984-04 | 00:06:34
=C2=A0cds.x937_cash_letter =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2024-05-07 10:45:38.763453-04 | 00:09:45<= br>=C2=A0tms.batch =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| 2024-05-07 10:37:50.758763-04 | 00:17:33
=C2=A0cds= .cdslockbox =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2024-= 05-07 10:35:38.625663-04 | 00:19:46
=C2=A0tms.item_mapping =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2024-05-07 10:29:09.16413-04 =C2=A0| = 00:26:15
=C2=A0public.job =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | 2024-05-07 10:03:38.270296-04 | 00:51:46
=C2= =A0cds.mail_out_address =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2024-05-07 09:= 55:38.269805-04 | 00:59:46
=C2=A0cds.rebatching_rule =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0| 2024-05-07 09:38:38.062069-04 | 01:16:46
=C2= =A0cds.cds_job_history =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2024-05-0= 7 09:16:40.071253-04 | 01:38:44
=C2=A0tms.document =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2024-05-07 08:01:15.545398-04 |= 02:54:09
=C2=A0cds.cdsdocument =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0| 2024-05-07 08:00:13.793372-04 | 02:55:10
=C2=A0cds.al= l_day_event_trigger =C2=A0 =C2=A0 =C2=A0| 2024-05-07 07:54:38.202722-04 | 0= 3:00:46
=C2=A0public.job_history =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | 2024-05-07 01:45:25.265417-04 | 09:09:59
=C2=A0tms.chk_image =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2024-05-06 15:= 39:12.708045-04 | 19:16:12
=C2=A0tms.transaction =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2024-05-06 15:38:32.878078-04 | 19:16:5= 1
=C2=A0tms.payment =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0| 2024-05-06 14:10:17.76129-04 =C2=A0| 20:45:06
=C2=A0p= ublic.schedule =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 202= 4-05-05 00:00:49.160792-04 | 2 days 10:54:35
=C2=A0tms.gl_ticket_image = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2024-05-04 23:55:05.632414-04 | = 2 days 11:00:19
=C2=A0tms.alerted_watchlist =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| 2024-05-04 23:55:05.62597-04 =C2=A0| 2 days 11:00:19
=C2=A0cds.b= alancing_record_imagerps =C2=A0| 2024-05-04 23:55:05.625671-04 | 2 days 11:= 00:19
=C2=A0cds.balancing_record_publisher | 2024-05-04 23:55:05.618346-= 04 | 2 days 11:00:19
=C2=A0tms.credit_card =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2024-05-04 23:55:05.617497-04 | 2 days 11:00:1= 9
=C2=A0tms.chk_original_image =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 2024-05-04 = 23:55:05.607952-04 | 2 days 11:00:19
=C2=A0cds.billing_volume_header =C2= =A0 =C2=A0 =C2=A0| 2024-05-04 23:55:05.60093-04 =C2=A0| 2 days 11:00:19
= =C2=A0cds.balancing_publisher_batch =C2=A0| 2024-05-04 23:55:05.590679-04 |= 2 days 11:00:19
(30 rows)
--000000000000b4b1da0617de90b7--