Compare two timestamps in mysql, then return a custom field

Question

I'm building a small scale forum script in PHP, and I am having difficulty demonstrating the read/unread status of a given thread.

So, I have a particularly gargantuan query which returns the thread information, including the date it was first posted in, the date it was last posted in (if any) as well as the date it was last looked at by the currently logged in user (if such a date exists.

The query then returns three values:

• firstPostDate
• lastPostDate
• lastViewDate

What I need to check is that if firstPostDate or lastPostDate are more recent than lastViewDate then to mark that thread as being new / having unread replies.

I'm having problems executing this in PHP, due to the fact that my dates are all stored as timestamps in MySQL. At first I thought I could use UNIX_TIMESTAMP to return a Unix timestamp and then compare them, but I don't believe that I'm getting the correct results (because the timestamps are in Y-M-D H-M-S format (or similiar, I do not remember off the top of my head).

I have other queries in the site that compare two dates, and that appears to work well. Is there a way I can compare the two or three dates in a mysql query, and return a "YES" if it's new, or "NO" if it is not? It would make my PHP markup much simpler.

Or am I going about this the wrong way?

Answer 1

You can use an expression as an additional field in your result set:

SELECT   ...
         (firstPostDate > lastViewDate OR lastPostDate > lastViewDate) AS unread
FROM     posts;

The unread field should be 1 when the thread is new / having unread replies, and 0 otherwise.

Test case:

CREATE TABLE posts (
    id int, 
    firstPostDate timestamp, 
    lastPostDate timestamp, 
    lastViewDate timestamp
);

INSERT INTO posts VALUES (1, '2010-01-01 12:00', '2010-01-02 12:00', '2010-01-03 12:00');
INSERT INTO posts VALUES (2, '2010-01-03 12:00', '2010-01-05 12:00', '2010-01-04 12:00');
INSERT INTO posts VALUES (3, '2010-01-06 12:00', '2010-01-06 12:00', '0000-00-00 00:00');
INSERT INTO posts VALUES (4, '2010-01-07 12:00', '2010-01-07 12:00', '2010-01-08 12:00');

Result:

SELECT   id,
         (firstPostDate > lastViewDate OR lastPostDate > lastViewDate) AS unread
FROM     posts;

+------+--------+
| id   | unread |
+------+--------+
|    1 |      0 |
|    2 |      1 |
|    3 |      1 |
|    4 |      0 |
+------+--------+
4 rows in set (0.01 sec)