MYSQL - 从最新到最旧按升序排列时间戳值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11493463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL - Order timestamp values ascending in order, from newest to oldest?
提问by Craig van Tonder
I have come across a problem when trying to order certain results by their timestamp value.
我在尝试按时间戳值对某些结果进行排序时遇到了问题。
I would like these results displayed from the newest, to the oldest based on the timestamp values.
我希望根据时间戳值从最新到最旧显示这些结果。
So to explain this, imagine that there were 3 results:
为了解释这一点，假设有 3 个结果：
2012-07-11 17:34:57
2012-07-11 17:33:28
2012-07-11 17:33:07
This result set would be what I would require, but given the following query
这个结果集将是我所需要的，但给出以下查询
SELECT timestamp
FROM randomTable
ORDER BY timestamp ASC
I get:
我得到：
2012-07-11 17:34:57
2012-07-11 17:33:07
2012-07-11 17:33:28
This is as it is sorted by numerical value and 07comes before 28.
这是因为它按数值排序07并排在 之前28。
If i sort in descending order I get
如果我按降序排序，我会得到
2012-07-11 17:33:07
2012-07-11 17:33:28
2012-07-11 17:34:57
Which is what I am looking for... But it is in reverse.
这就是我正在寻找的......但它是相反的。
So my question is fairly simple, how could I sort these values in ascending order as I have described?
所以我的问题很简单，我如何按照我描述的升序对这些值进行排序？
EDIT:
编辑：
EDIT2:
编辑2：
CREATE TABLE `user_quotations` (
`id` int(100) NOT NULL AUTO_INCREMENT,
`quoteNumber` int(100) NOT NULL,
`lastModified` datetime NOT NULL,
`userId` int(100) NOT NULL,
`manufacturer` varchar(250) COLLATE latin1_general_ci NOT NULL,
`modelNumber` varchar(250) COLLATE latin1_general_ci NOT NULL,
`productDesc` varchar(1000) COLLATE latin1_general_ci NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `quoteNumber` (`quoteNumber`,`lastModified`,`userId`,`manufacturer`,`modelNumber`,`timestamp`),
KEY `productDesc` (`productDesc`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
回答by Ravinder Reddy
Your query :
您的查询 ：
SELECT timestamp
FROM randomTable
ORDER BY timestamp ASC;
is perfect. But I doubt about the results you have presented in your posting. You posted :
是完美的。但我怀疑您在帖子中提出的结果。你张贴：
2012-07-11 17:34:57
2012-07-11 17:33:07
2012-07-11 17:33:28
But results in your sqlbox shows :
但是您的 sqlbox 中的结果显示：
2012-07-11 17:34:57
2012-07-15 17:33:07
2012-07-15 17:33:28
Which are perfectly right.
这是完全正确的。
Is that a typo error in your posting?
If no, then try the following :
这是您的帖子中的拼写错误吗？
如果没有，请尝试以下操作：
SELECT timestamp( `timestamp` ) as 'timestamp'
FROM randomTable
ORDER BY 1 ASC;
回答by Ray
Check your create statement for the table. I expect your timestamp column is really a string.
检查表的创建语句。我希望你的时间戳列真的是一个字符串。
Show create table tablename;
回答by Jeremy Holovacs
if you write the query as:
如果您将查询编写为：
select q.`timestamp`
from user_quotations as q
order by q.`timestamp`
limit 30
you should have them ordered properly.
你应该正确订购它们。
If not, there is a problem with the timestamp data. Look for leading/ trailing spaces, odd characters, etc.
如果不是，则时间戳数据有问题。查找前导/尾随空格、奇数字符等。
回答by TheSatinKnight
screen shot shows two of the results from 15th and one from 11th. Probably affects the order a wee bit.
屏幕截图显示了 15 日的两个结果和 11 日的一个结果。可能会稍微影响订单。
