时间戳

首页 > 解决方案 > 我想在 Informix 数据库中将 Integer 转换为 hh:mm:ss 格式

sql - 我想在 Informix 数据库中将 Integer 转换为 hh:mm:ss 格式

问题描述

SELECT MAX(longestwaitduration) AS MaxWaitD from myTable;

这里，Columnlongestwaitduration是Integer存储毫秒数，数据库是 Informix 版本 11+。

标签： sqlinformix

解决方案

这是一个适用于millitimes具有 INTEGER 列的表的 SELECT 语句milliseconds：

SELECT milliseconds,
       (milliseconds / 1000) AS seconds_f3,
       TRUNC(milliseconds/1000) AS tr_seconds,
       ROUND(milliseconds/1000) AS ro_seconds,
       TRUNC(milliseconds/1000) UNITS SECOND AS tr_iv_s9,
       ROUND(milliseconds/1000) UNITS SECOND AS ro_iv_s9,
       CAST(TRUNC(milliseconds/1000) UNITS SECOND AS INTERVAL HOUR(9) TO SECOND) AS tr_iv_hs,
       CAST(ROUND(milliseconds/1000) UNITS SECOND AS INTERVAL HOUR(9) TO SECOND) AS rd_iv_hs,
       (milliseconds/1000) UNITS FRACTION(3) AS iv_s9f3,
       CAST((milliseconds/1000) UNITS FRACTION(3) AS INTERVAL HOUR(9) TO FRACTION(3)) AS iv_h2f3
  FROM millitimes
 ORDER BY milliseconds;

这显示了我是如何朝着解决方案前进的。

关键技巧是：

  1. 使用 UNITS SECOND 或 UNITS FRACTION(3) 将数字转换为区间，然后
  2. 将结果转换为您想要的 INTERVAL 类型。如果需要，您可以使用 INTERVAL DAY TO SECOND 或 INTERVAL DAY(9) TO FRACTION(3) 或类似的。

示例数据和架构。

时间是随机值——按两个顺序排列。前六个是使用 0 到 86,400,000（一天中的毫秒数）之间的均匀分布生成的。其余的值是使用“统一对数”算法生成的，因此时间的对数在 1000 毫秒到 864,000,000 毫秒（10 天）的范围内统一生成，然后转换回一个值。它强调较小的值，同时也涵盖较大的值。

CREATE TEMP TABLE millitimes (milliseconds INTEGER NOT NULL PRIMARY KEY);

INSERT INTO millitimes VALUES(65284606);
INSERT INTO millitimes VALUES(40498249);
INSERT INTO millitimes VALUES(83967419);
INSERT INTO millitimes VALUES(35819561);
INSERT INTO millitimes VALUES(72919126);
INSERT INTO millitimes VALUES(43567311);

INSERT INTO millitimes VALUES(75242);
INSERT INTO millitimes VALUES(13043053);
INSERT INTO millitimes VALUES(48569485);
INSERT INTO millitimes VALUES(14161761);
INSERT INTO millitimes VALUES(355773248);
INSERT INTO millitimes VALUES(2599);
INSERT INTO millitimes VALUES(3038);
INSERT INTO millitimes VALUES(359329);
INSERT INTO millitimes VALUES(484870033);
INSERT INTO millitimes VALUES(254664);
INSERT INTO millitimes VALUES(248989810);
INSERT INTO millitimes VALUES(87865);
INSERT INTO millitimes VALUES(2225346);
INSERT INTO millitimes VALUES(2779735);
INSERT INTO millitimes VALUES(1889026);
INSERT INTO millitimes VALUES(48921224);
INSERT INTO millitimes VALUES(43920528);
INSERT INTO millitimes VALUES(321290);
INSERT INTO millitimes VALUES(108176);
INSERT INTO millitimes VALUES(32854);

样本输出：

毫秒 秒_f3 tr_seconds ro_seconds tr_iv_s9 ro_iv_s9 tr_iv_hs rd_iv_hs iv_s9f3 iv_h2f3
整数 十进制(20) 十进制（32,0） 十进制（32,0） 间隔秒 (9) 到秒 间隔秒 (9) 到秒 间隔小时 (9) 到秒 间隔小时 (9) 到秒 间隔秒 (9) 到分数 (3) 间隔小时 (9) 到分数 (3)
2599 2.599 2 3 2 3 0:00:02 0:00:03 2.599 0:00:02.599
3038 3.038 3 3 3 3 0:00:03 0:00:03 3.038 0:00:03.038
32854 32.854 32 33 32 33 0:00:32 0:00:33 32.854 0:00:32.854
75242 75.242 75 75 75 75 0:01:15 0:01:15 75.242 0:01:15.242
87865 87.865 87 88 87 88 0:01:27 0:01:28 87.865 0:01:27.865
108176 108.176 108 108 108 108 0:01:48 0:01:48 108.176 0:01:48.176
254664 254.664 254 255 254 255 0:04:14 0:04:15 254.664 0:04:14.664
321290 321.29 321 321 321 321 0:05:21 0:05:21 321.290 0:05:21.290
359329 359.329 359 359 359 359 0:05:59 0:05:59 359.329 0:05:59.329
1889026 1889.026 1889 1889 1889 1889 0:31:29 0:31:29 1889.026 0:31:29.026
2225346 2225.346 2225 2225 2225 2225 0:37:05 0:37:05 2225.346 0:37:05.346
2779735 2779.735 2779 2780 2779 2780 0:46:19 0:46:20 2779.735 0:46:19.735
13043053 13043.053 13043 13043 13043 13043 3:37:23 3:37:23 13043.053 3:37:23.053
14161761 14161.761 14161 14162 14161 14162 3:56:01 3:56:02 14161.761 3:56:01.761
35819561 35819.561 35819 35820 35819 35820 9:56:59 9:57:00 35819.561 9:56:59.561
40498249 40498.249 40498 40498 40498 40498 11:14:58 11:14:58 40498.249 11:14:58.249
43567311 43567.311 43567 43567 43567 43567 12:06:07 12:06:07 43567.311 12:06:07.311
43920528 43920.528 43920 43921 43920 43921 12:12:00 12:12:01 43920.528 12:12:00.528
48569485 48569.485 48569 48569 48569 48569 13:29:29 13:29:29 48569.485 13:29:29.485
48921224 48921.224 48921 48921 48921 48921 13:35:21 13:35:21 48921.224 13:35:21.224
65284606 65284.606 65284 65285 65284 65285 18:08:04 18:08:05 65284.606 18:08:04.606
72919126 72919.126 72919 72919 72919 72919 20:15:19 20:15:19 72919.126 20:15:19.126
83967419 83967.419 83967 83967 83967 83967 23:19:27 23:19:27 83967.419 23:19:27.419
248989810 248989.81 248989 248990 248989 248990 69:09:49 69:09:50 248989.810 69:09:49.810
355773248 355773.248 355773 355773 355773 355773 98:49:33 98:49:33 355773.248 98:49:33.248
484870033 484870.033 484870 484870 484870 484870 134:41:10 134:41:10 484870.033 134:41:10.033

推荐阅读