1.功能
将Unix时间戳转换成指定或默认的日期字符串官宣。
它具有的特点
- 使用整型的高效存储和查询时间(时间戳)
- 使用此函数兼容日期的时间处理(可读性)
2. 语法
FROM_UNIXTIME(unix_timestamp[,format])
即:省略格式化字符串和指定格式化字符串,两种方式
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)
铁路图如下:
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,它可以通过显示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 |
+----------------------------------------------------+------------+
更是格式控制字符串如下:
Specifier | Description |
---|---|
%a | Abbreviated weekday name (Sun…Sat) |
%b | Abbreviated month name (Jan…Dec) |
%c | Month, numeric (0…12) |
%D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
%d | Day of the month, numeric (00…31) |
%e | Day of the month, numeric (0…31) |
%f | Microseconds (000000…999999) |
%H | Hour (00…23) |
%h | Hour (01…12) |
%I | Hour (01…12) |
%i | Minutes, numeric (00…59) |
%j | Day of year (001…366) |
%k | Hour (0…23) |
%l | Hour (1…12) |
%M | Month name (January…December) |
%m | Month, numeric (00…12) |
%p | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S | Seconds (00…59) |
%s | Seconds (00…59) |
%T | Time, 24-hour (hh:mm:ss) |
%U | Week (00…53), where Sunday is the first day of the week; WEEK() mode 0 |
%u | Week (00…53), where Monday is the first day of the week; WEEK() mode 1 |
%V | Week (01…53), where Sunday is the first day of the week; WEEK() mode 2; used with |
%v | Week (01…53), where Monday is the first day of the week; WEEK() mode 3; used with |
%W | Weekday name (Sunday…Saturday) |
%w | Day of the week (0=Sunday…6=Saturday) |
%X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y | Year, numeric, four digits |
%y | Year, numeric (two digits) |
%% | A literal % character |
%x | x, for any “x” not listed above |