主页 / dba / 问题 / 148450
Accepted
GWR
Asked: 2016-09-01 07:10:41 +0800 CST

在 SQL Server 中将 rfc822 转换为 GMT DATETIME 或 Unix 时间戳

  • 772

我试图找到一种方法将 rfc822 字符串日期（带时区）转换为数据类型 DATETIME 的 GMT 值或数据类型 INT 的 unix 时间戳。例如：

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

我相信“UTC”和“GMT”部分实际上意味着同一件事，对于我的特定用例，我相信这是我将看到的仅有的两个时区值。

我在 SQL Server 2008r2 环境中工作。

我发现唯一能做到这一点的是下面的功能，但我想避免这样做有几个原因：

  • 它使用硬编码偏移量
  • 它需要注册表读取访问权限（我认为我的托管服务提供商不会拥有​​）
  • 我希望不要依赖于用户定义的函数

有没有更简单的方法来完成这种转换？更好的是我可以在 T-SQL 中本地使用而不是创建函数依赖项。

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 个回答

  1. Best Answer

    考虑到您提到的有关使用托管系统的内容，我不确定 SQLCLR 是否适合您，但如果您可以使用 SQLCLR，那么这在 .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;
}

    它将考虑历史时区/DST 信息。

    作为旁注：此功能以及能够指定区域性的功能在 4.0 版的SQL# SQLCLR 库（我是其作者）中。它被称为String_TryParseToDateTime并且在免费版本中可用:)。

    • 0

相关问题