0

EDIT :Different example : i have a car i check every minute the cars speed. Now i want to know for how many hours the car was driving at a specific speed. i have timestamp and x = speed /*******************************************************************************/

I have a table with Timestamps and a value X. Is it possible to get the time in hours for a specific X? And if possible for all data or a specific year or month. The timestamp is every min, but there can be gaps too.

time                  x
1.01.16 20:59:22     50 
1.01.16 21:00:22     40
1.01.16 21:01:22     40
1.01.16 21:02:25     40
1.01.16 21:04:25     40
1.01.16 21:06:25     40
   .                  .

output example

hours             x
   X ?            50
   X ?            40

I have a timestamp in a period of 1-2 min, sometimes there is no data for days or months. I need to know how many hours there was x.

EDIT:

For better unterstanding. I have timestamps and a value X. If i had 60 Timestamps in 1 hour with value X i would know that X was for 1 hour.But i dont have 60 timestamps i have sometimes only 30 or 2 or none. how can i do this with gaps? 

  time                  x

 1.01.16 21:00:00     40
 1.01.16 21:01:00     40
 1.01.16 21:02:00     40
 1.01.16 21:03:00     40
 1.01.16 21:04:00     40
 1.01.16 21:05:00     40
     ....
 1.01.16 22::00       40

   OUTPUT:
 hours            x
  1              40

EDIT : ^^ Different example : i have a car i check every min the cars speed. Now i want to know for how many hours the car was driving at a specific speed.

Khan
  • 1,418
  • 1
  • 25
  • 49
  • How do you get `200` from that timestamp? Should that be `20`? – Barmar Aug 12 '16 at 09:35
  • it should be an example if i had more timestamps with value 50. – Khan Aug 12 '16 at 09:37
  • Please show more data and explain how the result comes from the data. – Barmar Aug 12 '16 at 09:38
  • do you understand? Sometimes there are gaps, If i have only a timestamp for value 50 in 1 hour and the next timestamp comes 2 hours later with same value 50, how can i calculate this as 2 min? or is it right to calculate it this way? :( Wouldnt be a sum wrong because of gaps? – Khan Aug 12 '16 at 09:55
  • I still don't understand what you're trying to calculate. The difference in time between two timestamps? Where does `200` come from? – Barmar Aug 12 '16 at 10:01
  • it is an example.... i delete it – Khan Aug 12 '16 at 10:55
  • Your question still is not understandable. What is `hours` supposed to be? How are you combining the timestamps to get it? – Barmar Aug 12 '16 at 11:03
  • Perhaps if you explained what these values represent and the meaning you are trying to derive and provided more complex examples with real values then we might be able to guess what you're asking. Its far from clear. – symcbean Aug 12 '16 at 11:33

1 Answers1

0

If I understand your question right you need to extract hours from timestamp try 'time_format()' funciton as defined in the below tutorial.

TIME_FORMAT( time, format_mask )

  • %f Microseconds (000000 to 999999)
  • %H Hour (00 to 23 generally, but can be higher)
  • %h Hour (00 to 12) %I Hour (00 to 12)
  • %i Minutes (00 to 59) %p AM or PM
  • %r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
  • %S Seconds (00 to 59)
  • %s Seconds (00 to 59)
  • %T Time in 24 hour format (hh:mm:ss)

SELECT TIME_FORMAT('17:42:03.000001', '%h');

http://www.techonthenet.com/mysql/functions/time_format.php

Ahmed Khan
  • 518
  • 4
  • 12