Skip to Content
Author's profile photo John Appleby

Simple Script to generate more data

I thought this was a useful little script to share. It allows you to generate some more data based on existing data. In many cases you can find representative data on the internet but you need a lot more of it. It has two neat points:

1) Add a day to the time

add_seconds(TIMESTAMP,86400+(100*RAND())) AS TIMESTAMP

In my example I have lots of data but for one day. So this script adds one day, and between 0 and 100 seconds to the timestamp.

2) Change the price field

PRICE*(1+((RAND()-0.5)*0.1)) AS PRICE

This takes the price and adds + or – 10%.

It’s not that fast – about 10m records a minute – but it sufficed for me to create 1bn rows fairly quickly. Hope it helps someone.

SELECT add_seconds(TIMESTAMP,86400+(100*RAND())) AS TIMESTAMP, FIELD2, FIELD3,

PRICE*(1+((RAND()-0.5)*0.1)) AS PRICE

FROM TABLE ORDER BY TIMESTAMP

INTO TABLE;

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Rama Shankar
      Rama Shankar

      cool - thanks!

      Author's profile photo Former Member
      Former Member

      Thank you!

      Author's profile photo Jorge Weiss
      Jorge Weiss

      Smart!

      Thank you!

      /Weiss

      Author's profile photo Former Member
      Former Member

      excellent! thanks

      Author's profile photo Former Member
      Former Member

      Thanks 🙂

      Author's profile photo Former Member
      Former Member

      Holy Cow !