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

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