Simple SQL date insertions using bash

Here’s a quick solution for populating a database with a series of records having increasing date values. For example, 2012-04-04, 2012-05-05, and so on. This is so I can prime a database with upcoming data that changes daily:

mysql> select * from mytable;
+------------+------+
| date       | ref  |
+------------+------+
| 2012-04-05 |    1 |
| 2012-04-06 |    2 |
| 2012-04-07 |    3 |
| 2012-04-08 |    4 |
| 2012-04-09 |    5 |
| 2012-04-10 |    6 |
| 2012-04-11 |    7 |
+------------+------+
7 rows in set (0.00 sec)

mysql>

I could have used a language like Perl, Python or Groovy, but in this case I chose a simple shell script.

The basic idea: call the ‘mysql’ command with a sequence of INSERT
statements. I’ve simplified this example to use just two columns – a date and an id. The inserting-the-data part is easy:

for id in 1 2 3 4 5 6 7
do
echo INSERT INTO blah \(date,ref\) VALUES \(...\);
done | mysql -u dbuser ...

The ‘for’ loop generates SQL statements on its standard output, which ‘mysql’ then executes line-by-line as it reads from the pipe.

The Date Calculations

To increment the date values, we can use a couple of features of the standard ‘date’ command. First, the format:

$ date +%Y-%m-%d
2012-04-04
$ start=$(date +%Y-%m-%d)
$ echo $start
2012-04-04
$

So that gets us nicely into ISO 8601 format, MySQL’s default. Now for adding days:

$ date --date "now + 1 day" +%Y-%m-%d
2012-04-05
$

So our shell script becomes:

for id in 1 2 3 4 5 6 7
do
  newdate=$(date --date "now + $id days" +%Y-%m-%d)
  echo INSERT INTO mytable \(date,ref\) VALUES \(\"$newdate\",$id\)\;
done | mysql -u dbuser -p -h 127.0.0.1 mydb

Bash Arithmetic

In my case I actually wanted to subtract days, so I could create a rolling ‘history’ of records, with the dates starting two days from today. (Also in my case, the ids were foreign keys.)

mysql> select * from mytable;
+------------+------+
| date       | ref  |
+------------+------+
| 2012-04-06 |   18 |
| 2012-04-05 |  351 |
| 2012-04-04 |  296 |
| 2012-04-03 |  550 |
| 2012-04-02 |  328 |
| 2012-04-01 |   19 |
| 2012-03-31 |   17 |
| 2012-03-30 |  549 |
| 2012-03-29 |  142 |
| 2012-03-28 |  551 |
+------------+------+
10 rows in set (0.00 sec)

mysql>

This can be done using bash’s Arithmetic Expansion construct, $((…)). I introduced a loop counter, i, incremented each time round the loop. The $((-i+2)) construct subtracts 2 from i and uses this value in the date argument:

i=0

for id in 18 351 296 550 328 19 17 549 142 551
do
  newdate=$(date --date "now + $((-i+2)) days" +%Y-%m-%d)
  echo INSERT INTO mytable \(date,ref\) VALUES \(\"$newdate\",$id\)\;
  let i++
done | mysql -u dbuser -p -h 127.0.0.1 mydb

That’s it. Let me know if you found it helpful.

 

This entry was posted in Uncategorized by peter. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

HTML tags are not allowed.