TL DR
You have a performance issue due to the way you are trying to solve a timestamp issue. You will be well served by handling your time conversions differently.
Your message does not explain where you get the time zone information from. As a result, it is unclear why (or how) you are actually processing time zones. Sure, there are many ways to avoid time zone gymnastics, but they are not within the scope of your published code. However, the last example in this post shows one way to add the session time zone to each row in the query results.
As for your problem with Ruby on Rails, you are not actually creating Time , you are actually creating an instance of ActiveSupport :: TimeZone from ActiveSupport :: TimeZone :: ThreadSafe, and the ActiveSupport and thread safety libraries are required to accurately evaluate performance. You cannot force Ruby objects to create objects faster, but you can avoid headaches by using your database to perform transformations.
In addition, while SQL-99 and PostgreSQL define timestamps with time zones as the actual column types, neither MySQL nor MariaDB. Thus, it is actually unclear how you plan to store this converted data, but it seems you need to reconsider your approach for both speed and data processing.
No matter what you are really trying to do, in almost every case the right answer is to get more work out of Rails and into the database. The following are examples of MySQL / MariaDB.
Database timestamps from milestone seconds
You can simply transfer the data in the form of epoch-seconds to MySQL or MariaDB and convert the database using the FROM_UNIXTIME function, rather than trying to convert to Ruby. For instance:
> SELECT FROM_UNIXTIME(1439135459.6); +-----------------------------+ | FROM_UNIXTIME(1439135459.6) | +-----------------------------+ | 2015-08-09 10:50:59.6 | +-----------------------------+ 1 row in set (0.00 sec)
It depends on whether epoch-seconds should be stored as numbers or as a timestamp, and therefore whether data should be converted to storage or retrieval. The answer will most likely depend on how you plan to search and join your data, so your mileage will vary.
Time Zone Conversion
If you just want to use one time zone and add it to your timestamp, this is trivial if you use system or session time zones. For instance:
> SELECT CONCAT(FROM_UNIXTIME(1439135459.6), " ", @@session.time_zone); +---------------------------------------------------------------+ | CONCAT(FROM_UNIXTIME(1439135459.6), " ", @@session.time_zone) | +---------------------------------------------------------------+ | 2015-08-09 10:50:59.6 -05:00 | +---------------------------------------------------------------+ 1 row in set (0.00 sec)
If you want to do more complex things, you first need to make sure your time zone tables are full if you plan on using zone names rather than numeric offsets. See mysql_tzinfo_to_sql for more details.
If you use named zones or numeric offsets, call CONVERT_TZ in your arguments. For instance:
> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00'); +-----------------------------------------------------+ | CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') | +-----------------------------------------------------+ | 2004-01-01 22:00:00 | +-----------------------------------------------------+ 1 row in set (0.02 sec)
Introducing everything together
The following works on MariaDB and MariaDB uses a convenient sequence mechanism to avoid having to fill out the sequence table or actually enter any values to make an example work. However, the principle (and speedup!) Should be similar to MySQL, but you really have to take on the task of first loading the data from the second era or timestamps into a table.
-- Set a session time zone to concatenate with query results. SET time_zone = '-5:00'; -- Load sequence engine on MariaDB. -- Skip on MySQL, which does not currently have a sequence engine. INSTALL SONAME "ha_sequence"; -- Select from the sequence on MariaDB. -- Change query to SELECT...FROM a table on MySQL. SELECT CONCAT( FROM_UNIXTIME(seq), @@session.time_zone ) FROM seq_1439135459_to_1439165458;
This returns the set as follows:
+---------------------------------------------------------------+ | CONCAT( FROM_UNIXTIME(seq), @@session.time_zone ) | +---------------------------------------------------------------+ | 2015-08-09 10:50:59-05:00 | | 2015-08-09 10:51:00-05:00 | | 2015-08-09 10:51:01-05:00 | | ... | | 2015-08-09 19:10:57-05:00 | | 2015-08-09 19:10:58-05:00 | +---------------------------------------------------------------+ 30000 rows in set (0.02 sec)
On my old, slowest laptop, it returned 30,000 rows in 0.02 seconds or less. Server-level hardware should not even notice the request, but, of course, your mileage may change.