Generate Timestamps in MySQL

Writing by shivdev on Thursday, 1 of October , 2009 at 5:20 pm

So you want to populate a new column in MySQL with fake dates?

Here’s how to add a new datetime column in SQL

alter table city_new add column mytime datetime;

Here’s how to populate it with fake timestamps data

update city_new set mytime = select from_unixtime(
unix_timestamp(‘2009-01-01 01:00:00’)+floor(rand()*31536000)
);

Here’s a more generic formula

select from_unixtime(
unix_timestamp( ‘start timestamp’)
+floor(rand()* (max interval in seconds) )
);

Leave a comment

Category: Tips and Tricks

• Add this post to
• Del.icio.us -
• Digg -
• -
• Tweet -

No comments yet.

You must be logged in to post a comment.