Início / coding / Perguntas / 79491680
Accepted
MikeC
Asked: 2025-03-07 16:47:30 +0800 CST

Converter hora local em elemento XML para UTC

  • 772

Preciso converter um elemento de registro de data e hora na minha coluna XML para horário UTC no SQL Server 2019.

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>');

Quero alterar o registro de data e hora para "2024-08-20T00:55:37Z". Estou brincando com "Data.modify('replace value of' ", mas não consigo encontrar um exemplo em que o novo valor seja baseado no valor antigo e não em uma simples substituição de string.

sql-server

2 respostas

  1. Best Answer

    Há algumas maneiras de fazer isso. Eu uso alguns APPLYs para obter o valor do timestamp do XML e então altero para UTC com AT TIME ZONE. Então você pode usar modifyo UPDATEvalor XML usando 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);

    Isso resulta no seguinte XML:

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

  2. Por que não usar um datetimeoffset para armazenamento:

    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)
);

    Então a consulta é mais rápida como sugerido com:

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

relate perguntas