代码之家 › 专栏 › 技术社区 › Chris Loelke

将ISO 8601持续时间转换为十进制时间SQL值,如PT7H30M或PT8H0M

iso8601 duration sql-server-2014 tsql sql-server

Chris Loelke · 技术社区 · 6 年前

我需要在 varchar 字段设置为表示该持续时间(小时)的十进制值。

我该怎么做 SELECT 使用以下数据返回结果,行值为8.0( PT8H0M ),7.5( PT7H30M )和1.0( PT1H0M )对于持续时间字段?

CREATE TABLE [dbo].[timetracking](
    [qbsql_id] [int] IDENTITY(1,1) NOT NULL,
    [username_id] [int] NULL,
    [TxnDate] [datetime2](0) NULL,
    [Duration] [varchar](50) NULL,
PRIMARY KEY CLUSTERED ([qbsql_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[timetracking] ON 
GO

INSERT [dbo].[timetracking] ([qbsql_id], [username_id], [TxnDate], [Duration]) VALUES (1, 1, CAST(N'2018-02-02T00:00:00.0000000' AS DateTime2), N'PT8H0M')

INSERT [dbo].[timetracking] ([qbsql_id], [username_id], [TxnDate], [Duration]) VALUES (2, 2, CAST(N'2018-02-01T00:00:00.0000000' AS DateTime2), N'PT7H30M')

INSERT [dbo].[timetracking] ([qbsql_id], [username_id], [TxnDate], [Duration]) VALUES (3, 1, CAST(N'2018-02-01T00:00:00.0000000' AS DateTime2), N'PT1H0M')
GO
SET IDENTITY_INSERT [dbo].[timetracking] OFF

3 回复 | 直到 5 年前

frmbelz Dikla Behor 5 年前

恐怕没有内置功能。我刚刚写了一个,它是完全内联的ad-hoc SQL-但不会很快。。。

您可以尝试以下操作:

CREATE FUNCTION dbo.ConvertISO8601Periode2Seconds(@periode VARCHAR(100))
RETURNS TABLE
AS
RETURN
WITH Variables AS
(
    SELECT CASE WHEN CHARINDEX('T',@Periode)>0 THEN CHARINDEX('M',@periode,CHARINDEX('T',@Periode))-1 ELSE -1 END AS posMinute
          ,REPLACE(SUBSTRING(@periode,2,LEN(@periode)),'T','0') AS Original
)
,SwitchMinute AS
(
    SELECT CASE WHEN posMinute>0 THEN STUFF(Original,posMinute,1,'X') ELSE Original END AS WorkWith
    FROM Variables
)  
,recCTE AS
(      
    SELECT CAST(0 AS FLOAT) AS Seconds
          ,1 AS StartPos
          ,2 AS nextPos
          ,WorkWith 
    FROM SwitchMinute

    UNION ALL

    SELECT CASE SUBSTRING(r.WorkWith,r.nextPos,1) 
                WHEN 'Y' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 365 * 24 * 60 * 60
                WHEN 'M' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 30 * 24 * 60 * 60
                WHEN 'W' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 7 * 24 * 60 * 60
                WHEN 'D' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 24 * 60 * 60
                WHEN 'H' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 60 * 60
                WHEN 'X' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 60
                WHEN 'S' THEN CAST(SUBSTRING(r.WorkWith,r.StartPos,r.nextPos-r.StartPos) AS FLOAT) * 1
           ELSE 0
           END + r.Seconds
           ,CASE WHEN SUBSTRING(r.WorkWith,r.nextPos,1) IN('Y','M','W','D','H','X','S') THEN r.nextPos+1 ELSE r.StartPos END 
           ,r.nextPos + 1
           ,r.WorkWith
    FROM recCTE AS r
    WHERE r.nextPos<=LEN(r.WorkWith)
)
SELECT @periode AS ISO8601Periode
      ,MAX(Seconds) AS Seconds 
FROM recCTE;
GO

--你这样称呼它

DECLARE @SomePeriodes TABLE(p VARCHAR(100));
INSERT INTO @SomePeriodes VALUES('P3Y6M4DT12H30M5S'),('PT8H0M'),('PT7H30M'),('PT1H0M');

SELECT ISO2Sec.ISO8601Periode
      ,ISO2Sec.Seconds
      ,ISO2Sec.Seconds/(60*60) Hrs
FROM @SomePeriodes AS p
CROSS APPLY dbo.ConvertISO8601Periode2Seconds(p.p) AS ISO2Sec;
GO

--清理

DROP FUNCTION dbo.ConvertISO8601Periode2Seconds;

结果

ISO8601Periode      Seconds     Hrs
P3Y6M4DT12H30M5S    110550605   30708,5013888889
PT8H0M              28800       8
PT7H30M             27000       7,5
PT1H0M              3600        1

一些解释

遗憾的是,ISO 8601期间可以使用 M 几个月甚至几分钟。如果有 T 在字符串中 M 在 T 是分钟。我将此替换为 X 以便直接穿过管柱。

中心代码是一个从字符串开始的递归CTE 逐字符 ,记住最后一个数字的起始位置并查找非数字。只要找到一个字母,前面的数值就会相应地相乘,并与前面的值相加,从而累加所有值。

HABO 6 年前

下面的代码演示了ISO 8601 Duration格式子集的穷人解析器。

它只处理时间字段:小时、分钟和秒。
它不处理日期字段:年、月、周和天。可以通过以简单的方式扩展代码来添加它们。
支持小数,如“PT12.5S”。
它无法验证字段值,尤其是小数,是否格式正确,例如“0,2.3”。(CAST可能会捕获一些错误,但解析器没有查找它们。)
它不能验证只有最小的单位才有小数。(这可以作为最终选择中的检查来实现,以确保基点(如果有)只出现在最小的非空单位中。)
它不执行一般顺序的验证,即字段必须从较大的单位前进到较小的单位。(这可以通过添加列来跟踪“已使用”字段来实现,例如作为位掩码。)

-- Sample input.
declare @Period as VarChar(128) = 'PT12H56.7S';

-- Parser.
with PeriodFields as (
  select Cast( '' as VarChar(128) ) as Hours, Cast( '' as VarChar(128) ) as Minutes, Cast( '' as VarChar(128) ) as Seconds,
    Cast( '' as VarChar(128) ) as Field,
    Substring( @Period, 2, 1 ) as Character, -- The next character to be processed.
    Substring( @Period, 2, 128 ) as Remainder -- The remainder of the input string.
    where LEFT( @Period, 2 ) = 'PT' -- Handle only periods (P) that consist only of a time (T) without years, months, weeks or days.
  union all
  select
    -- Save the accumulated field value when there is a field identifier, i.e. 'H', 'M' or 'S'.
    case when Character = 'H' then Field else Hours end,
    case when Character = 'M' then Field else Minutes end,
    case when Character = 'S' then Field else Seconds end,
    -- Accumulate characters in   Field   until there is a field identifier, i.e. 'H', 'M' or 'S'.
    Cast( case when Character like '[0-9.,]' then Field + Character else '' end as VarChar(128) ),
    Substring( Remainder, 2, 1 ), Substring( Remainder, 2, 128 )
    from PeriodFields
    where Remainder != '' )
  select *,
    -- Assemble the field values into an instance of   Time .
    Cast( DateAdd( millisecond, Cast( Seconds as Float ) * 1000.0,
      DateAdd( second, Cast( Hours as Float ) * 3600 + Cast( Minutes as Float ) * 60, 0 ) ) as Time ) as Period
    from PeriodFields
    where Remainder = ''; -- Comment out this line to see the intermediate results.

Zohar Peled 6 年前

与当前的答案不同,我想建议一种不基于递归CTE的解决方案。

我的解决方案根本不验证这些值,只是尝试解析它们。我只实现了对时间部分(小时、分钟和秒)的解析,但很容易扩展以支持其他部分。

尽管如此,我不确定这是否是处理持续时间的最佳方式。一旦指定了月数,就无法使用确定性函数将ISO8601持续时间转换为表示任何时间单位的数字,因为一个月可以有28到31天的时间。

在我看来,这样的持续时间只能通过从另一个日期时间值中加上或减去持续时间来计算日期时间值。

现在,讨论够了,让我们看看一些代码!

使用cte获取持续时间所有部分的位置, 和另一个处理缺失值的cte(即 PT30M ):

;WITH CTE1 AS
(
    SELECT  [qbsql_id], 
            [username_id], 
            [TxnDate], 
            [Duration],
            CHARINDEX('P', Duration) As Ppos,
            NULLIF(CHARINDEX('Y', Duration), 0) As Ypos,
            NULLIF(CHARINDEX('M', Duration), 0) As Monpos,
            NULLIF(CHARINDEX('D', Duration), 0) As Dpos,
            NULLIF(CHARINDEX('T', Duration), 0) As Tpos,
            NULLIF(CHARINDEX('H', Duration), 0) As Hpos,
            NULLIF(CHARINDEX('M', Duration, CHARINDEX('T', Duration)), 0) As Minpos,
            NULLIF(CHARINDEX('S', Duration), 0) As Spos
    FROM timetracking
), CTE2 AS
(
    SELECT  [qbsql_id], 
            [username_id], 
            [TxnDate], 
            [Duration],
            Ppos,
            COALESCE(Ypos, Ppos) AS Ypos,
            COALESCE(Monpos, Ypos, Ppos) AS Monpos,
            COALESCE(Dpos, Monpos, Ypos, Ppos) AS Dpos,
            COALESCE(Tpos, Dpos, Monpos, Ypos, Ppos) AS Tpos,
            COALESCE(Hpos, Tpos, Dpos, Monpos, Ypos, Ppos) AS Hpos,
            COALESCE(Minpos, Hpos, Tpos, Dpos, Monpos, Ypos, Ppos) AS Minpos,
            COALESCE(Spos, Minpos, Hpos, Tpos, Dpos, Monpos, Ypos, Ppos) AS Spos
    FROM CTE1
)

从该cte中选择,计算其表示为浮点值的小时数:

SELECT  [qbsql_id], 
        [username_id], 
        [TxnDate], 
        [Duration],
        0.0 + 
        CASE WHEN Ppos = 1 AND Tpos > 0 THEN -- a period containing a time part
            CASE WHEN Hpos > Tpos THEN
                ISNULL(CAST(SUBSTRING([Duration], Tpos+1, Hpos - Tpos-1) as float), 0)
            ELSE 
                0
            END
            + 
            CASE WHEN Minpos > Hpos THEN
                ISNULL(CAST(SUBSTRING([Duration], Hpos+1, Minpos - Hpos-1) as float), 0) / 60.0
            ELSE 
                0
            END 
            +
            CASE WHEN Spos > Minpos THEN
                ISNULL(CAST(SUBSTRING([Duration], Minpos+1, Spos - Minpos-1) as float), 0) / 60.0 /  60.0
            ELSE 
                0
            END 
        END AS DurationInHours
FROM CTE1

结果:

qbsql_id    username_id     TxnDate                 Duration    DurationInHours
1           1               02.02.2018 00:00:00     PT8H0M      8
2           2               01.02.2018 00:00:00     PT7H30M     7,5
3           1               01.02.2018 00:00:00     PT1H0M      1

You can see a live demo on rextester.