CODEWITHSUNDEEP
phpmysql
aachraf
aachraf

Reputation: 119

How to calculate time difference in MySQL?

I have a table with column 'start', which contain the start date of some tasks

+----------+---------------------+------------+
| name     | start               | end        |
+----------+---------------------+------------+
| p1       | 2014-12-08 15:56:03 |            | 
| p2       | 2014-12-08 15:56:03 |            | 
| p3       | 2014-12-08 15:56:03 |            | 
| ...      | ...                 | ...        | 
+----------+---------------------+------------+

I need to select, from this table all the tasks started for more than 48h I tried 

start between DATE_SUB(NOW(),INTERVAL 2 DAY) and  NOW()

I googled and tried several ways to compare date but unfortunately didn't get the result as expected.

Upvotes: 0

Views: 71

Answers (3)

EternalHour
EternalHour

Reputation: 8661

This seems to be closest to what you are trying to do.

SELECT * 
FROM table 
WHERE start BETWEEN (CURRENT_DATE() - INTERVAL 2 DAY) AND CURRENT_DATE();

Upvotes: 0

Tibor B.
Tibor B.

Reputation: 1690

This is the correct MySQL statement for the results you'd like to achieve. Simply replace the word table with your MySQL table's name.

SELECT *
FROM table
WHERE start BETWEEN DATE_SUB(NOW(), INTERVAL 2 DAY) AND NOW()

Edit: I've just noticed, that you are actually looking for every date, which is older than 2 days, rather than "within 2 days". So the MySQL statement for that is:

SELECT *
FROM table
WHERE start < DATE_SUB(NOW(), INTERVAL 2 DAY)

Upvotes: 1

skrilled
skrilled

Reputation: 5371

I'm not fluent on the ways of doing this purely with mysql, but you could do the dates with PHP (since you tagged this as using PHP too):

$startDate = date("Y-m-d H:i:s",strtotime("-48 hours"));
$nowDate = date("Y-m-d H:i:s");
$sql = "select * from table where start >= '".$startDate."' AND start <= '".$nowDate."'";
// execute your sql statement here and fetch results

Upvotes: 0

Related Questions