CODEWITHSUNDEEP
mysqlsqldatediff
BlackM
BlackM

Reputation: 4065

MySQL query with DATEDIFF

I am trying to build a query that will load records from and to specific date comparing 2 fields - the start_time and the end_date.

SELECT start_time
,end_time
,DATEDIFF(end_time, start_time) AS DiffDate
FROM my_tbl
WHERE start_time >= '2015-04-27 00:00:00'
AND end_time <= '2015-04-28 00:00:00'
AND end_time >= '2015-04-27 00:00:00'
AND DiffDate < 100
LIMIT 1000;

Unfortunately the DiffDate returns always 0. The ideal scenario was to calculate the difference between start_time and end_time when inserting the end_time but the I cant make any changes on the database. What am I doing wrong here? Even if the DiffDate was working will it considered as a good solution?

Upvotes: 1

Views: 1753

Answers (4)

Kishor Pawar
Kishor Pawar

Reputation: 3526

SELECT start_time,end_time,DATEDIFF(end_time, start_time) AS DiffDate FROM my_tbl WHERE start_time >= '2015-04-27 00:00:00' AND end_time between '2015-04-27 00:00:00' AND '2015-04-28 00:00:00' AND for date diff < 100 LIMIT 1000;

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

From the condition in the where clause it appears that you are trying to get data for the same date, however using the datediff for the same day always would result 0 

mysql> select datediff('2015-04-27 12:00:00','2015-04-27 00:00:00') as diff ;
+------+
| diff |
+------+
|    0 |
+------+

1 row in set (0.03 sec)

You may need other means of calculation perhaps using the timestampdiff

mysql> select timestampdiff(minute ,'2015-04-27 00:00:00','2015-04-27 12:00:00') as diff ;
+------+
| diff |
+------+
|  720 |
+------+
1 row in set (0.00 sec)

Also you are using alias in the where clause which is not allowed you have to change that to having clause

SELECT start_time
,end_time
,timestampdiff(minute,start_time,end_time) AS DiffDate
FROM my_tbl
WHERE start_time >= '2015-04-27 00:00:00'
AND end_time <= '2015-04-28 00:00:00'
AND end_time >= '2015-04-27 00:00:00'
having DiffDate < 100
LIMIT 1000;

Upvotes: 2

Smila
Smila

Reputation: 1140

Start_time and end_time are datetime column. So use TimeDIFF..

SELECT start_time, end_time, TIMEDIFF(end_time, start_time) AS DiffDate
FROM my_tbl
WHERE start_time >= '2015-04-27 00:00:00'
AND end_time <= '2015-04-28 00:00:00'
AND end_time >= '2015-04-27 00:00:00'
AND DiffDate < 100
LIMIT 1000;

Upvotes: 0

BlackM
BlackM

Reputation: 4065

Coming from MS SQL I was using DATEDIFF but the solution is:

SELECT start_time
,end_time
,TIMESTAMPDIFF(SECOND,start_time,end_time) AS DiffDate
FROM my_tbl
WHERE start_time >= '2015-04-27 00:00:00'
AND end_time <= '2015-04-28 00:00:00'
AND end_time >= '2015-04-27 00:00:00'
AND DiffDate < 100
LIMIT 1000;

I would like to know if there is a better solution from that.

Upvotes: 0

Related Questions