Sql-Server

在 SQL Server 中將 rfc822 轉換為 GMT DATETIME 或 Unix 時間戳

  • December 17, 2017

我試圖找到一種方法將 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

鑑於您提到的使用託管系統的內容，我不確定 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並且在免費版本中可用:)。

引用自：https://dba.stackexchange.com/questions/148450

相關問答

Sql-Server

如何選擇有效的最大日期？

  • May 29, 2022
檢視問答
Sql-Server

如何將 Text 列中的 ISO8859 文本批量轉換為 nvarchar(max) 列中的 UTF8？

  • February 11, 2022
檢視問答
Sql-Server

LANGUAGE 和 DATEFORMAT 安全的日期/時間文字格式是什麼？

  • August 14, 2021
檢視問答
Sql-Server

有人可以告訴這兩個日期比較有什麼區別嗎？

  • July 24, 2020
檢視問答
Sql-Server

以 hhmmssff 格式提取 Datetime2(7) 的時間部分

  • May 8, 2020
檢視問答
Sql-Server

將 CHAR 轉換為 DATETIME，以便我可以使用 DATEADD()

  • May 8, 2020
檢視問答