Obtaining a timestamp every minute from a database
Posted
by
soumen
on Stack Overflow
See other posts from Stack Overflow
or by soumen
Published on 2011-02-10T13:38:48Z
Indexed on
2011/02/11
7:25 UTC
Read the original article
Hit count: 107
Filed under:
mysql
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.
© Stack Overflow or respective owner
