时间戳

首页 > 解决方案 > 需要使用 SQL 字符串变量中的函数计算秒的总和

sql-server - 需要使用 SQL 字符串变量中的函数计算秒的总和

问题描述

在一个名为Adventurous的表中有一个名为duration的列。该列的值如下。在列中，“H”的后缀是小时，“M”的后缀是分钟，“S”的后缀是秒。我们怎么能选择小时、分钟和秒并将所有转换为秒，即所有小时分钟和秒的总和，以秒的形式。

Duration 
--------
PT10M13S
PT13M22S
PT1H2M18S
PT11S

我尝试如下使用 substring 和 charindex 并尝试创建一个函数，但出现错误：

Declare @Duration varchar(30) ='PT16H13M42S', @Dur varchar(10)
Declare @hours int
declare @mins int
declare @secs int
declare @len int

select @len = len(substring (@Duration, 3, len(@Duration))), @Dur=substring (@Duration, 3, len(@Duration))
select @hours = charindex('H', @Dur)

select substring(@Dur, 1, @hours-1)

select @Duration=substring (@Dur, @hours+1, len(@Dur))
select @mins = charindex('M', @Duration)

select substring(@Duration, 1, @mins-1)

select @Dur=substring (@Duration, @mins+1, len(@Duration))
select @secs= charindex('S', @Dur)

select substring(@Dur, 1, @Secs-1)

select @len, @Dur, @Duration

例如 PT1H2M18S= 1*3600+2*60+18=3738

标签： sql-serverpostgresqlsql-server-2008

解决方案

另一种可能的方法是将Duration文本输入转换为有效的T-SQL表达式（'PT1H2M18S'将被转换为'1*3600+2*60+18*1+0'）。之后，考虑接下来的两个选项：

  • 生成并执行动态语句，它将评估每个表达式或

  • 定义一个函数来进行计算

输入：

CREATE TABLE #Data (
    Duration varchar(50)
)
INSERT INTO #Data
    (Duration)
VALUES
    ('PT10M13S'),
    ('PT13M22S'),
    ('PT1H2M18S'),
    ('PT100H'),
    ('PT11S')

动态声明：

DECLARE @stm nvarchar(max)
SET @stm = N''

SELECT @stm = @stm + 
    CONCAT(
        'UNION ALL SELECT ''',
        Duration,
        ''' AS [Duration], ', 
        REPLACE(REPLACE(REPLACE(REPLACE(Duration, 'H', '*3600+'), 'M', '*60+'), 'S', '*1+'), 'PT', ''), 
        '0 AS [Seconds] '
    )
FROM #Data
SET @stm = STUFF(@stm, 1, 10, N'')

EXEC (@stm)

用户自定义函数：

CREATE FUNCTION [udfCalculateHMS] (@expression varchar(100))
RETURNS int
AS
BEGIN
   DECLARE @result int
   DECLARE @s varchar(100)

   --
   SET @result = 0
   WHILE (CHARINDEX('+', @expression) > 0) BEGIN
      SET @s = SUBSTRING(@expression, 1, CHARINDEX('+', @expression) - 1)
      SET @expression = STUFF(@expression, 1, CHARINDEX('+', @expression), '')
      SET @result = @result + 
          CONVERT(int, SUBSTRING(@s, 1, CHARINDEX('*', @s) - 1)) * 
          CONVERT(int, STUFF(@s, 1, CHARINDEX('*', @s), ''))
   END

   -- Return value
   RETURN @result
END

SELECT 
    Duration, 
    dbo.udfCalculateHMS(CONCAT(REPLACE(REPLACE(REPLACE(REPLACE(Duration, 'H', '*3600+'), 'M', '*60+'), 'S', '*1+'), 'PT', ''), '0')) AS Seconds
FROM #Data

输出：

Duration    Seconds
PT10M13S    613
PT13M22S    802
PT1H2M18S   3738
PT100H      360000
PT11S       11

推荐阅读