I have completely revised my long (and mostly irrelevant - thanks for pointing that out!) answer.
I did however come to one valid conclusion in that previous draft of this answer: that the best approach was to make use of
CONVERT_TZ and you've made use of it!
However, your own answer (with
CONVERT_TZ(`timestamp`,'+00:00',REPLACE(`offset`,'00',':00')) -- S1/Statement_1
which you have marked as correct is, in fact, incorrect! It won't work in all circumstances, some of which are very important!
My answer now is:
CONVERT_TZ -- multi-line here for legibility, single line in SQL
SUBSTRING(offset, 1, 3),
SUBSTRING(offset, 4, 5)
) AS "Vérace's d_time", -- S2/Statement_2
And it does work under all circumstances.
The OP has come up with what I think is the optimal solution to this problem which makes use of the
[INSERT] function (kicking myself for not having spotted it!). In my notation, this would give:
CONVERT_TZ(time_utc, '+00:00', INSERT(offset, LENGTH(offset) -1, 0,' :'));
which also gives the correct result under all cicumstances.
To illustrate this, I did the following:
Created a table:
CREATE TABLE time_test
time_utc TIMESTAMP NOT NULL,
offset VARCHAR(5) NOT NULL
Added sample data:
INSERT INTO time_test
('2018-05-28 02:34:58', '+0300'),
('2018-05-28 07:50:12', '+0400'),
('2018-05-28 09:23:34', '+0530'), -- S1 fails because of 0530
('2018-05-28 12:16:56', '+1000'), -- S1 fails because of +1000
('2018-05-28 16:07:17', '-0200'),
('2018-05-28 20:02:05', '-0700'),
('2018-05-28 23:33:03', '-1000'), -- S1 fails because of -1000
('2018-05-28 23:33:03', '-0000'); -- S1 fails because of 0000
Then ran a query incorporating the
REPLACE answer and the
CONCAT(SUBSTRING( answer (which works for every
offset). Both answers are compared in the query result.
REPLACE(offset,'00',':00') AS "OP's offset", -- from S1
CONVERT_TZ(time_utc, '+00:00', REPLACE( offset, '00', ':00')) AS "OP's d_time",
CONCAT(SUBSTRING(offset, 1, 3), ':', SUBSTRING(offset, 4, 5)) AS "Vérace's offset", -- from S2
CONVERT_TZ(time_utc, '+00:00', CONCAT(SUBSTRING(offset, 1, 3), ':', SUBSTRING(offset, 4, 5))) AS "Vérace's d_time",
TIMESTAMPDIFF(HOUR, CONVERT_TZ(time_utc, '+00:00', REPLACE( offset, '00', ':00')), CONVERT_TZ(time_utc, '+00:00', CONCAT(SUBSTRING(offset, 1, 3), ':', SUBSTRING(offset, 4, 5)))) AS "OP diff Vérace"
-- `TIMESTAMPDIFF` is the comparison. Anything other than 0 is a fail!
And the result is (see the fiddle here):
time_utc OP's offset OP's d_time Vérace's offset Vérace's d_time OP diff Vérace
2018-05-28 02:34:58 +03:00 2018-05-28 05:34:58 +03:00 2018-05-28 05:34:58 0
2018-05-28 07:50:12 +04:00 2018-05-28 11:50:12 +04:00 2018-05-28 11:50:12 0
2018-05-28 09:23:34 +0530 null +05:30 2018-05-28 14:53:34 null
2018-05-28 12:16:56 +1:000 2018-05-28 13:16:56 +10:00 2018-05-28 22:16:56 9
2018-05-28 16:07:17 -02:00 2018-05-28 14:07:17 -02:00 2018-05-28 14:07:17 0
2018-05-28 20:02:05 -07:00 2018-05-28 13:02:00 -07:00 2018-05-28 13:02:05 0
2018-05-28 23:33:03 -1:000 2018-05-28 22:33:03 -10:00 2018-05-28 13:33:03 -9
2018-05-28 23:33:03 -:00:00 null -00:00 2018-05-28 23:33:03 null
no. 3 - offset =
'+0530' - no '00' string for the
REPLACE to work on, hence the
REPLACE just returns '0530 which in turn causes
CONVERT_TZ to return
NULL (as per the MySQL documentation - reference below. See below also for countries with timezones not on the hour),
no. 4 - offset =
+1000' - the first '00' string is replaced by ':00', hence the
REPLACE offset = '+1:00' which is read by
CONVERT_TZ as '+01:00' (and not '+10:00'), hence a discrepancy of +9 hours,
no. 7 - offset =
'-1000' - same as for record no. 4 except for the sign,
no. 8 - offset =
'-00:00' - the first '00' is replaced with ':00' which gives the
REPLACE answer an offset of ':00:00' which causes
CONVERT_TZ to return
With respect to the
'-00:00' (same answer with + also), the following countries use UTC (also erroneously called GMT) for part of the year (from here):
Greenland, Ireland, Iceland (all year), Britain.
A number of West African countries use UTC all year round:
Burkina Faso, Cote d'Ivoire (Ivory Coast), Gambia, Ghana, Guinea, Guinea-Bissau, Liberia, Mali, Mauritania, Saint Helena, Sao Tome and Principe, Senegal, Sierra Leone, Togo.
Furthermore, there are a number of time zones that have an offset of UTC+0 (i.e. = UTC):
AZOST – Azores Summer Time, EGST – Eastern Greenland Summer Time, WET – Western European Time, WT – Western Sahara Standard Time, Z – Zulu Time Zone
One would imagine that any app worth its salt should work for Western Europe?
CONVERT_TZ documentation (from here):
CONVERT_TZ() converts a datetime value dt from the time zone given by
from_tz to the time zone given by to_tz and returns the resulting
value. Time zones are specified as described in Section 5.1.12, “MySQL
Server Time Zone Support”. This function returns NULL if the arguments
Places that have non-integer time zone offsets (from here).
Some governments make local time zones decisions that deviate from the
norm. Note that India is +5½ from UTC, while Myanmar (Burma) is +6½,
Iran is +4½, Iraq is +3½, Nepal is +5¾ and Central Australia is +9½.
Venezuela is -4 1/2, the Canadian island of Newfoundland is -3½ hours
from UTC, and some smaller islands in French Polynesia are -9½, while
the Pitcairn Islands are -8½.
Finally, I would strongly urge you not to use
SQL keyword identifiers like
TIMESTAMP for database and/or table and/or field names. There are two reasons why doing this is a bad idea:
It makes your SQL difficult to read and debug. Say you get a message about a problem with a
TIMESTAMP - is that the data type or your variable?
Your application will be difficult to port should you ever decide to do so! Use lower_case identifiers with_words_separated_by_underscores and keep your SQL keywords and functions in UPPER CASE and always use singular names. This makes your SQL easy to read and debug (caveat: this is my opinion - others may not concur!).