CODEWITHSUNDEEP
phpmysql
JMRboosties
JMRboosties

Reputation: 15740

Question regarding MySQL timestamp comparisons

I'll explain my goal first: I want the user to query the database, and return rows only if those rows have been updated since their last query. No sense returning data they'd already have. So I created a column called 'lastupdated', a timestamp type which autoupdates every time any content in the row is updated. This works fine. Now, I want to form the query correctly. The user will have their previous query's timestamp saved, and via php will use it to compare their previous query's time with the time each row has been updated. If the row was updated after their last query, the row should be returned.

I made something like this,

SELECT * FROM users WHERE '2011-02-26 01:50:30' <= lastupdated

but its obviously much too simple. I checked the MySQL manual and found this page MySQL Time/Date Page. I'm sure the answer is here, but I've read through it any nothing really makes sense. I have a timestamp in the same format used by the MySQL timestamp type, but I don't know how I will compare them. Thank you very much for your help.

Upvotes: 1

Views: 2969

Answers (2)

Greg
Greg

Reputation: 2563

As Marc said, your code should work. But you probably want to do this programmatically with a variable for the time instead of the literal.

If you don't have the date-time specified as a string, but rather as a timestamp (e.g. from using the php time() function), then you can use the following query:

$query = "SELECT * FROM users WHERE FROM_UNIXTIME(" . $timestamp . ") <= lastupdated";

The key is the FROM_UNIXTIME() MySQL function.

Upvotes: 3

Marc B
Marc B

Reputation: 360672

That query is exactly how you'd do it. As long as a stringified date-time is in MySQL's preferred format (yyyy-mm-dd hh:mm:ss), then it will be internally converted into a datetime value, and the comparisons will go ahead.

You'd only need the date/time functions you found if you want to do something more complicated than simple "greater/less than/equal" type comparison, e.g. "any records that have a December timestamp".

Upvotes: 5

Related Questions