CODEWITHSUNDEEP
mysqldatetimesplit
holden_caulfield
holden_caulfield

Reputation: 23

How to split a column into multiple columns ordered by date

I'm having a lot of problems when trying to get the data of my database in a proper format. I'm looking for the way to print the output of my database in columns by days.

My database has two columns, one with a timestamp in a format like YYYY-MM-DD H:M and another colum with data related to a speed.

I need to split the speed data in columns by day.

More or less, my database is something like this (with 288 data everyday):

 Timestamp          |   Speed

2014-05-01 00:03:00 |   328.71    
2014-05-01 00:18:00 |   331.31    
2014-05-01 00:33:00 |   193.83        
2014-05-02 00:03:00 |   321.21    
2014-05-02 00:18:00 |   290.39    
2014-05-02 00:33:00 |   283.43

And what I'm looking for is:

Hour    | 2014-05-01 | 2014-05-02

00:03:00 | 328.71    | 321.21     
00:18:00 | 331.31    | 290.39    
00:33:00 | 193.83    | 283.43

First of all, I've tried to do in a static way, but when I get it, I'll do dynamic.

The query I've made is

SELECT 
(CASE WHEN Timestamp>='2014-05-01' AND Timestamp<='2014-05-03' 
    THEN date_format(Timestamp,'%H:%i') ELSE 0 END) AS 'Hour', 
(CASE WHEN Timestamp>='2014-05-01' AND Timestamp<='2014-05-02' 
    THEN speed ELSE 0 END) AS '2014-05-01', 
(CASE WHEN Timestamp>='2014-05-02' AND Timestamp<='2014-05-03' 
    THEN velocidad ELSE 0 END) AS '2014-05-02' 
FROM `db_speed` 
WHERE Timestamp>='2014-05-01' AND Timestamp<='2014-05-10

With this query, I get a structure like this:

Hour     | 2014-05-01 | 2014-05-02

00:03:00 | 328.71     | 0     
00:18:00 | 331.31     | 0    
00:33:00 | 193.83     | 0    
00:03:00 | 0          | 321.21     
00:18:00 | 0          | 290.39
00:33:00 | 0          | 283.43

Upvotes: 2

Views: 1825

Answers (2)

fancyPants
fancyPants

Reputation: 51888

You are quite close, you're just missing group by and aggregate function.

SELECT 
(CASE WHEN Timestamp>='2014-05-01' AND Timestamp<='2014-05-03' 
    THEN date_format(Timestamp,'%H:%i') ELSE 0 END) AS 'Hour', 
MAX((CASE WHEN Timestamp>='2014-05-01' AND Timestamp<='2014-05-02' 
    THEN speed ELSE NULL END)) AS '2014-05-01', 
MAX((CASE WHEN Timestamp>='2014-05-02' AND Timestamp<='2014-05-03' 
    THEN speed ELSE NULL END)) AS '2014-05-02' 
FROM `db_speed` 
WHERE Timestamp>='2014-05-01' AND Timestamp<='2014-05-10'
GROUP BY `Hour`

Upvotes: 1

guido
guido

Reputation: 19194

Not really the result table you are asking for, but this solution maybe a better choice. You can start with a query like this, which gives each time per row, then comma separated columns for dates and speeds.

select TIME_FORMAT(Timestamp,'%H:%i:%s'), 
       group_concat(DATE(Timestamp)), 
       group_concat(speed) 
from db_speed GROUP BY MINUTE(Timestamp) ORDER BY Timestamp asc;

to get a result like this:

TIME        GROUP_CONCAT(DATE(TS))  GROUP_CONCAT(SPEED)
00:03:00    2014-05-01,2014-05-02   328.71,321.21
00:18:00    2014-05-01,2014-05-02   331.31,290.39
00:33:00    2014-05-01,2014-05-02   193.83,283.43

Demo here: http://sqlfiddle.com/#!2/fc340/11/0

Then in your language of choice you can split the grouped values into arrays or maps.

Upvotes: 0

Related Questions