0

I have a table which contains 2 columns date_picked_begin and date_picked and there will be multiple rows which contain a date in each.

How do I use TIMESTAMPDIFF to get the difference in seconds between the dates in all rows of data.

I have tried the following query but it only returns the difference in seconds from the first row. (It's the alias total_picking_waiting_time which is trying to get the total time).

SELECT COUNT(pi.pi_id) AS num_picking_waiting_time,
AVG(TIMESTAMPDIFF(SECOND, pi.date_picked_begin, pi.date_picked)) AS average_picking_waiting_time,
TIMESTAMPDIFF(SECOND, pi.date_picked_begin, pi.date_picked) AS total_picking_waiting_time
FROM (SELECT pi_id, type, date_picked_begin, date_picked, picker FROM pickwave_items) as pi
WHERE pi.type = 'Picking (Waiting Time)' AND DATE(pi.date_picked) = '2017-02-25' AND pi.picker = 1

Also, I have been assuming that this query was getting the average time between the 2 dates in all rows. Is this the case? With it not returning the total time as I expected I am now thinking that it isn't.

Thank you

Dan
  • 103
  • 1
  • 13

1 Answers1

1

First, the subquery in the FROM is unnecessary. It adds verbosity to the query and actually slows it down (because MySQL materializes such subqueries).

Then, to get the total picking time, use SUM():

SELECT COUNT(pi.pi_id) AS num_picking_waiting_time,
       AVG(TIMESTAMPDIFF(SECOND, pi.date_picked_begin, pi.date_picked)) AS average_picking_waiting_time,
       SUM(TIMESTAMPDIFF(SECOND, pi.date_picked_begin, pi.date_picked)) AS total_picking_waiting_time
FROM pickwave_items as pi
WHERE pi.type = 'Picking (Waiting Time)' AND
      DATE(pi.date_picked) = '2017-02-25' AND
      pi.picker = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Ah of course SUM. Thank you very much! I will accept the answer when it allows me to. Thank you also for letting me know the sub query is unnecessary, I could have sworn it didn't work correctly without it when I first tried it months and months ago but is returning the same result now so obviously it was :) Thanks again – Dan Feb 25 '17 at 13:28