MYSQL Query timestamp comparison

Question

Given a table with a column that contains a timestamp, I would like to count the number of instances where the TIMEDIFF(now(), timeStamp) is greater than some value. The issue I ran into is implementing this all into a statement. My efforts:

SELECT COUNT*()
FROM sometable
WHERE (SELECT TIMEDIFF(now(),column_from_sometable) > 10

I'm just not quite sure how to use the TIMEDIFF in the where statement to use it for a comparison. (Apparently you must use SELECT to call the method, and then I'm just not sure how to access the column from sometablein that statement).

Is COUNT*() a typo? Did you mean COUNT(*)?

Marcus Adams
– Marcus Adams

2014-07-10 19:33:18 +00:00
Commented Jul 10, 2014 at 19:33 — Marcus Adams
– Marcus Adams, Commented Jul 10, 2014 at 19:33

Rahul · Accepted Answer · 2014-07-10 19:26:58Z

2

You don't need a sub select provided the column column_from_sometable belong to same table sometable. You can dimply include in WHERE condition like

SELECT COUNT(*)
FROM sometable
WHERE TIMEDIFF(now(),column_from_sometable) > 10

You can as well simplify this by adding the condition in your aggregate function like

SELECT SUM(CASE WHEN TIMEDIFF(now(),column_from_sometable) > 10 THEN 1 ELSE 0 END)
FROM sometable

edited Jul 10, 2014 at 19:26

answered Jul 10, 2014 at 19:21

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marcus Adams Over a year ago

I think the first is simpler. I think the key is: In order to output a result set, you need a SELECT somewhere. The examples for TIMEDIFF() use a SELECT just to get a simple result set without using a table. Within a query, you can use the function without putting SELECT in front of it.

Rahul Over a year ago

@MarcusAdams, that correct but I have seen many people (Here in SO) prefer the 2nd option rather :)

VMai Over a year ago

@Rahul If there's no difference in performance I prefer code that's easier to understand. Most times it's code with a few lines more. So I agree with Marcus (+1 for both)

boisterouslobster Over a year ago

That was a typo indeed. Thank you for your solution, that is what I needed!

Collectives™ on Stack Overflow

MYSQL Query timestamp comparison

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related