Hi I have a SELECT query that grabs some dates, where the 'lastview' date may be Null e.g.

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?

Thanks.

Note am using MySQL.

you can convert both lastPost and lastView to a unix timestamp, subtract them, and then convert the answer to something that means something to you

what's the largest difference you expect?

by the way, i'm a forum moderator, and i'll move this into the mysql forum for you

how are you getting the time difference? TIMEDIFF was only introduced in 4.1.1

Thanks for moving the post

Yeah, I was using TIMEDIFF()

changed it to:

(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

So I can ORDER BY bool, lastpost

if you were using TIMEDIFF(), why didn't you try DATEDIFF()?

by the way, you don't need a boolean, you just need this --

ORDER BY case when lastview < lastpost then 1 else 2 end, lastpost

This is the first time i've ever really mucked aorund with dates in SQL, i did try Date diff, but then i tried time diff, trying to get the full date diff - if that makes sense :s lol.

ooooo, I hadn;t seen the CASE statement in SQL before, thanks, that solves everything

Thanks again