« MySQL Operator Precedence | Main | Boston MySQL Meetup December 12th »

December 6, 2005

Creating Time-Sensitive Sample Data

I'm digging this newly-discovered method for creating sample data that is time sensitive. What I mean is sample data that will work correctly when used in the future with time-based queries.

Let me explain further . . . I'd like to create some sample data to use with a stored function that looks for data that's been spaced over regular intervals in the past few hours. If the sample data has the timestamps from the database I'm using now, the function won't demo correctly because the data will be too old when it is being used elsewhere.

Using now() normally works for this kind of stuff, but I need data spread over an interval prior to whatever now() happens to be then. Lo and behold, the date and time functions documentation steered me to date_sub(), which allows you to take a date and subtrack in certain intervals. This is perfect for my set of INSERT statements for the sample data:

INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 100 minute),'23.70');
INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 90 minute),'23.75');
INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 80 minute),'23.76');
INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 70 minute),'23.73');
INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 60 minute),'23.74');
INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 50 minute),'23.79');
INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 40 minute),'23.81');
INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 30 minute),'23.83');
INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 20 minute),'23.84');
INSERT INTO stockprice VALUES ('AMD',date_sub(now(), interval 10 minute),'23.82');

What you're seeing here is a set of ten records that will be created, each one ten minutes apart working backward in ten minute increments from the current time on the server where the data is inserted. Perhaps there are other uses for these kind of time-sensitive INSERTs . . . for now this will do the trick perfectly.

Yes, this may seem strangely familiar to those who've read the December Linux Magazine article on MySQL 5 that didn't quite get to covering stored functions (and a few other MySQL 5 new features).

Update: Giuseppe notes that inserting time intervals is something that can be done using the FOR EACH loops in the MySQL 5 general purpose routine library.

Arjen points out that you can also perform this kind of interval math directly in the query like:

INSERT INTO stockprice VALUES ('AMD',now() - interval 10 minute,'23.82');

Posted by mike at December 6, 2005 11:59 PM