如何将UNIX TIME转换为SQL datetime格式 [英] How to convert the UNIX TIME into SQL datetime format

问题描述

select COUNT(DISTINCT devices) AS "Devices" from measure_tab where 
  measure_tab.time >= 1375243200 and 
  measure_tab.time < 1375315200;

上述sql查询的输出给出一个名为Devices的列，其数量为设备。我想要的时间是measure_tab的属性之一也应该显示在输出表的另一列中，这个查询中的UNIX TIME是1375243200转换为SQL日期时间格式，这是 Thu Aug 1 00:00:00 UTC 2013 。

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.

有人可以帮我吗？

谢谢

推荐答案

您可以使用如下功能：

select FROM_UNIXTIME(UNIX_TIMESTAMP(),'%a %b %d %H:%i:%s UTC %Y');

输出将为：

'Wed Feb 05 05:36:16 UTC 2014'

在你的查询

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;

有关更多信息，您可以查看文档： http://dev.mysql.com/doc/refman/5.5/en/date- and-time-functions.html＃function_from-unixtime

For more info you can check documentation: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

你可以看到sql fiddle： http://sqlfiddle.com/#!2/a2581/20357

You can see sql fiddle:http://sqlfiddle.com/#!2/a2581/20357

这篇关于如何将UNIX TIME转换为SQL datetime格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！