PostgreSQL:该列包含秒至小时，分钟，秒，天，INTERVAL SECONDSDIFF [英] PostgreSQL: Column containing seconds to Hours Minutes Seconds Days INTERVAL SECONDSDIFF

问题描述

我想将包含秒(即11549404)的列转换为天，小时，分钟，秒

I want to convert a column which contains seconds (i.e 11549404) into days, hours, minutes, seconds

SELECT (myCol || ' second')::interval, 'HH24 hrs MI "minutes" SS "seconds"') AS duration
FROM public.myTable

返回以下内容；

"3208小时10分04秒"

"3208 hrs 10 minutes 04 seconds"

将其显示为天，小时，分钟，秒的方式是什么

Whats the way to display it as days, hours, minutes seconds

推荐答案

因为有些日子有23个小时，而其他日子有25个小时，结果并非易事(不可能，因为不知道绝对值). interval 类型是月，日和秒的结构.这些值不会在这些字段之间自动移动，因为安装的天数不同，天数的秒数也可以不同.但是您可以进行一些标准化-有一个函数 justify_interval 预计每天有24小时:

Because some days has 23hours and others 25hours the result is not easy task (it is not possible, because don't know absolute value). interval type is a structure of months, days and seconds. The values are not automatically moved between these fields because mounts has different number of days, days can has different number of seconds. But you can do some normalization - there is a function justify_interval that expects so days has 24 hours every time:

postgres=# select justify_interval('3208 hrs 10 minutes 04 seconds'::interval);
+-------------------------+
|    justify_interval     |
+-------------------------+
| 4 mons 13 days 16:10:04 |
+-------------------------+
(1 row)

这篇关于PostgreSQL:该列包含秒至小时，分钟，秒，天，INTERVAL SECONDSDIFF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！