Obtaining a timestamp every minute from a database
- by soumen
This is my table structure:
+------------+-----------+------+-----+---------+-------+
| 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 | |
+------------+-----------+------+-----+---------+-------+
I want to get a timestamp every minute within a range.
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;
outputs
+---------------------+
| 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 |
+---------------------+
Please advise me.
