MySQL CONVERT_TZ returns null 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.
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.