Skip to Content

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;

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply