Now the timediff isn't really what I want - I really want the difference between lastpost and lastview, and not just the time (TIMEDIFF()), there is a DATETIMEDIFF() function but this isn't avaliable in older versions of MySQL, so I want to keep away from that. How do I create a field that will give me both the time and date difference?
Also I would like to create a final boolean column that is true if the datetimediff is negative and vice-versa, can NE help?
(UNIX_TIMESTAMP(m.datetime)-UNIX_TIMESTAMP(h.datetime)) AS timediff,
and gives much nicer output, thanks.
Basically this is for a heklp ticket system - the lastview or h.datetime, is the lsat time that the user viewed the ticket, hence it can be NULL, the m.datetime or lastpost value os the last time a message was posted into the ticket (much the same as threads in a forum), I want to be able to ORDER BY a: tickets with new posts since last view ordered by last post date, b: all other tickets ordered by last post date.
So I would like a boolean field, for
if lastview < last post bool = true
else bool = false