CODEWITHSUNDEEP
mysqldatetime
user985723
user985723

Reputation: 628

mysql. How to select the last date for each hour

I have stock market data in a mysql database. I'd like to find the closing trade price for each hour.

I've come up with this query:

SELECT MAX(date), price, FROM tradetable WHERE date BETWEEN '2014-01-25 23:00:00' AND '2014-01-28 12:59:59' GROUP BY hour(date) ORDER BY date;

However my results are not ordered by the date properly. It's jumbled up. The hours and days are not in order.

What am I doing wrong?

Returned data:

2014-01-27 02:59:59 --- 815.37000
2014-01-27 03:59:59 --- 813.00000
2014-01-27 04:59:59 --- 808.59998
2014-01-27 05:59:59 --- 812.07001
2014-01-27 06:59:59 --- 816.00000
2014-01-27 07:59:59 --- 825.84998
2014-01-27 08:59:59 --- 824.00000
2014-01-27 09:59:59 --- 825.50000
2014-01-27 10:59:59 --- 828.91998
2014-01-27 11:59:59 --- 820.00000
2014-01-27 12:59:59 --- 816.90002
2014-01-27 13:59:59 --- 821.20001
2014-01-26 14:59:59 --- 822.88000
2014-01-26 15:59:59 --- 819.00000
2014-01-26 16:59:59 --- 817.79999
2014-01-26 17:59:59 --- 817.78003
2014-01-26 18:59:59 --- 819.98999
2014-01-26 19:59:59 --- 816.66998
2014-01-26 20:59:59 --- 822.15997
2014-01-26 21:59:59 --- 819.52002
2014-01-26 22:59:59 --- 820.00000
2014-01-26 23:59:59 --- 816.00000
2014-01-27 00:59:59 --- 825.39001
2014-01-27 01:59:59 --- 821.90002

Upvotes: 0

Views: 190

Answers (3)

Tin Tran
Tin Tran

Reputation: 6202

You'll want to select max(date) group by date and hour, then join that result back with tradetable to get price for each hour.

SELECT T1.date,T1.price
FROM tradetable T1
INNER JOIN
    (SELECT DATE(date) as tradedate,
            HOUR(date) as tradehour,
            MAX(date) as maxtime
     FROM tradetable
     WHERE date BETWEEN '2014-01-25 23:00:00' AND '2014-01-28 12:59:59'
     GROUP BY tradedate,tradehour
    )T2 
ON T1.date = T2.maxtime
ORDER BY T1.date

Upvotes: 0

JB9
JB9

Reputation: 49

SELECT MAX(date), price, FROM tradetable WHERE date BETWEEN '2014-01-25 23:00:00' AND '2014-01-28 12:59:59' 
GROUP BY hour(date) ORDER BY date, time DESC

Upvotes: 0

Barmar
Barmar

Reputation: 780673

Use:

GROUP BY DATE(date), HOUR(date)

You're combining times from different days in the same group.

Upvotes: 1

Related Questions