MySQL CONVERT_TZ returns null

Question

MySQL CONVERT_TZ returns null, if timezone value is in plus.

example:

select CONVERT_TZ(now(),SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5),'+00:00');

it returns perfect time 2015年12月16日 10:32:19 when the time zone is in minus value. The same is not working when the time zone value is in plus. For an example @@session.time_zone returns 05:30. If I concat plus manually, it is working. Please let me know, if any alternative solutions are available.

Manually concatenated code:

select CONVERT_TZ(now(),concat("+",SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5)),'+00:00');

Thanks in advance.

Answer 1

When the difference is positive, the SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5) part returns a positive values, thats without the +.

But CONVERT_TZ expects the offset to contain either + or -, so without it the value is not proper offset and the function returns NULL as specified.

Simply said - results of timediff are not proper timezone offsets.

You might use DATE_ADD instead, it allows for - too.

• Nisar: Thanks, Is there any other options to get proper timezone?
• jkavalik: @Nisar I did not find any, but check the edited answer for an alternative.
• Nisar: Thanks again, Sorry! I already used this scenario which is creating some new issues using DATE_ADD and DATE_SUB.