1

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?

Improve this question
2
  • What do you want the date diff to be in? hours mins days weeks etc? Commented Apr 29, 2015 at 7:50
  • have you checked the BETWEEN operator? Commented Apr 29, 2015 at 7:51

4 Answers 4

Reset to default
2

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for the tip with the having. Is this query considered as good practise?
For small data set there would not be any performance issue however when data grows you may add indexed on start_time and end_time also limit without order by has no meaning, so you may think of ordering the data while doing limit.
What exactly do mean "add indexed on start and end time"? I am not able to make any changes on the database. Only to retrieve data
I see well indexes make data retrieval faster. If you do not have that privilege to do I am sure the DB admin will have and he/she would take care of it.
0

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.

Improve this answer

Comments

0 
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;
Improve this answer

Comments

0

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;

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.