Mysql

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

  • May 29, 2018

我正在使用 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