CODEWITHSUNDEEP
mysqlsql
Phil
Phil

Reputation: 1849

mysql, need to update column with random dates

I have a 'timestamp' type column in my table called updated_date. When adding a column to the table, all rows got updated to the same updated_date. Not a disaster as we're still in testing, but it kind of broke the functionality of our site (which shows things in order of updated_date).

Is there a way I can change all the updated_date values in the column (but where id is lower than x) to some random date (or an incremental date)?

Thanks in advance!

Upvotes: 3

Views: 1019

Answers (3)

cristi _b
cristi _b

Reputation: 1813

Well, you could do this

UPDATE table SET updated_time = NOW() WHERE id < x

Given id belongs to table

in case you want some random data from the past

UPDATE test2 SET update_time = NOW() - interval rand()*120 day - interval rand()*36000 second WHERE id < x

Tweak it to your needs

Upvotes: 2

Joshua Martell
Joshua Martell

Reputation: 7212

Timestamps are just the number of seconds since the epoch (1970-01-01 00:00:01). If you start with a base timestamp, you can just add a random number of seconds since that number and you have random dates.

Upvotes: 0

Vinay Pai
Vinay Pai

Reputation: 7758

This might solve your problem:

UPDATE updated_table SET timestamp = FROM_UNIXTIME(1e9 + id) WHERE id < x;

Basically it sets dates to Unix timestamps corresponding to 1 billion + id (1,000,000,000 unix timestamp is 2001-09-08 21:46:40). That way you get unique timestamps in order of id.

Upvotes: 2

Related Questions