mysql - 从数据库获取每分钟的时间戳

Question

这是我的表结构:

+------------+-----------+------+-----+---------+-------+
| Field      | Type      | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+-------+
| uid        | char(255) | NO   | MUL | NULL    |       |
| cid        | char(255) | NO   | MUL | NULL    |       |
| ip_address | char(15)  | NO   |     | NULL    |       |
| user_agent | char(255) | YES  |     | NULL    |       |
| open_date  | datetime  | NO   | MUL | NULL    |       |
| referrer   | char(255) | YES  |     | NULL    |       |
| environ    | text      | YES  |     | NULL    |       |
| country    | char(255) | NO   | MUL | NULL    |       |
+------------+-----------+------+-----+---------+-------+

我想获取一个范围内每分钟的时间戳。

select DATE_ADD(open_date,interval 1 minute)
as m from open_track.camp_open  where  open_date between
"2009-05-13 00:00:00"   and "2009-05-13 23:59:59"  limit 10;

输出

+---------------------+
| m                   |
+---------------------+
| 2009-05-13 00:01:01 |
| 2009-05-13 00:01:02 |
| 2009-05-13 00:01:03 |
| 2009-05-13 00:01:03 |
| 2009-05-13 00:01:04 |
| 2009-05-13 00:01:05 |
| 2009-05-13 00:01:06 |
| 2009-05-13 00:01:08 |
| 2009-05-13 00:01:08 |
| 2009-05-13 00:01:09 |
+---------------------+

请给我建议。

Answer 1

i want a count of opens hit per minute

  SELECT COUNT(*),
         DATE_FORMAT(open_date, '%Y-%m-%d %H:%i')
    FROM camp_open
GROUP BY DATE_FORMAT(open_date, '%Y-%m-%d %H:%i')