How to convert MySQL datetime field into timestamp using Ruby?

Question

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!

Zimbabao · Accepted Answer · 2011-03-20 05:45:05Z

2

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.

answered Mar 20, 2011 at 5:45

Zimbabao

8,2803 gold badges32 silver badges36 bronze badges

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

2 Comments

TJChambers Over a year ago

While this sounds like an appropriate answer - what about the consideration for timezone? What if the data in last_modified is not stored implicitly as UTC?

Zimbabao Over a year ago

unix_timestamp retuns seconds from 1st Jan 0000Hrs UTC. Even if your machines timezone is different from UTC above function will adjust to that.

mu is too short · Accepted Answer · 2011-03-20 17:42:25Z

0

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.

answered Mar 20, 2011 at 17:42

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Collectives™ on Stack Overflow

How to convert MySQL datetime field into timestamp using Ruby?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related