MySQL 如何将 UNIX TIME 转换为 SQL 日期时间格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21569556/
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 convert the UNIX TIME into SQL datetime format
提问by sam
select COUNT(DISTINCT devices) AS "Devices" from measure_tab where
measure_tab.time >= 1375243200 and
measure_tab.time < 1375315200;
The output of the above sql query gives a table with a column named "Devices" with number of devices. I want the time which is one of the attributes of measure_tab should also get displayed in another column of the output table with the UNIX TIME which is 1375243200 in this query converted into the SQL datetime format which is Thu Aug 1 00:00:00 UTC 2013.
上述 sql 查询的输出给出了一个表，其中包含一个名为“设备”的列，其中包含设备数量。我希望作为 measure_tab 属性之一的时间也应该显示在输出表的另一列中，在此查询中将 UNIX TIME 1375243200 转换为 SQL 日期时间格式，即Thu Aug 1 00:00:00 UTC 2013.
Can someone help me out?
有人可以帮我吗？
Thanks
谢谢
回答by Suresh Kamrushi
You can use function as below:
您可以使用以下功能：
select FROM_UNIXTIME(UNIX_TIMESTAMP(),'%a %b %d %H:%i:%s UTC %Y');
output will be:
输出将是：
'Wed Feb 05 05:36:16 UTC 2014'
In your query
在您的查询中
select COUNT(DISTINCT devices) AS "Devices",
FROM_UNIXTIME(measure_tab.time,'%a %b %d %H:%i:%s UTC %Y') as d from measure_tab where
measure_tab.time >= 1375243200 and
measure_tab.time < 1375315200;
For more info you can check documentation: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime
有关更多信息，您可以查看文档：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime
You can see sql fiddle:http://sqlfiddle.com/#!2/a2581/20357
回答by anubhava
In Mysql you can use from_unixtime()function to convert unix timestamp to Date:
在 Mysql 中，您可以使用from_unixtime()函数将 unix 时间戳转换为Date：
select COUNT(DISTINCT devices) AS "Devices" from measure_tab where
measure_tab.time >= from_unixtime(1375243200) and
measure_tab.time < from_unixtime(1375315200);
回答by Vignesh Kumar A
you could use FROM_UNIXTIMEinside DATE_FORMAT, but luckily, FROM_UNIXTIME also accepts a format string, so you could just use it by itself
您可以在DATE_FORMAT 中使用FROM_UNIXTIME，但幸运的是， FROM_UNIXTIME 也接受格式字符串，因此您可以单独使用它
Like this
像这样
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x')
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x')
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
回答by Lindsay Winkler
As detailed in the other answers, FROM_UNIXTIMEis the function you are looking for. Please be aware that this implicitly takes into account the local time zone setting on the machine running MySQL.
正如其他答案中详述的那样，FROM_UNIXTIME是您正在寻找的功能。请注意，这隐含地考虑了运行 MySQL 的机器上的本地时区设置。
There's lots of useful information here:
这里有很多有用的信息：
Should MySQL have its timezone set to UTC?
if you need to find out how to check/set the time zone.
如果您需要了解如何检查/设置时区。
