mysql使用FROM_UNIXTIME将时间戳按日期group by

今天药忘吃喽~ 2023-06-17 13:58 2阅读 0赞

### 1.功能 ###

将Unix时间戳转换成指定或默认的日期字符串[官宣][Link 1]。  
它具有的特点

1.  使用整型的高效存储和查询时间（时间戳）
2.  使用此函数兼容日期的时间处理（可读性）

### 2. 语法 ###

FROM_UNIXTIME(unix_timestamp[,format])
    即：省略格式化字符串和指定格式化字符串，两种方式
    FROM_UNIXTIME(unix_timestamp)
    FROM_UNIXTIME(unix_timestamp,format)

铁路图如下：  
![在这里插入图片描述][20191129132837508.png]

### 3.使用 ###

比如按照小时汇总数据

select FROM_UNIXTIME(ReportTime, '%H'), sum(Value) from reporter_data group by FROM_UNIXTIME(ReportTime, '%H');

+---------------------------------+------------+
    | FROM_UNIXTIME(ReportTime, '%H') | sum(Value) |
    +---------------------------------+------------+
    | 00                              |          0 |
    | 01                              |          2 |
    | 02                              |          4 |
    | 03                              |          6 |
    | 04                              |          8 |
    | 05                              |         10 |
    | 06                              |         12 |
    | 07                              |         14 |
    +---------------------------------+------------+
    8 rows in set (0.00 sec)

常用的格式控制比如：’%Y-%m-%d %H:%i:%s’。然而这些并不容易记住，一种捷径就是结合使用[get\_format][get_format],它可以通过显示DATE, DATETIME, TIME和指定的标准获得所需的日期时间格式控制字符串，比如

MySQL [coding]> select FROM_UNIXTIME(ReportTime, GET_FORMAT(DATE, 'ISO')), sum(Value) from device_data group by FROM_UNIXTIME(ReportTime, GET_FORMAT(DATE, 'ISO'));
    +----------------------------------------------------+------------+
    | FROM_UNIXTIME(ReportTime, GET_FORMAT(DATE, 'ISO')) | sum(Value) |
    +----------------------------------------------------+------------+
    | 2019-11-29                                         |         56 |
    +----------------------------------------------------+------------+

更是格式控制字符串如下：

<table> 
 <thead> 
  <tr> 
   <th>Specifier</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%a</td> 
   <td>Abbreviated weekday name (Sun…Sat)</td> 
  </tr> 
  <tr> 
   <td>%b</td> 
   <td>Abbreviated month name (Jan…Dec)</td> 
  </tr> 
  <tr> 
   <td>%c</td> 
   <td>Month, numeric (0…12)</td> 
  </tr> 
  <tr> 
   <td>%D</td> 
   <td>Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)</td> 
  </tr> 
  <tr> 
   <td>%d</td> 
   <td>Day of the month, numeric (00…31)</td> 
  </tr> 
  <tr> 
   <td>%e</td> 
   <td>Day of the month, numeric (0…31)</td> 
  </tr> 
  <tr> 
   <td>%f</td> 
   <td>Microseconds (000000…999999)</td> 
  </tr> 
  <tr> 
   <td>%H</td> 
   <td>Hour (00…23)</td> 
  </tr> 
  <tr> 
   <td>%h</td> 
   <td>Hour (01…12)</td> 
  </tr> 
  <tr> 
   <td>%I</td> 
   <td>Hour (01…12)</td> 
  </tr> 
  <tr> 
   <td>%i</td> 
   <td>Minutes, numeric (00…59)</td> 
  </tr> 
  <tr> 
   <td>%j</td> 
   <td>Day of year (001…366)</td> 
  </tr> 
  <tr> 
   <td>%k</td> 
   <td>Hour (0…23)</td> 
  </tr> 
  <tr> 
   <td>%l</td> 
   <td>Hour (1…12)</td> 
  </tr> 
  <tr> 
   <td>%M</td> 
   <td>Month name (January…December)</td> 
  </tr> 
  <tr> 
   <td>%m</td> 
   <td>Month, numeric (00…12)</td> 
  </tr> 
  <tr> 
   <td>%p</td> 
   <td>AM or PM</td> 
  </tr> 
  <tr> 
   <td>%r</td> 
   <td>Time, 12-hour (hh:mm:ss followed by AM or PM)</td> 
  </tr> 
  <tr> 
   <td>%S</td> 
   <td>Seconds (00…59)</td> 
  </tr> 
  <tr> 
   <td>%s</td> 
   <td>Seconds (00…59)</td> 
  </tr> 
  <tr> 
   <td>%T</td> 
   <td>Time, 24-hour (hh:mm:ss)</td> 
  </tr> 
  <tr> 
   <td>%U</td> 
   <td>Week (00…53), where Sunday is the first day of the week; WEEK() mode 0</td> 
  </tr> 
  <tr> 
   <td>%u</td> 
   <td>Week (00…53), where Monday is the first day of the week; WEEK() mode 1</td> 
  </tr> 
  <tr> 
   <td>%V</td> 
   <td>Week (01…53), where Sunday is the first day of the week; WEEK() mode 2; used with</td> 
  </tr> 
  <tr> 
   <td>%v</td> 
   <td>Week (01…53), where Monday is the first day of the week; WEEK() mode 3; used with</td> 
  </tr> 
  <tr> 
   <td>%W</td> 
   <td>Weekday name (Sunday…Saturday)</td> 
  </tr> 
  <tr> 
   <td>%w</td> 
   <td>Day of the week (0=Sunday…6=Saturday)</td> 
  </tr> 
  <tr> 
   <td>%X</td> 
   <td>Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V</td> 
  </tr> 
  <tr> 
   <td>%x</td> 
   <td>Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v</td> 
  </tr> 
  <tr> 
   <td>%Y</td> 
   <td>Year, numeric, four digits</td> 
  </tr> 
  <tr> 
   <td>%y</td> 
   <td>Year, numeric (two digits)</td> 
  </tr> 
  <tr> 
   <td>%%</td> 
   <td>A literal % character</td> 
  </tr> 
  <tr> 
   <td>%x</td> 
   <td>x, for any “x” not listed above</td> 
  </tr> 
 </tbody> 
</table>

[Link 1]: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime
[20191129132837508.png]: https://img-blog.csdnimg.cn/20191129132837508.png
[get_format]: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_get-format