Generate Timestamps in MySQL

Writing by 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

No Comments

No comments yet.

Leave a comment

You must be logged in to post a comment.

Shivdev Kalambi's Blog

Shivdev Kalambi is a Software Development Manager, previously a Principal Software Engineer at ArcSight/HP. With over 16 years' experience in software development, he's worked on several technologies and played different roles and contributed to all phases of projects. Non-tech activies include Ping-pong, Rock Climbing and Yoga at PG, Golf, Skiing, Swimming & a beer enthusiast.