Ruby convert_tz using MariaDB is returning nil

Question

When doing a regular:

select convert_tz(now(), "UTC", "Europe/London")

directly on a MariaDB server, it will produce:

+-------------------------------------------+
| convert_tz(now(), "UTC", "Europe/London") |
+-------------------------------------------+
| 2013-03-07 16:01:32                       |
+-------------------------------------------+

But if done through ruby 1.8.7 or 1.9.3 width:

q = 'select convert_tz(now(), "UTC", "Europe/London")'
ActiveRecord::Base.connection.select_all(q)

it will produce:

"convert_tz(now(), \"UTC\", \"Europe/London\")" => nil

Do the same against a MySQL setup:

q = 'select convert_tz(now(), "UTC", "Europe/London")'
ActiveRecord::Base.connection.select_all(q)

results in:

"convert_tz(now(), \"UTC\", \"Europe/London\")" => "2013-03-07 16:05:14"

Now tested in MySQLWorkbench and the same bad result occurs there, null is returned instead of the converted time.

So I guess it's something in MariaDB or?

score 1 · Answer 1 · 2014-03-27T16:51:02.770

1

You are likely missing the timezone info. You can generate this by using the mysql_tzinfo_to_sql utility or downloading the tables from the mysql dev site.

If you're on Mac or Linux, then the following is likely to work for you (might have to update the location of your zoneinfo)

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -Dmysql

edited Mar 27 '14 at 16:51

answered Mar 27 '14 at 16:41

score 0 · Accepted Answer · answered Mar 08 '13 at 12:22

0

Upgrading to 5.5.29-MariaDB solved the problem, so I guess it was related to 5.5.28.

answered Mar 08 '13 at 12:22

renemadsen

161
1
9

Ruby convert_tz using MariaDB is returning nil

2 Answers2