Início / dba / Perguntas / 148450
Accepted
GWR
Asked: 2016-09-01 07:10:41 +0800 CST

Converter rfc822 para GMT DATETIME ou Unix timestamp no SQL Server

  • 772

Estou tentando encontrar uma maneira de converter uma data de string rfc822 (com fuso horário) em um valor GMT do tipo de dados DATETIME ou um registro de data e hora unix do tipo de dados INT. Por exemplo:

Mon, 15 Aug 2016 11:36:36 UTC
Mon, 29 Aug 2016 04:37:10 GMT
Wed, 27 Jul 2016 14:41:05 UTC

Acredito que as porções "UTC" e "GMT" realmente significam a mesma coisa e, para meu caso de uso específico, acredito que esses são os únicos dois valores de fuso horário que verei.

Estou trabalhando em um ambiente SQL Server 2008r2.

A única coisa que estou encontrando para fazer isso é a função abaixo, mas quero evitar isso por alguns motivos:

  • Ele usa deslocamentos codificados
  • Ele precisa de acesso de leitura de registro (que acho que não terei com meu provedor de hospedagem)
  • Espero não ter uma dependência de uma função definida pelo usuário

Existem maneiras mais simples e fáceis de realizar essa conversão? Melhor ainda seria algo que eu pudesse usar nativamente no T-SQL em vez de criar uma dependência de função.

CREATE FUNCTION udf_ConvertTime (
    @TimeToConvert  varchar(80),
    @TimeZoneTo     varchar(8)
)
RETURNS DateTime
AS
BEGIN
    DECLARE @dtOutput       datetime,
            @nAdjust        smallint,
            @hh             smallint,
            @Loc            smallint,
            @FromDate       datetime,
            @mm             smallint,
            @Ndx            tinyint,
            @TimeZoneFrom   varchar(80),
            @WkTime         varchar(80)

    SET @TimeZoneTo = ISNULL(@TimeZoneTo, 'LOCAL')

    /* ------------------------------------------------------------------------ */
    /*  Important: If you want to convert to your local time, the following is  */
    /*  necessary to handle daylight savings time. Your SQLServer installation  */
    /*  must allow this function to execute xp_regread.                         */
    /* ------------------------------------------------------------------------ */

    SET @Loc = CONVERT(smallint, DATEDIFF(hh, GETUTCDATE(), GETDATE()) * 60)
    IF @TimeZoneTo = 'LOCAL'
    BEGIN
        DECLARE @root VARCHAR(32),
                @key  VARCHAR(128),
                @StandardBias VARBINARY(8),
                @DaylightBias VARBINARY(8)
        SET @root = 'HKEY_LOCAL_MACHINE'
        SET @key  = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
        EXEC master..xp_regread @root, @key, 'StandardBias', @StandardBias OUTPUT
        EXEC master..xp_regread @root, @key, 'DaylightBias', @DaylightBias OUTPUT
        IF @StandardBias <> @DayLightBias
            SET @Loc = @Loc - 60
    END

    /* ------------------------------------------------------------------------ */
    /*  Build a temporary table of timezone conversions.                        */
    /* ------------------------------------------------------------------------ */

    DECLARE @Temp TABLE (
        TimeZone varchar(8),
        nOffset  smallint )

    INSERT INTO @Temp
    SELECT 'A',     60  UNION ALL
    SELECT 'ACDT', 630  UNION ALL
    SELECT 'ACST', 570  UNION ALL
    SELECT 'ADT', -180  UNION ALL
    SELECT 'AEDT', 660  UNION ALL
    SELECT 'AEST', 600  UNION ALL
    SELECT 'AKDT',-480  UNION ALL
    SELECT 'AKST',-540  UNION ALL
    SELECT 'AST', -240  UNION ALL
    SELECT 'AWDT', 540  UNION ALL
    SELECT 'AWST', 480  UNION ALL
    SELECT 'B',    120  UNION ALL
    SELECT 'BST',   60  UNION ALL
    SELECT 'C',    180  UNION ALL
    SELECT 'CDT', -300  UNION ALL
    SELECT 'CEDT', 120  UNION ALL
    SELECT 'CEST', 120  UNION ALL
    SELECT 'CET',   60  UNION ALL
    SELECT 'CST', -360  UNION ALL
    SELECT 'CXT',  420  UNION ALL
    SELECT 'D',    240  UNION ALL
    SELECT 'E',    300  UNION ALL
    SELECT 'EDT', -240  UNION ALL
    SELECT 'EEDT', 180  UNION ALL
    SELECT 'EEST', 180  UNION ALL
    SELECT 'EET',  120  UNION ALL
    SELECT 'EST', -300  UNION ALL
    SELECT 'F',    360  UNION ALL
    SELECT 'G',    420  UNION ALL
    SELECT 'GMT',    0  UNION ALL
    SELECT 'H',    480  UNION ALL
    SELECT 'HAA', -180  UNION ALL
    SELECT 'HAC', -300  UNION ALL
    SELECT 'HADT',-540  UNION ALL
    SELECT 'HAE', -240  UNION ALL
    SELECT 'HAP', -420  UNION ALL
    SELECT 'HAR', -360  UNION ALL
    SELECT 'HAST',-600  UNION ALL
    SELECT 'HAT', -150  UNION ALL
    SELECT 'HAY', -480  UNION ALL
    SELECT 'HNA', -240  UNION ALL
    SELECT 'HNC', -360  UNION ALL
    SELECT 'HNE', -300  UNION ALL
    SELECT 'HNP', -480  UNION ALL
    SELECT 'HNR', -420  UNION ALL
    SELECT 'HNT', -210  UNION ALL
    SELECT 'HNY', -540  UNION ALL
    SELECT 'I',    540  UNION ALL
    SELECT 'IST',   60  UNION ALL
    SELECT 'K',    600  UNION ALL
    SELECT 'L',    660  UNION ALL
    SELECT 'LOC',  @Loc UNION ALL
    SELECT 'LOCAL',@Loc UNION ALL
    SELECT 'M',    720  UNION ALL
    SELECT 'MDT', -360  UNION ALL
    SELECT 'MESZ', 120  UNION ALL
    SELECT 'MEZ',   60  UNION ALL
    SELECT 'MST', -420  UNION ALL
    SELECT 'N',    -60  UNION ALL
    SELECT 'NDT', -150  UNION ALL
    SELECT 'NFT',  690  UNION ALL
    SELECT 'NST', -210  UNION ALL
    SELECT 'O',   -120  UNION ALL
    SELECT 'P',   -180  UNION ALL
    SELECT 'PDT', -420  UNION ALL
    SELECT 'PST', -480  UNION ALL
    SELECT 'Q',   -240  UNION ALL
    SELECT 'R',   -300  UNION ALL
    SELECT 'S',   -360  UNION ALL
    SELECT 'T',   -420  UNION ALL
    SELECT 'U',   -480  UNION ALL
    SELECT 'UTC',    0  UNION ALL
    SELECT 'V',   -540  UNION ALL
    SELECT 'W',   -600  UNION ALL
    SELECT 'WEDT',  60  UNION ALL
    SELECT 'WEST',  60  UNION ALL
    SELECT 'WET',    0  UNION ALL
    SELECT 'WST',  540  UNION ALL
    SELECT 'WST',  480  UNION ALL
    SELECT 'X',   -660  UNION ALL
    SELECT 'Y',   -720  UNION ALL
    SELECT 'Z',      0

    /* ------------------------------------------------------------------------ */
    /*  If timezone is embedded within @TimeToConvert, separate it out. If we   */
    /*  can at all convert this date with SQL, do it.                           */
    /* ------------------------------------------------------------------------ */

    SET @Ndx = CHARINDEX(' ', REVERSE(@TimeToConvert))
    IF @Ndx > 0
    BEGIN
        SET @TimeZoneFrom = RIGHT(@TimeToConvert, @Ndx - 1)

        IF 'TRUE' = CASE
                        WHEN @TimeZoneFrom LIKE '[0-9][0-9][0-9][0-9]' THEN 'TRUE'
                        WHEN @TimeZoneFrom LIKE '[+][0-9][0-9][0-9][0-9]' THEN 'TRUE'
                        WHEN @TimeZoneFrom LIKE '[-][0-9][0-9][0-9][0-9]' THEN 'TRUE'
                        ELSE 'FALSE'
                    END
        BEGIN   -- This has already converted offset hhmm
            SET @hh = CONVERT(smallint, LEFT(@TimeZoneFrom, LEN(@TimeZoneFrom) - 2))
            SET @mm = CONVERT(smallint, RIGHT(@TimeZoneFrom, 2))
            SET @nAdjust = (@hh * 60) + @mm
            SET @TimeToConvert = LEFT(@TimeToConvert, LEN(@TimeToConvert) - @Ndx)
        END
        ELSE
        IF EXISTS (SELECT 1 FROM @Temp
                   WHERE  TimeZone = @TimeZoneFrom)
            SET @TimeToConvert = LEFT(@TimeToConvert, LEN(@TimeToConvert) - @Ndx)
        ELSE
            SET @TimeZoneFrom = NULL
    END

    IF ISDATE(@TimeToConvert) = 1
        SET @FromDate = CONVERT(datetime, @TimeToConvert)

    SET @TimeZoneFrom = ISNULL(@TimeZoneFrom, 'LOCAL')

    /* ------------------------------------------------------------------------ */
    /*  We are providing a varchar(80) date field to facilitate RFC822 dates.   */
    /* ------------------------------------------------------------------------ */

    IF @FromDate IS NULL
    BEGIN
        SET @Ndx  = 1

        SET @WkTime = REPLACE(@TimeToConvert, ',', '')
        SET @WkTime = REVERSE(
                        SUBSTRING(
                            REVERSE(
                                SUBSTRING(@WkTime, 5, LEN(@WkTime))
                                   ), @Ndx, LEN(@WkTime)))

        IF CHARINDEX(' ', @WkTime)     = 4  AND
           CHARINDEX(' ', @WkTime, 5)  = 7  AND
           CHARINDEX(':', @WkTime, 8)  = 10 AND
           CHARINDEX(':', @WkTime, 11) = 13 -- Means we have no year
            SET @WkTime = LEFT(@WkTime, 7) + CONVERT(varchar(5), YEAR(GETDATE())) + SUBSTRING(@WkTime, 7, 40)

        IF ISDATE(@WkTime) = 1
            SET @FromDate = CONVERT(datetime, @WkTime)
    END

    IF @FromDate IS NULL
        RETURN @FromDate

    /* ------------------------------------------------------------------------ */
    /*  If the from and to are the same, we need go no further.                 */
    /* ------------------------------------------------------------------------ */

    IF ISNULL(@TimeZoneFrom, '') IN (ISNULL(@TimeZoneTo, ''), ISNULL(@TimeZoneTo, 'LOCAL'))
        RETURN @FromDate

    /* ------------------------------------------------------------------------ */
    /*  Return the difference between the from/to timezones.                    */
    /* ------------------------------------------------------------------------ */

    IF @nAdjust IS NULL
    BEGIN
        SELECT @nAdjust = nOffset
        FROM   @Temp
        WHERE  timeZone = @TimeZoneFrom

        IF EXISTS (SELECT 1 FROM @Temp
                   WHERE  timeZone = @TimeZoneTo)
            SELECT @nAdjust = nOffset - @nAdjust
            FROM   @Temp
            WHERE  timeZone = @TimeZoneTo
    END

    SET @dtOutput = DATEADD(n, ISNULL(@nAdjust, 0), @FromDate)
    RETURN @dtOutput
END
sql-server sql-server-2008-r2

1 respostas

  1. Best Answer

    Não tenho certeza se o SQLCLR é uma opção para você, considerando o que você mencionou sobre o uso de um sistema hospedado, mas se você pode usar o SQLCLR, isso é bastante direto em .NET / C#:

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static DateTime? DateTimeFromRFC822([SqlFacet(MaxSize = 50)] SqlString StringValue)
{
  DateTime __ReturnVal = new DateTime();

  if (DateTime.TryParse(StringValue.Value, out __ReturnVal);
  {
    return (DateTime?)__ReturnVal;
  }

  return (DateTime?)null;
}

    E levará em consideração as informações históricas do fuso horário / DST.

    Como observação: essa funcionalidade, além de poder especificar a cultura, está na biblioteca SQL# SQLCLR (da qual sou o autor) a partir da versão 4.0. Chama-se String_TryParseToDateTime e está disponível na versão Free :).

    • 0

relate perguntas