Question 181109 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to find different between two times with sql

I have a table contain reference time and a user check-in time. Both data is different in type.

Sample data

  • dtime = 2023-06-02 08:23:21
  • work_time = 08:00-18:00

And my code is…

SELECT substring(dtime,-8,5) AS chkin, 
SUBSTRING(work_time, 1, 5) AS wt1, 
TIMESTAMPDIFF(MINUTE, substring(dtime,-8,5), SUBSTRING(work_time, 1, 5)) AS min_diff 
FROM ta_db 
WHERE id = 13181;

As a result…

  • chkin = 08:23
  • wt1 = 08:00

Now, I want to know how many minutes different from chkin and wt with TIMESTAMPDIFF.
So I did this…

TIMESTAMPDIFF(MINUTE, substring(dtime,-8,5), SUBSTRING(work_time, 1, 5)) AS min_diff

But it returns NULL. Please be advised.

Tags:

2

Answers


  1. 0

    my solution is to cast using time()

    SELECT SUBSTRING(dtime,-8,5) AS chkin, 
SUBSTRING(work_time, 1, 5) AS wt1, 
abs(timediff(time(SUBSTRING(dtime,-8,5)),time(SUBSTRING(work_time,1, 
5)))) AS min_diff 
FROM ta_db 
WHERE id = 13181;
    Login or Signup to reply.
  2. 0

    Try this:

    SET @dtime = '2023-06-02 08:28:21';
SET @work_time = '08:00-18:00';

SELECT substring(@dtime,-8,5) AS chkin, 
SUBSTRING(@work_time, 1, 5) AS wt1, 
TIMESTAMPDIFF(MINUTE, @dtime, CONCAT(SUBSTRING(@dtime, 1,10), ' ', SUBSTRING(@work_time, 1, 5))) AS min_diff
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search