Rails timestamp different than mysql timestamp

Question

My rails application has a Photo model which has a DATETIME column called taken_at.

I'm trying to understand why I get a different timestamp (num of secs since epoch) using the following methods:

Photo.find(3095).taken_at.to_i # => 1307292495
Photo.select("id, UNIX_TIMESTAMP(taken_at) as timestamp").where(:id => 3095).first['timestamp'] # => 1307317695
Photo.find(3095).taken_at.strftime('%s') # => "1307267295"

I'm using MySQL as the database and I'm using rails 4.1.7

thanks!

They're off by exactly 7 hours, which seems to indicate a time zone difference. — Darryl
– Darryl, Commented Dec 12, 2014 at 23:39
Darryl - i agree it is a timezone thing but I don't understand why. — Francois
– Francois, Commented Dec 13, 2014 at 2:48

Francois · Accepted Answer · 2014-12-13 07:19:28Z

2

So the reason that using UNIX_TIMESTAMP(taken_at) in MYSQL gives a different result is because MySQL assumes that taken_at is a date stored in the server's time zone and converts it to an internal value in UTC before making the timestamp calculation. But taken_at isn't a date stored in the server's time zone, it is a date stored in UTC because ActiveRecord stores all datetimes as UTC in the database and then converts them back to the local time zone when you read the record back from the database.

references:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_utc-timestamp

answered Dec 13, 2014 at 7:19

Francois

6835 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Rails timestamp different than mysql timestamp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related