How to get MySQL get time difference between insert data and exits data

Vote count:

0

I am tring to get time difference between,

MySQL table has this rows:

room_id start_time              finish_time
13      2014-10-20 05:30:00     2014-10-20 06:30:00
13      2014-10-20 08:30:00     2014-10-20 18:30:00

If I try to insert following values from PHP:

Room id = 13
start time = 2014-10-20 06:45:00
finish time = 2014-10-20 07:30:00

I want to get time difference between:

New start time and db table finish time
New Finish time and db table start time

eg:

as above example should have to get:

Between New start and Finish time: 2014-10-20 06:30:00 (table row 1 finish) - 2014-10-20 06:45:00 Answer is: 15

I wrote this query:

              SELECT
                 TIMESTAMPDIFF(MINUTE,finish_time, 2014-10-20 06:45:00) as time_diff_finish_new_start
              FROM rooms
              WHERE room_id='13'
              AND finish_time < '2014-10-20 06:45:00'
              ORDER BY finish_time DESC
              LIMIT 1

Between New Finish and Start time: 2014-10-20 08:30:00 (table row 2 start) - 2014-10-20 07:30:00 Answer is: 60

I wrote this query:

              SELECT
                 TIMESTAMPDIFF(MINUTE,start_time, 2014-10-20 07:30:00) as time_diff_start_new_finish
              FROM rooms
              WHERE room_id='13'
              AND start_time > '2014-10-20 07:30:00'
              ORDER BY start_time
              LIMIT 1

But these queries have errors:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '07:30:00) as time_diff_start_new_finish FROM rosters ' at line 2 (SQL: SELECT TIMESTAMPDIFF(MINUTE,start_time, 2014-10-20 07:30:00) as time_diff_start_new_finish FROM rosters WHERE guard_id='13' AND start_time > '2014-10-20 07:30:00' ORDER BY start_time LIMIT 1 )

Please show me what is the error? and please advice me any suggestion about above queries, thank you

asked 45 secs ago

user3099298

159

How to get MySQL get time difference between insert data and exits data