Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Posts
    63

    Unanswered: create boollean filed in SELECT query from dates

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

    Code:
    firstPost		lastPost		lastView		timediff
    2004-12-03 21:11:52  	2004-12-03 21:11:52  	2004-12-03 21:11:52  	00:00:00
    2004-12-10 10:53:30 	2004-12-10 10:53:30 	2004-12-10 10:53:30 	00:00:00
    2004-12-10 10:54:00 	2004-12-10 10:54:00 	NULL 			NULL
    2004-12-10 10:54:08 	2004-12-10 10:54:09 	2004-12-10 18:58:10 	08:04:01
    2004-12-10 10:54:19 	2004-12-10 10:55:24 	NULL 			NULL
    2004-12-10 18:29:51 	2004-12-10 18:29:51 	NULL 			NULL
    2004-12-10 10:53:22 	2004-12-10 19:09:56 	2004-12-10 18:36:57 	-00:32:59
    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how are you getting the time difference? TIMEDIFF was only introduced in 4.1.1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2002
    Posts
    63
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2002
    Posts
    63
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •