如何在mysql中对日期进行排序? [英] How to sort Date in mysql?

问题描述

我有一个关于如何在 mysql 中对日期进行排序的问题.这听起来可能很愚蠢，因为它可以通过 order by date asc 来完成.在我的表中，日期实际上是以字符串形式存储的，例如周三，2014-02-19.假设我有一张如下所示的表格.

I have a question on how to sort date in mysql. It may sound stupid as it can be done with order by date asc. In my table the date was actually stored in string with the format e.g. Wed,2014-02-19. Let say I have a table like below.

id | user_id | date
1  |   38    | Wed,2014-02-19
2  |   38    | Wed,2014-02-26
3  |   38    | Wed,2014-02-12
4  |   38    | Wed,2014-02-05

对于上述情况，我仍然可以使用 order by date asc 进行排序，因为所有日期都是星期三.但是假设一天是不同的，如下所示，

for the case like above, I am still able to sort using order by date asc since all the dates are Wednesday. But let say one of the day is different as shown below,

id | user_id | date
1  |   38    | Wed,2014-02-19
2  |   38    | Wed,2014-02-26
3  |   38    | Thu,2014-02-13
4  |   38    | Wed,2014-02-05

mysql 将首先对 Thu,2014-02-13 进行排序.

mysql will sort Thu,2014-02-13 first.

无论如何在mysql中做?

Anyway to do in mysql?

推荐答案

只需按逗号后的部分排序:

Simply sort by the part after the comma:

order by substring_index(date, ',', -1)

这篇关于如何在mysql中对日期进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！