主页 / dba / 问题 / 343516
Accepted
muttBunch
Asked: 2024-11-11 15:56:01 +0800 CST

如何在 Postgres 中 TRIM 去掉 Time 中的前导零

  • 772

我正在考虑以下问题：

SELECT
    to_char(date_trunc('hour', "TimeStamp"), 'HH12 AM') || ' - ' || to_char(date_trunc('hour', "TimeStamp")  + interval '1 hour', 'HH12 AM') as time,
    COUNT(*) as totals
FROM 
    eventtotals
    
WHERE "TimeStamp" >= NOW() - Interval '24 HOURS'

GROUP BY date_trunc('hour', "TimeStamp")

它显示以下输出：

TIME                TOTAL
04 PM - 05 PM       300
05 PM - 06 PM       452
06 PM - 07 PM       393
07 PM - 08 PM       356
08 PM - 09 PM       356
09 PM - 10 PM       361
10 PM - 11 PM       359
11 PM - 12 AM       367
12 AM - 01 AM       357
01 AM - 02 AM       360
02 AM - 03 AM       286

我知道

TRIM(LEADING '0'...

但是，我想从小时中删除前导零，我只是不确定在上面列出的查询中实际将修剪行放在哪里，除非有其他方法。

谢谢

postgresql

1 个回答

  1. Best Answer

    您可以使用 LTRIM() 删除前导“0”

    SELECT
    ltrim(to_char(date_trunc('hour', "TimeStamp"), 'HH12 AM'),'0') || ' - ' || ltrim(to_char(date_trunc('hour', "TimeStamp")  + interval '1 hour', 'HH12 AM'),'0') as time,
    COUNT(*) as totals
FROM 
    eventtotals
    
WHERE "TimeStamp" >= NOW() - Interval '24 HOURS'

GROUP BY date_trunc('hour', "TimeStamp")
    • 0

相关问题