如何在mysql中按日期排序 [英] How to order by date in mysql

问题描述

我的专栏数据是1-jul，16-jul，5-jul，7-jul，15-jul，28-jul，10-jul我希望这个订单按日期





1-jul，5-jul，7-ju，10-jul，15-jul，28-jul



我尝试了什么：



按日期从表格顺序中选择日期

my column data is 1-jul,16-jul,5-jul,7-jul,15-jul,28-jul,10-jul i want this order by date


1-jul,5-jul,7-jul,10-jul,15-jul,28-jul

What I have tried:

select date from table order by date

推荐答案

不要那样存储：永远不要将日期存储为字符串，以后总会出现问题。

相反，将其存储为DATETIME值和表中的单个行，而不是逗号分隔值，外键返回与其相关的另一个表行。从中生成存储的CSV列表几乎是微不足道的，其中按日期排序基于字符串的CSV数据列表是可能的，但实际上非常复杂。它也会在以后引起其他问题，这些问题也很复杂。



更改数据库，并始终以适当的类型存储数据。

Don't store it like that: never store dates a strings, it always gives problems later.
Instead, store it as DATETIME values and as individual rows in a table, not as comma separated values, with a foreign key back to the other table row they relate to. Generating a stored CSV list from that is almost trivial, where sorting by date a string based list of CSV data is possible but really pretty complicated. And it will cause other problems later as well, which are as complicated to fix.

Change your DB, and always store data in appropriate types.

< br>

引用：

如何在mysql中按日期排序

How to order by date in mysql

正如您所看到的，这是一个坏主意如果你想做更大的事情而不是按原样阅读那个日期，那么以普通人的形式存储日期。

所以建议永远都不要这样做。

日期是一个复杂的数据类型和计算机必须知道它是一个能够处理它的日期。

大多数语言都有DateTime数据类型，因为它是一个非常常见的数据，具有非常特定的操作。

如果您需要将日期存储为字符串，最实用的格式是yyyymmdd，今天为20160818。这主要是DateTime在内部做的事情。



对于你的问题，由于日期存储在一个糟糕的形式，你必须创建一个程序来处理它们。您的程序将读取一个字符串日期，将其拆分为组件并重建一个符合此格式yyyymmdd的字符串，以便能够对数据进行排序。如果您需要计算日期之间的天数，则必须执行另一个程序。

将字符串转换为DateTime类型会简化操作。

As you can see, it is a bad idea to store dates in common human form if you want to do something bigger than just read that date as is.
So the advice is never do it that way.
Date is a complicated data type and the computer must know that it is a date to be able to handle it.
Most languages have a DateTime data type because it is a very common data with very specific operations.
If you need to store a date as a string, the most practical format is "yyyymmdd", which gives "20160818" for today. this mostly what DateTime is doing internally.

For your problem, since the dates are stored in a bad form, you have to create a program to deal with them. Your program will read a string date split it in components and rebuild a string that will match this format "yyyymmdd" just to be able to sort the data. If you need to count days between dates, you have to do another program.
Transforming the string to DateTime type would simplify things.

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