By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

select data after timer interval

P: 45
guys, plz help

i have table with datas like speed of vehicle, position e.t.c from morning 8 a.m. to 8 p.m.

I need to get these details, but after every 15 minutes
i.e. after selecting datas at 8 a.m. it shd select datas @ 8.15 a.m. then 8.30 a.m.

hw can i write a mysql query for this ? or a PHP approach is appreciated
Apr 4 '12 #1
Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,364
It depends on how you're storing the data. If it's as a date time field, you can grab the number of minutes, mod it by 15, and if it equal 0, return that row.
Apr 4 '12 #2

100+
P: 1,059
if your date has this format ([4 digit year]-[2 digit month]-[2 digit date] [2 digit hour]:[2 digit minute]:[2 digit seconds])

e.g
1982-01-03 13:12:30

in that case you can get do your query simple comparison way

such as (SELECT some_events from event_recorder where time between '1982-01-03 13:12:30' and '1982-01-03 13:27:30')

I am not sure whether it would be different in other format or not.

but in mysql you will get in above format.

and in php use the function mktime to do your time math
Apr 5 '12 #3

P: 45
Rabbit, thanks for the reply. cd u plz format ur reply into a query. It wd be a great help

johny10151981,date is the format 1982-01-03 13:12:30, but ur query returns the details between two time, but we need to get these details after every 15 minutes
i.e. after selecting datas at 8 a.m. query shd select datas @ 8.15 a.m. then 8.30 a.m.
Apr 9 '12 #4

100+
P: 1,059
??? I am confused. do you want to access database in an interval? in that case database has nothing to do.
Apr 9 '12 #5

P: 45
sorry for the confusion johny10151981. my db is like this
slno > 1
time > 09/04/2012 08:00:00
latitude > 8.56
longitude > 76.52
AC > 1

in the next row
slno >2
time > 09/04/2012 08:05:00
latitude > 10.56
longitude > 88.52
AC > 0

in the next row
slno >3
time > 09/04/2012 08:12:00
latitude > 10.56
longitude > 88.52
AC > 0

in the next row
slno >4
time > 09/04/2012 08:15:00
latitude > 12.56
longitude > 66.52
AC > 0

in the next row
slno >5
time > 09/04/2012 08:25:00
latitude > 10.56
longitude > 88.52
AC > 0

in the next row
slno >6
time > 09/04/2012 08:30:00
latitude > 10.56
longitude > 88.52
AC > 1

wat we need is to select row @ 09/04/2012 08:00:00, then 09/04/2012 08:15:00, then 09/04/2012 08:30:00 i.e. select row after 15 minutes from the previous row

i hope iam clear,

thanks in advance
Apr 9 '12 #6

Rabbit
Expert Mod 10K+
P: 12,364
Sorry, I don't post code unless the poster has shown they have attempted the solution themselves. Make an attempt and post the SQL code you ended up with and I will help you fix the errors in it. All you need is the MINUTE() function and the MOD operator.
Apr 9 '12 #7

P: 45
Rabbit, only query i know is
Expand|Select|Wrap|Line Numbers
  1. select * from table where timestamp between 2012-04-10 10:15:21 AND 2012-04-10 18:15:21
Apr 10 '12 #8

Rabbit
Expert Mod 10K+
P: 12,364
Well, you didn't use the function or the operator I mentioned in my post.
Apr 10 '12 #9

P: 45
rabbit, iam not sure what iam saying is correct. MINUTE() retrives the minute in a time i.e.
SELECT MINUTE('2009-05-18 10:15:21.000423'); returns 15, iam not sure hw to use it in my case.

plz help
Apr 10 '12 #10

Rabbit
Expert Mod 10K+
P: 12,364
You're not selecting it. You want to filter it. So it should be in your WHERE clause which will filter it.
Apr 10 '12 #11

100+
P: 1,059
ok, I am not sure about it but I believe it would work (I did small test on mysql server)

on your where condition:

SELECT * FROM table where time%(900) = 0;

it would work if minutes are if the second values are zero
Apr 11 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.