3

So I have to use a database which has time as column (there's a column for every 10 min in a specific day), the id in here isn't a primary key even though it's unique (and a primary key) in another table. And the rows equals to a day. So I'd like to have the value for each time slot with it's datetime on a row.

Is that even possible ? I could write a little program that recreate a table with what I need but I'd like to have as less duplicate data as possible.

What I have :

+---+----------+-----+-----+-----+-----+
|ID |   DATE   |00h00|00h10|00h20|00h30|
+---+----------+-----+-----+-----+-----+
|1  |2016-09-28|80   |79   |75   |73   |
+---+----------+-----+-----+-----+-----+
|1  |2016-09-27|82   |80   |76   |74   |
+---+----------+-----+-----+-----+-----+

What I'd like to have :

+---+----------------+-----+
|ID |        DATE    |VALUE|
+---+----------------+-----+
|1  |2016-09-28 00h00|80   |
|1  |2016-09-28 00h10|79   |
|1  |2016-09-28 00h20|75   |
|1  |2016-09-28 00h30|73   |
+---+----------------+-----+
|1  |2016-09-28 00h00|82   |
|1  |2016-09-28 00h10|80   |
|1  |2016-09-28 00h20|76   |
|1  |2016-09-28 00h30|74   |
+---+----------------+-----+
Jérôme B
  • 311
  • 5
  • 18

1 Answers1

4

The simplest way is to use union all:

select id, date_add(date, interval 0 minute) as date, `00h00` as value from t union all
select id, date_add(date, interval 10 minute) as date, `00h10` as value from t union all
. . .

If you have a numbers table, you can also do:

select t.id, date_add(date, interval (n.n - 1) * 10 minute) as date,
       (case when n = 1 then `00h00`
             when n = 2 then `00h10`
             . . .
        end) as value
from t join
     numbers n
     on n.n < 24 * 6;

I don't think there is any way to do this in MySQL without listing all the columns in some way. You can use another query or spreadsheet to generate the SQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You're a genius ! It works, it's a bit ugly since they are 24*6 columns but waouh thank you ! – Jérôme B Sep 28 '16 at 12:34
  • There's just a little typo in you're answer it's `date_add()` not `dateadd()`, btw the table numbers means `00h00`, `00h10` ... ? – Jérôme B Sep 28 '16 at 12:37