Time issues in Rails + MySQL Development

 

MySQL Time Zone

Display Time Zone Information

 
 

  
  
mysql> show variables like '%time_zone%'; 
  
  
+------------------+--------+ 
  
  
| Variable_name    | Value  | 
  
  
+------------------+--------+ 
  
  
| system_time_zone | CST    | 
  
  
| time_zone        | SYSTEM | 
  
  
+------------------+--------+ 
  
  
2 rows in set (0.00 sec) 
 
 

 

Set Time Zone Information

+ Is China's time zone, UTC + 8.

 
 

  
  
mysql> set time_zone = '+8:00'; 
  
  
Query OK, 0 rows affected (0.00 sec) 
 
 

 

Time zone in Rails

By default, rails writes data to the utc time, and then reads data to the utc time.

Setting config. time_zone can only ensure that the time to write data to the database is local, that is, when creating an object, created_at and updated_at use the set local time.

However, the read time may still be utc time and may need to be converted on the interface.

The utc time is recommended for rails, Which is unified, but is formatted as the local time when the interface is displayed.

 
 

  
  
Rake time: zones: all # view all time zones
  
  
Rake time: zones: local # view the local time zone
 
 

 

Default

We use

 
 

  
  
rails g scaffold post title:string content:string 
 
 

After a model is generated, open the db/migrate/201200_create_posts.rb file to view the following content.

 
 

  
  
class CreatePosts < ActiveRecord::Migration 
  
  
  def change 
  
  
    create_table :posts do |t| 
  
  
      t.string :title 
  
  
      t.string :content 
  
  
 
  
  
      t.timestamps 
  
  
    end 
  
  
  end 
  
  
end 
 
 

T. timestamps will generate the created_at and updated_at fields in the database. The two fields rails will automatically assign values without being manually specified.

After adding a post on the page, open the log/production. log file and you will see the following content.

 
 

  
  
INSERT INTO `posts` (`category_id`, `content`, `created_at`, `picture`, `published`, `title`, `updated_at`, `url`) VALUES (1, 'post100', '2012-11-01 05:13:45', NULL, 0, 'post100', '2012-11-01 05:13:45','post100') 
 
 

If this SQL statement is not displayed, add it to config/applicaiton. rb.

 
 

  
  
config.log_level = debug 
 
 

The debug level records each SQL statement in the log to facilitate debugging. Modify the level after publishing to the production environment.

Note that the value of created_at is clearly a record inserted at noon China time, but the time in the insert statement is utc time, eight hours behind, because China is GMT +.

The value inserted into the database is the utc time.

 

After the time zone is set

To solve this problem, add the following configuration in the config/application. rb file.

 
 

  
  
config.active_record.default_timezone = :local 
  
  
config.time_zone = 'Beijing' 
 
 

Insert data again, open the log file, and you will find that the time is changed to Beijing time, and the database is also inserted at Beijing time. After entering mysql-u root-p, the query result is also Beijing time.

Use

 
 

  
  
<%= created_at %> 
 
 

The displayed result is

 
 

  
  
2012-11-01 13:39:26 +0800 
 
 

There is no problem, but there is more time zone + 0800 information.

If you use

 
 

  
  
<%= post.updated_at.to_s(:db) %> 
 
 

The result is

 
 

  
  
2012-11-01 05:39:26 
 
 

No more than 0800, but the time has changed to utc time.

Use

 
 

  
  
<%= post.updated_at.localtime.to_s(:db) %>  
 
 

It becomes

 
 

  
  
2012-11-01 13:39:26 
 
 

There is no time zone + 0800 information, and the time is also the local time.It is converted to the local time and then formatted.

 

 
 

  
  
Created_at.utc # convert to utc time
  
  
Created_at.localtime # convert to local time
 
 

 

 

There is something special in the rails console.

 
 

  
  
1.9.3-p286: 013> p = Post. last
  
  
Post Load (0.3 ms) SELECT 'posts'. * FROM 'posts' order by 'posts'. 'id' desc limit 1
  
  
### <Post id: 67, title: "Guo Degang's French team", content: "asdfasdf", created_at: "05:39:26", updated_at: "05:39:26", url: "df", category_id: 1, published: false, picture: nil>
  
  
1.9.3-p286: 014> p. created_at
  
  
=> Thu, 01 Nov 2012 13:39:26 CST +
 
 

Have you noticed that in p = Post. the last query result shows that the time of created_at is utc, but when you press p. after created_at, the displayed value is changed to the local time.

 

Summary

Formatting time zones and dates is a required course for every programmer. It is as important as the various processing of strings and is frequently used.

By default, rails writes data to the utc time, and then reads data to the utc time.

Setting config. time_zone can only ensure that the time to write data to the database is local, that is, when creating an object, created_at and updated_at use the set local time.

However, the read time may still be utc time and may need to be converted on the interface.

The utc time is recommended for rails, Which is unified, but is formatted as the local time when the interface is displayed.

For any application, you should consider the language and storage itself, or even the time zone settings and some default values of the operating system, in order to better solve the time zone problem.

 

References

1. Convert local time to UTC in Rails

2. Rails 3 default datetime format without UTC

3. rails-related time zone settings

4. Date Time Format in RUBY

5. rails time format

6. Perfect solution to rails China Time Zone settings

7. Time Zones in Rails 2.1

8. MySQL time zone settings

9. Rails built-in time formatting

 

This article is from the "breakthrough IT architects" blog, please be sure to keep this source http://virusswb.blog.51cto.com/115214/1046723