I wonder how to output the average data of each second. My data set is in millisecond and I can out them in millisecond by

SELECT * FROM some_table 
where RealTime > '2019-09-10 23:00:00' LIMIT 200;

Below is the Data Format

AcX       RealTime
-15836   2019-09-05 15:02:37.502
-16666   2019-09-05 15:02:37.508
-17645   2019-09-05 15:02:37.512

But how can I get the average data in each second? Thx

  • Use date_format to round to second only and then group by on it – Madhur Bhaiya Sep 10 '19 at 15:41
SELECT  LEFT(RealTime, 19) AS to_the_second,
        ROUND(AVG(AcX))  AS Average_AcX
    FROM some_table
    GROUP BY 1;


  • Often it is practical to treat a datetime (or timestamp) as a string, as I am doing with the LEFT.
  • You may want to add the number of decimal places to the ROUND() function call.
  • The 1 in GROUP BY 1 is shorthand for 1st column (the LEFT...).
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.