select data after timer interval

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • charles07
    New Member
    • Dec 2011
    • 45

    select data after timer interval

    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
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    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.

    Comment

    • johny10151981
      Top Contributor
      • Jan 2010
      • 1059

      #3
      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

      Comment

      • charles07
        New Member
        • Dec 2011
        • 45

        #4
        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.

        Comment

        • johny10151981
          Top Contributor
          • Jan 2010
          • 1059

          #5
          ??? I am confused. do you want to access database in an interval? in that case database has nothing to do.

          Comment

          • charles07
            New Member
            • Dec 2011
            • 45

            #6
            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

            Comment

            • Rabbit
              Recognized Expert MVP
              • Jan 2007
              • 12517

              #7
              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.

              Comment

              • charles07
                New Member
                • Dec 2011
                • 45

                #8
                Rabbit, only query i know is
                Code:
                select * from table where timestamp between 2012-04-10 10:15:21 AND 2012-04-10 18:15:21

                Comment

                • Rabbit
                  Recognized Expert MVP
                  • Jan 2007
                  • 12517

                  #9
                  Well, you didn't use the function or the operator I mentioned in my post.

                  Comment

                  • charles07
                    New Member
                    • Dec 2011
                    • 45

                    #10
                    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

                    Comment

                    • Rabbit
                      Recognized Expert MVP
                      • Jan 2007
                      • 12517

                      #11
                      You're not selecting it. You want to filter it. So it should be in your WHERE clause which will filter it.

                      Comment

                      • johny10151981
                        Top Contributor
                        • Jan 2010
                        • 1059

                        #12
                        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

                        Comment

                        Working...