How to convert MySQL datetime field into timestamp using Ruby?

I have two fields:

last_modified : datetime
updated_at    : timestamp

updated_at is a new field which has just been added to the table, last_modified contains accurate dates.

I want to be able to loop through all of the rows in the table, selecting the last_modifed datetime, converting it into a timestamp and saving it in 开发者_运维技巧the new updated_at field.

Have no idea how to do this, any help is much appreciated!

Try

update table_name set updated_at=unix_timestamp(last_modified);

I don't why you want to store same data twice. Storing same data twice may result into inconsistency.

If you want timestamp in your code, you can do that in code itself.

If your table is called some_table, then you can put this in your migration's up class method:

execute 'UPDATE some_table SET updated_at = last_modifed'

MySQL should be able to convert from datetime to timestamp automatically. In an SQL SET clause, the columns are specific to a single row and the SET applies to all matched rows; without a WHERE clause the UPDATE applies to all rows in the table. Remember that SQL is set-based so everything operates on sets of rows and loops don't really fit the model.

I'm assuming that you're using ActiveRecord as you have a timestamp called updated_at. If you're not using ActiveRecord then you'll have to find another way to send the SQL UPDATE to the database.