Mysql query efficient date calculation

Question

I have a table as:

ID           RECORD_DATE              
1            2012-12-15 00:00:00      
2            2012-12-16 00:00:00      
3            2012-12-17 00:00:00      
4            2012-12-17 16:00:00

Now I need to calculate the time between the adjacent date, so it would look like:

start                  end                  difference
2012-12-15 00:00:00    2012-12-15 23:59:59  23:59:59
2012-12-16 00:00:00    2012-12-16 23:59:59  23:59:59
2012-12-17 00:00:00    2012-12-17 16:00:00  16:00:00

Is there any efficient way to do this kind of calculation on the database side?

All help is appreciated.

Can you explain the logic that deducts 1 second from the first two results, but not the third? Also, we like to see proper DDLs as well as the best efforts of the author to date. — Strawberry
– Strawberry, Commented May 22, 2014 at 12:27

thebignoob · Accepted Answer · 2014-05-22 12:31:57Z

2

I didn't understand what you said clearly but try this

SELECT t1.RECORD_DATE AS start,
       t2.RECORD_DATE AS end,
       TIMEDIFF(t2.RECORD_DATE,t1.RECORD_DATE) AS difference
FROM TABLE AS t1
JOIN TABLE AS t2 ON t1.id = (t2.id + 1);

answered May 22, 2014 at 12:31

thebignoob

4715 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Strawberry Over a year ago

I'm not convinced that the assumption of incremental ids is reasonable (although I accept that they are in the example provided above)

thebignoob Over a year ago

Even I am not happy with this query, but I wrote it assuming incremental id, although we can sort it by few changes in the query, Anyways thanks for the taking interest. :)

Adnan Over a year ago

@RaghavPotluri I have just tested your query, and it provides the fast execution. Unfortunately I have to deal with the possibility of not having the incremental sequence. Thank you

thebignoob Over a year ago

@Adnan yes, I was writing with your example in mind.I'll try for a better answer.Thanks :)

Abhik Chakraborty · Accepted Answer · 2014-05-22 13:12:04Z

0

Here is a way to deal with adjacent rows when its not sequencial i.e. after id = 1 you may have 4 or 5 etc.

select 
t1.RECORD_DATE as `start`,
t2.RECORD_DATE as `end`,
TIMEDIFF(t2.RECORD_DATE, t1.RECORD_DATE) as difference
from table_name t1
join table_name t2 on t2.ID = (
                  select MIN(x.ID) 
                  from table_name x 
                  where x.id > t1.id
)

Also I have noticed you are taking the end date from 2012-12-16 00:00:00 to 2012-12-15 23:59:59 which is not as in the table so you may need to use date_sub() to deduct 1 second from the end date.

answered May 22, 2014 at 13:12

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Comments

Strawberry · Accepted Answer · 2014-05-22 13:16:01Z

0

SELECT x.*
     , MIN(y.record_date - INTERVAL 1 SECOND) end
     , TIMEDIFF(MIN(y.record_date) - INTERVAL 1 SECOND,x.record_date) diff 
  FROM my_table x 
  JOIN my_table y 
    ON y.record_date > x.record_date 
 GROUP 
    BY x.id;

If it matters, I guess some trick with an OUTER JOIN could deal with the missing second

answered May 22, 2014 at 13:16

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Comments

zessx · Accepted Answer · 2014-05-22 13:27:08Z

0

Use this kind or query, using TIME_DIFF() :

SELECT
    start,
    end,
    TIMEDIFF(end, start) AS difference
FROM
(
    SELECT 
        t_start.record_date AS start,
        (
            SELECT 
                t_end.record_date
            FROM records AS t_end
            WHERE t_end.id > t_start.id
                    ORDER BY t_end.id
            LIMIT 1
        ) AS end
    FROM records AS t_start
) AS t_boundaries

edited May 22, 2014 at 13:27

answered May 22, 2014 at 12:43

zessx

68.9k29 gold badges139 silver badges166 bronze badges

3 Comments

Strawberry Over a year ago

...assuming id is incremental (no gaps) - and that dates and ids never fall out of sequence

zessx Over a year ago

Just changed for t_end.id > t_start.id, to prevent missing entries to break everything.

Strawberry Over a year ago

Hm, now you need an ORDER BY (or a MIN... GROUP BY). Can we just use a simpler query?

Collectives™ on Stack Overflow

Mysql query efficient date calculation

4 Answers 4

4 Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related