Mysql
在生成的列中使用 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