按时间戳排序数据库记录 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9301976/
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
Ordering database records by timestamp - Oracle
提问by name_masked
For the query:
对于查询：
SELECT MAX(LOG_CREATION_DATE),COL_A, COL_B
FROM IMPORT_LOG
GROUP BY TO_CHAR(LOG_CREATION_DATE, 'MM-DD-YYYY'),
COL_A,
COL_B
ORDER BY MAX(LOG_CREATION_DATE) DESC
;
the records are in descending order as needed:
记录按需要按降序排列：
09-FEB-12 12.59.18.000000000 PM
09-FEB-12 12.41.42.000000000 PM
09-FEB-12 11.26.15.000000000 AM
09-FEB-12 11.26.00.000000000 AM
01-FEB-12 01.27.11.000000000 PM
01-FEB-12 01.25.18.000000000 PM
01-FEB-12 01.25.17.000000000 PM
01-FEB-12 01.24.36.000000000 PM
25-JAN-12 02.39.11.000000000 PM
25-JAN-12 02.32.05.000000000 PM
25-JAN-12 02.31.37.000000000 PM
25-JAN-12 02.31.34.000000000 PM
But when I change the query to format the same timestamp column, the order completely changes to:
但是当我更改查询以格式化相同的时间戳列时，顺序完全更改为：
02-09-2012 12:02:18
02-09-2012 12:02:42
02-09-2012 11:02:15
02-09-2012 11:02:00
02-01-2012 01:02:11
02-01-2012 01:02:18
02-01-2012 01:02:17
02-01-2012 01:02:36
01-25-2012 02:01:11
01-25-2012 02:01:05
01-25-2012 02:01:37
01-25-2012 02:01:34
the updated query is
更新的查询是
SELECT TO_CHAR(MAX(LOG_CREATION_DATE), 'MM-DD-YYYY HH:MM:SS'),COL_A, COL_B
FROM IMPORT_LOG
GROUP BY TO_CHAR(LOG_CREATION_DATE, 'MM-DD-YYYY'),
COL_A,
COL_B
ORDER BY MAX(LOG_CREATION_DATE) DESC
;
why is the output changed ?
为什么输出改变了？
回答by Clockwork-Muse
The ordering of your results hasn't changed; if you'll look a little closer at your results, you'll note that the date/second combination is unchanged. This is mostly because in your ORDER BYstatement, you're ordering by LOG_CREATION_DATEitself, and not a modified representation.
结果的顺序没有改变；如果您仔细查看结果，您会注意到日期/秒组合没有变化。这主要是因为在您的ORDER BY声明中，您LOG_CREATION_DATE自己订购，而不是修改后的表示。
The error you're seeing is because you're telling the formatting string that you want MONTHtwice - MMis month, and is notcontext dependent (use MIfor minutes - sorry, I don't use Oracle, so it took me a second to catch this).
您看到的错误是因为您告诉格式化字符串您想要MONTH两次 -MM是month，并且不依赖MI于上下文（使用几分钟 - 抱歉，我不使用 Oracle，所以我花了一秒钟抓住这个）。
Here's the corrected statement - is this generating what you wanted? Also, I don't recommend grouping by the character representation - use TRUNC()instead.
这是更正的声明 - 这是否产生了您想要的？另外，我不建议按字符表示分组 -TRUNC()而是使用。
SELECT TO_CHAR(MAX(log_creation_date), 'MM-DD-YYYY HH:MI:SS'), COL_A, COL_B
FROM Import_Log
GROUP BY TRUNC(log_creation_date), COL_A, COL_B
ORDER BY MAX(log_creation_date) DESC
回答by Alex Poole
Your date format mask is wrong, you have the time part as HH:MM:SSwhen you should have HH:MI:SS. Or even better possibly, HH24:MI:SS.
你的日期格式掩码是错误的，你有时间部分，HH:MM:SS你应该有HH:MI:SS. 或者甚至更好，HH24:MI:SS.
回答by Cade Roux
You are ordering by a string representation of a date in a 12H calendar. Your TO_CHAR is also incorrectly using MM twice.
您正在按 12 小时日历中日期的字符串表示形式进行排序。您的 TO_CHAR 也错误地使用了 MM 两次。
Try this instead:
试试这个：
SELECT TO_CHAR(MAX (LOG_CREATION_DATE) , 'MM-DD-YYYY HH:MI:SS') AS CUSTOM_LOG_CREATION_DATE,
USER,
LOCATION,
SUM(FILE_NAME) AS SUM_IMPORT
FROM MYTABLE
GROUP BY TO_CHAR(LOG_CREATION_DATE, 'MM-DD-YYYY'),
USER,
LOCATION
ORDER BY MAX (LOG_CREATION_DATE) DESC
