如何提取 MySQL FROM_UNIXTIME() 结果的小时数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15329355/
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
How to extract hour for MySQL FROM_UNIXTIME() results?
提问by Gill Bates
I have a column with time of measurments in timestamps in INT, and I need group data by interval of a hour. I already discovered MySQL function FROM_UNIXTIME(), but is there internal instrument in MySQL to extract hour from results of this function?
我有一个包含 INT 时间戳中测量时间的列，我需要按一小时的间隔对数据进行分组。我已经发现了 MySQL 函数 FROM_UNIXTIME()，但是 MySQL 中是否有内部工具可以从该函数的结果中提取小时？
回答by Explosion Pills
I think you mean FROM_UNIXTIME
我想你的意思 FROM_UNIXTIME
GROUP BY
DATE_FORMAT(FROM_UNIXTIME(time_column), '%Y %m %d %H'))
回答by Aiias
SELECT *
FROM table
GROUP BY DATE_FORMAT(FROM_UNIXTIME(time_col_name), '%H')
Check out MySQL's Date documentation.
查看 MySQL 的日期文档。
Edit:
编辑：
Use DATE_FORMATinstead to reliably determine the format of the hour returned. See DATE_FORMAT documentation. Note: The format %H will return a value of the hour in the format 00, 01, ... 23.
DATE_FORMAT改为使用来可靠地确定返回的小时的格式。请参阅DATE_FORMAT 文档。注意：格式 %H 将返回格式为 00, 01, ... 23 的小时值。
回答by Kermit
Something like:
就像是：
SELECT HOUR(FROM_UNIXTIME(unixCol))
FROM tbl
GROUP BY HOUR(FROM_UNIXTIME(unixCol))
HOURwill extract the hour after the UNIX timestamp is coverted to a date.
HOUR将在 UNIX 时间戳转换为日期后提取小时。
回答by Milos Miskone Sretin
Try putting that DB data in variable as date and then echo it.
尝试将该数据库数据作为日期放入变量中，然后回显它。
$timestamp = strtotime($db_data);
echo date("m-d-Y", $timestamp);
回答by aydow
I know this is a few years after the initial question but if you're dealing with larger datasets this may take longer to execute due to calling 2 functions for every row in the query set.
我知道这是在最初的问题之后几年，但如果您正在处理更大的数据集，由于为查询集中的每一行调用 2 个函数，这可能需要更长的时间来执行。
I managed to shave a couple seconds off my time by using a bit of modular arithmetic
通过使用一些模块化算法，我设法缩短了几秒钟的时间
GROUP BY FLOOR(time_col/(60*60) MOD 24)
Hope this helps :)
希望这可以帮助 ：）
