将分钟列转换为天、小时和分钟SQL

Question

我有一个这样的列-

XXX_2019
234
2142
1423
4634
7886
3143
3243

我想输出

XXX_2019
3 hours, 54 minutes
1 days, 11 hours, 42 minutes
23 hours, 43 minutes

这不是日期时间。这只是一个分钟专栏。我正在使用Vertica。

Answer 1

您可以直接执行此操作。类似这样的东西：

select trim(leading ', ' from
(case when XXX_2019 > 24*60 then ', ' || floor(xxx_2019 / (24*60)) || ' days' else '' end) ||
(case when XXX_2019 > 60 then ', ' || floor((xxx_2019 % (24*60)) / 60) || ' hours' else '' end) ||
(', ' || xxx_2019 % 60 || ' minutes')
)
from (values (12345), (123), (12)) v(xxx_2019);

这里有一个db<gt；fiddle(使用Postgres(。

Answer 2

让Vertica为您完成繁重的工作。

从分钟计数中提取一个间隔，然后从间隔中提取天、小时和分钟，并将它们转换为逗号、计数器和单位，连接所有，并删除初始逗号：

WITH 
-- your input
input( xxx_2019 ) AS (
SELECT  234
UNION ALL SELECT 2142
UNION ALL SELECT 1423
UNION ALL SELECT 4634
UNION ALL SELECT 7886
UNION ALL SELECT 3143
UNION ALL SELECT 3243
)

--从分钟计数中抽出一个间隔

,
with_interval AS (
SELECT 
xxx_2019 
, (xxx_2019::char(8)||' minutes')::INTERVAL as dircast
FROM input
)

--最后，从间隔中提取比特，并将其格式化为

SELECT 
dircast
, REGEXP_REPLACE(
CASE EXTRACT(DAY    FROM dircast) 
WHEN 0 THEN ''
ELSE ', '||EXTRACT(DAY  FROM dircast)::VARCHAR(5)||' days'
END
|| CASE EXTRACT(HOUR   FROM dircast) 
WHEN 0 THEN ''
ELSE ', '||EXTRACT(HOUR FROM dircast)::VARCHAR(5)||' hours'
END
|| CASE EXTRACT(MIN   FROM dircast) 
WHEN 0 THEN ''
ELSE ', '||EXTRACT(MIN FROM dircast)::VARCHAR(5)||' minutes'
END
, '^, ','') AS s
FROM with_interval
-- out  dircast |              s               
-- out ---------+------------------------------
-- out  03:54   | 3 hours, 54 minutes
-- out  1 11:42 | 1 days, 11 hours, 42 minutes
-- out  23:43   | 23 hours, 43 minutes
-- out  3 05:14 | 3 days, 5 hours, 14 minutes
-- out  5 11:26 | 5 days, 11 hours, 26 minutes
-- out  2 04:23 | 2 days, 4 hours, 23 minutes
-- out  2 06:03 | 2 days, 6 hours, 3 minutes

当然，你可以直接，例如：

EXTRACT(DAY    FROM (xxx_2019::char(8)||' minutes')::INTERVAL) 

但我发现从另一个角度看，它更可读。。。

当然，你可以使用@Gordon Linoff的方法，但我会在Vertica中使用整数除法运算符，双斜杠//，而不是FLOOR()——之后进行除法——只是为了保持在整数算术中，这比浮点算术快得多。让我迂腐的头脑不安的是，INTEGER对字符的隐式转换，然后用字符串连接它。。。

SELECT
TRIM(LEADING ', ' FROM
CASE
WHEN XXX_2019 > 24*60
THEN ', ' || ( XXX_2019 // (24*60) )::VARCHAR(5)|| ' days'
ELSE ''
END
||CASE
WHEN XXX_2019 > 60
THEN ', ' || ( (XXX_2019 % (24*60)) // 60 )::VARCHAR(5)|| ' hours'
ELSE ''
END
||', ' || (xxx_2019 % 60) ::VARCHAR(5)|| ' minutes'
)                                                                         
FROM input;