在生成的列中使用 ISO 8601 時區偏移值時，有沒有辦法避免連結/嵌套“replace()”項？

我正在使用 MySQL 5.7 並且有一個表，其中包含用於儲存來自 Apache 通用日誌格式的訪問日誌的一些數據的以下列；從 MySQL 模式導出中提取的詳細資訊：

`timestamp` timestamp NULL DEFAULT NULL,
`offset` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
`date` date GENERATED ALWAYS AS ((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour)) VIRTUAL
`time` time GENERATED ALWAYS AS ((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour)) VIRTUAL
`hour` int(2) GENERATED ALWAYS AS (hour((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour))) VIRTUAL

如您所見，我正在儲存timestamp( 0000-00-00 00:00:00) 和offset( +00:00)，然後使用生成的列來計算date( 0000-00-00)、time( 00:00:00) 和hour( 0) 值。這很好用：我可以將 UTC 儲存timestamp為 UTC，然後——通過儲存該offset值——我可以動態地獲取非 UTC 形式的其他資訊。

但我不認為鍊式/嵌套REPLACE項目太熱。目標是能夠將ISO 8601時區偏移值作為計算的一部分-0400或+1000作為interval [some value] hour計算的一部分。

那麼有沒有更好的方法來解決這個問題？我可能能夠調整時區偏移量最初儲存的方式，因此它——基本上——正是我需要的列計算，但這看起來很混亂且不直覺，所以+/-[for digit]如果可能的話，我寧願使用這種格式。

在閱讀之後CONVERT_TZ，這似乎是處理這種情況的更簡潔的方法，因為我可以REPLACE用這樣的一個來減少三個 (!!!) 語句：

CONVERT_TZ(`timestamp`,'+00:00',REPLACE(`offset`,'00',':00'));

它似乎仍然有點草率——因為我仍然在處理必須轉換偏移值-0400的問題-04:00——但確實有效並且更清晰/更容易閱讀和理解。

也就是說，上述方法對於諸如+0530,+1000和之類的偏移值將失敗+0000。因此，與此不同的是，使用以下方法（使用INSERT()字元串函式，其中冒號 ( :) 插入基於由字元串本身長度計算的右側偏移值）更簡潔，並且適用於各種不同的偏移量：

CONVERT_TZ(`timestamp`,'+00:00',INSERT(`offset`,LENGTH(`offset`)-1,0,':'));

這些是使用以下範例時間戳和相關偏移量的結果：

SELECT CONVERT_TZ('2018-05-28 02:34:58','+00:00',INSERT('+0300',LENGTH('+0300')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 07:50:12','+00:00',INSERT('+0400',LENGTH('+0400')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 09:23:34','+00:00',INSERT('+0530',LENGTH('+0530')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 12:16:56','+00:00',INSERT('+1000',LENGTH('+1000')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 16:07:17','+00:00',INSERT('-0200',LENGTH('-0200')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 20:02:05','+00:00',INSERT('-0700',LENGTH('-0700')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 23:33:03','+00:00',INSERT('-1000',LENGTH('-1000')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 23:33:03','+00:00',INSERT('-0000',LENGTH('-0000')-1,0,':'));

結果如下：

• 2018-05-28 02:34:58偏移量+0300變為：2018-05-28 05:34:58
• 2018-05-28 07:50:12偏移量+0400變為：2018-05-28 11:50:12
• 2018-05-28 09:23:34偏移量+0530變為：2018-05-28 14:53:34
• 2018-05-28 12:16:56偏移量+1000變為：2018-05-28 22:16:56
• 2018-05-28 16:07:17偏移量-0200變為：2018-05-28 14:07:17
• 2018-05-28 20:02:05偏移量-0700變為：2018-05-28 13:02:05
• 2018-05-28 23:33:03偏移量-1000變為：2018-05-28 13:33:03
• 2018-05-28 23:33:03偏移量-0000變為：2018-05-28 23:33:03

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