mysql。如何选择每小时的最后日期

Question

我的 mysql 数据库中有股票市场数据。我想找到每小时的收盘交易价格。

我提出了这个查询:

SELECT MAX(日期)、价格、FROM tradetable WHERE date BETWEEN '2014-01-25 23:00:00' AND '2014-01-28 12:59:59' GROUP BY hour(日期)按日期排序；

但是我的结果没有按日期正确排序。已经乱七八糟了时间和日期不按顺序排列。

我做错了什么？

返回数据:

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

Answer 1

用途:

GROUP BY DATE(date), HOUR(date)

您正在将不同日期的时间合并到同一组中。