SQL将秒转换为日期：小时：最小：秒 [英] SQL Convert Seconds into Day:Hour:Min:Sec

问题描述

主题说了这一切。我试图做一些魔术，通过一个函数，将第二个整数变成字符串DD：HH：MM：SS。

Snip

输入：278543

输出：3D 5H 22M 23S

如果可能的话，我想做的更优雅，就是填写数字（所以5M显示为05M），并对齐它们，以便3D 5H 22M 23S代替3D 5H 22M 23S。 p>

编辑：似乎最有效的剪辑。会喜欢让它更漂亮，但这绝对有效我可以告诉：

  CREATE FUNCTION DHMS（secondsElapsed INT）
返回Char（20）
语言SQL 
无外部动作
确定
 
 BEGIN 
 DECLARE Dy Integer; 
 DECLARE Hr整数; 
 DECLARE Mn Integer; 
 DECLARE Sc Integer; 
 
 SET Dy = Cast（secondsElapsed / 86400 as Int）; 
 SET Hr = Cast（MOD（secondsElapsed，86400）/ 3600 as Int）; 
 SET Mn = Cast（MOD（secondsElapsed，3600）/ 60 as Int）; 
 SET Sc = Cast（MOD（secondsElapsed，60）as Int）; 
 
 RETURN REPEAT（''，6-LENGTH（RTRIM（CAST（Dy AS CHAR（6）））））|| Dy || 'D'
 || REPEAT（'0'，2-LENGTH（RTRIM（CAST（Hr AS CHAR（6）））））|| Hr || 'H'
 || REPEAT（'0'，2-LENGTH（RTRIM（CAST（Mn AS CHAR（6）））））|| Mn || 'M'
 || REPEAT（'0'，2-LENGTH（RTRIM（CAST（Sc AS CHAR（6）））））|| Sc || 'S' 
 END 
  

解决方案

你在正确的轨道使用LPAD（），因为它可以填零或任何其他字符串。 CHAR（15）不足以按照您想要的方式格式化输出，并且仍然允许五个位置的天数，这是您在代码中指定的长度。

 创建或替换功能DHMS（秒过去的INT）
返回Char（18）
语言SQL 
无外部动作
确定
 
 RETURN LPAD（secondsElapsed / 86400，5）|| 'D'
 || LPAD（MOD（secondsElapsed，86400）/ 3600，2，'0'）|| 'H'
 || LPAD（MOD（secondsElapsed，3600）/ 60，2，'0'）|| 'M'
 || LPAD（MOD（secondsElapsed，60），2，'0'）|| 'S'
; 
  

Topic says it all. I'm trying to do some magic, via a function, that turns a second integer into a string "DD:HH:MM:SS".

Snip

input: 278543
output: "3D 5H 22M 23S "

What I'd like to do, more gracefully if possible, is pad the numbers (So that 5M shows as 05M) and right align them so that "3D 5H 22M 23S " is " 3D 5H 22M 23S" instead.

edit: Latest cut that seems to work. Would love to have it prettier, but this definitely works as far as I can tell:

CREATE FUNCTION DHMS(secondsElapsed INT)
RETURNS Char(20)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC

BEGIN
    DECLARE Dy Integer;
    DECLARE Hr Integer;
    DECLARE Mn Integer;
    DECLARE Sc Integer;

    SET Dy = Cast(     secondsElapsed / 86400         as Int);
    SET Hr = Cast(MOD( secondsElapsed, 86400 ) / 3600 as Int);
    SET Mn = Cast(MOD( secondsElapsed, 3600 ) / 60    as Int);
    SET Sc = Cast(MOD( secondsElapsed, 60 )           as Int); 

     RETURN REPEAT(' ',6-LENGTH(RTRIM(CAST(Dy AS CHAR(6))))) || Dy || 'D ' 
         || REPEAT('0',2-LENGTH(RTRIM(CAST(Hr AS CHAR(6))))) || Hr || 'H ' 
         || REPEAT('0',2-LENGTH(RTRIM(CAST(Mn AS CHAR(6))))) || Mn || 'M '
         || REPEAT('0',2-LENGTH(RTRIM(CAST(Sc AS CHAR(6))))) || Sc || 'S';
END

解决方案

You were on the right track using LPAD(), since it can pad with zero or any other string. CHAR(15) is not enough to format the output the way you want and still allow five positions for the number of days, which is the length you specified in your code.

CREATE OR REPLACE FUNCTION DHMS(secondsElapsed INT)
RETURNS Char(18)
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC

RETURN LPAD( secondsElapsed / 86400 , 5 ) || 'D ' 
  || LPAD( MOD( secondsElapsed, 86400 ) / 3600, 2, '0') || 'H ' 
  || LPAD( MOD( secondsElapsed, 3600 ) / 60, 2, '0' ) || 'M '
  || LPAD( MOD( secondsElapsed, 60 ), 2, '0' ) || 'S'
;

这篇关于SQL将秒转换为日期：小时：最小：秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！