主页 / coding / 问题 / 79491680
Accepted
MikeC
Asked: 2025-03-07 16:47:30 +0800 CST

将 XML 元素中的本地时间转换为 UTC

  • 772

我需要将 xml 列中的时间戳元素转换为 SQL Server 2019 中的 UTC 时间。

CREATE TABLE TestTable
   (    
       Id INT IDENTITY(1,1) NOT NULL,
       Data XML NOT NULL
   );

INSERT TestTable (Data) VALUES ('<root>
        <id>1</id>
        <timestamp>2024-08-20T10:55:37+10:00</timestamp>
    </root>');

我想将时间戳更改为“2024-08-20T00:55:37Z”。我正在使用“Data.modify('replace value of'”，但找不到新值基于旧值而不是简单字符串替换的示例。

sql-server

2 个回答

  1. Best Answer

    有几种方法可以做到这一点。我使用几个APPLYs 从 XML 中获取时间戳的值，然后使用 将其更改为 UTC AT TIME ZONE。然后，您可以使用modify来UPDATE使用该 XML 值sql:column：

    UPDATE TT
SET [Data].modify ('replace value of (/root/timestamp/text())[1] with sql:column("ts.utc")')
FROM dbo.TestTable TT
     CROSS APPLY TT.Data.nodes('root/timestamp') r(ts)
     CROSS APPLY (VALUES(r.ts.value('(./text())[1]','datetimeoffset(0)') AT TIME ZONE 'utc'))ts(utc);

    这将产生以下 XML：

    <root>
    <id>1</id>
    <timestamp>2024-08-20T00:55:37Z</timestamp>
</root>
    • 2

  2. 为什么不使用 datetimeoffset 进行存储：

    CREATE TABLE TestTable (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Data XML NOT NULL,
    TimestampUTC DATETIMEOFFSET NOT NULL
);

INSERT INTO TestTable (Data, TimestampUTC)
VALUES (
    '<root>
        <id>1</id>
        <timestamp>2024-08-20T10:55:37+10:00</timestamp>
    </root>',
    SWITCHOFFSET(CAST('2024-08-20T10:55:37+10:00' AS DATETIMEOFFSET), 0)
);

    按照建议，查询速度会更快：

    SELECT 
    Id, 
    Data, 
    CONVERT(NVARCHAR(50), TimestampUTC, 127) + 'Z' AS TimestampUTC_Formatted
FROM TestTable;
    • 0

相关问题