MySQL – Generating 50k Rows of Unix Time Entries Auto-Incremented by 5 Minutes

auto-incrementMySQL

I am relatively new at using mysql, or any DB for that matter, so let me know if I'm doing this in a round-about way.

I have 4 similar tables with two rows columns each – unixtime + count. They all look something like this:

mysql> select * from wifiexporttotal limit 5;
+------------+-------+
| epoch      | count |
+------------+-------+
| 1446292800 |   298 |
| 1446293100 |   332 |
| 1446293400 |   375 |
| 1446293700 |   439 |
| 1446294000 |   412 |
+------------+-------+

etc…

These are log entries rounded to the nearest 5 minute interval for approx 6 months with around 50k entries each.

I want to join these in a single table with unixtime + 4 columns of values, and have done so successfully. However, there are some timestamps without a log entry in any of the four tables, and I would like for these to show zero instead of missing skipping rows.

So my question is – How can I generate a table with 50k rows and a single column that starts with a bigint (1446332400 for nov 1st in my case) and auto increments with 300 (5 minutes) per row? I assume I could LEFT JOIN the other 4 tables with this table on the timestamp and get an unbroken string of 5 minute intervals for the 6 months this way.

Best Answer

Plan A: (Drawback: you might still have gaps.)

  1. Find all epoch values from the 4 tables using UNION DISTINCT.
  2. LEFT JOIN from that table to the other 4 tables.

Plan B: (Drawback: I don't say how to do step 1.)

  1. Build a TEMPORARY table with every 5-min time for the next N months.
  2. LEFT JOIN ...

Plan C: (Drawback: Step 1.)

  1. Upgrade to MariaDB.
  2. Use a "sequence" table to 'virtually' generate enough integers.
  3. Use some simple arithmetic to convert the integers to epochs.
  4. LEFT JOIN ...
Related Question