0

I want to select all rows where the value of the updated_on field is older than 60 seconds.

I do this:

SELECT * FROM processes WHERE updated_on < DATE_SUB(NOW(), INTERVAL 60 SECOND)

However, i get an empty result.

For example, there is a row which has "2014-04-15 10:43:22" as the updated_on value (a timestamp field).

What is wrong?

TheNiceGuy
  • 3,462
  • 8
  • 34
  • 64
  • 2
    it shouldn't be `updated_on >` ? – Pedro Lobito Apr 15 '14 at 09:27
  • 1
    @Tuga how is that? If the row is older, then it's timestamp is supposed to have a lower value than `NOW()`. It is like: you are older than me, so `your birthyear < my birthyear`. – sybear Apr 15 '14 at 09:31
  • Yeah that was exactly what i was thinking. – TheNiceGuy Apr 15 '14 at 09:32
  • My idea is that you should check the time of your server. `SELECT NOW()`, it might have a wrong timezone or something. – sybear Apr 15 '14 at 09:34
  • @Michael: What is the correct data type of `updated_on`? – Ravinder Reddy Apr 15 '14 at 09:35
  • @Ravinder it is a `timestamp`, he specified in the question. – sybear Apr 15 '14 at 09:36
  • @Jari: Yes. I read that. But due to the issue, I asked if it was *correct*. I should also have asked as *what time is it `now()` on db*? – Ravinder Reddy Apr 15 '14 at 09:38
  • @Jari, you are right. The server returns "2014-04-15 05:35:53". But i am a bit confused. I have set the extra for the specic field to "ON UPDATE CURRENT_TIMESTAMP". Why is it inserting the wrong timestamp then? – TheNiceGuy Apr 15 '14 at 09:38
  • `CURRENT_TIMESTAMP` is still related to the server time. You might find a proper answer here on how to set date in mysql. http://stackoverflow.com/questions/930900/how-to-set-timezone-of-mysql Also, if those rows are important to you, just calculate the time difference between real timezone and the wrong timezone, and perform timeshift on all rows using `UPDATE` – sybear Apr 15 '14 at 09:42
  • WHat you mean with "related to the server time"? Are CURRENT_TIMESTAMP and now() using different sources for the timezone? Sadly i can't modify the timezone settings. – TheNiceGuy Apr 15 '14 at 09:49
  • Okay i found the problem. I had the wrong timezone at the PHP settings. Thanks anyways! – TheNiceGuy Apr 15 '14 at 10:39

2 Answers2

0

you should must mention greater than

try this:

SELECT * FROM processes WHERE updated_on > DATE_SUB(NOW(), INTERVAL 60 SECOND);

try this: update:1

SELECT * FROM processes WHERE updated_on < unix_timestamp(now() - interval 60 second);
jmail
  • 5,944
  • 3
  • 21
  • 35
0

Try this 

SELECT * FROM `processes` where TIMESTAMPDIFF( SECOND , updated_on, NOW()) < 60
Mehul Jethloja
  • 637
  • 4
  • 9