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


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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.