SQL query help needed

  • Time
  • Show
Clear All
new posts
  • doli
    • Mar 2006
    • 54

    SQL query help needed

    I extracted number of users who have registerd on certain dates:

    SELECT count(*) FROM `user` 
    WHERE `joindate` >= UNIX_TIMESTAMP('2010-11-15 00:00:00')
    AND `joindate` <UNIX_TIMESTAMP('2010-11-22 00:00:00')
    AND `usergroupid` = 2
    Now I want to check how many of these new users wrote a post within next 24 or 48 hours after registration, any idea how to do this? Probably some left join with post table ?

    And one more question, what is 'dateline' in thread table?
    Is this time when the first post was submited, or maybe last replay time ?
  • Lynne
    Former vBulletin Support
    • Oct 2004
    • 26255

    thread.dateline is the first post time or thread creation time. thread.lastpost is the time of the last post.

    I'll let someone else tackle the query question since I'm not very good at those.

    Please don't PM or VM me for support - I only help out in the threads.
    vBulletin Manual & vBulletin 4.0 Code Documentation (API)
    Want help modifying your vbulletin forum? Head on over to vbulletin.org
    If I post CSS and you don't know where it goes, throw it into the additional.css template.

    W3Schools &lt;- awesome site for html/css help


    • Lats
      Senior Member
      • Mar 2002
      • 3671

      Try this...
      SELECT user.userid, user.username, FROM_UNIXTIME(post.dateline) post_date, post.postid  
      FROM user, post 
      WHERE joindate >= UNIX_TIMESTAMP('2010-11-15 00:00:00')
      AND joindate <UNIX_TIMESTAMP('2010-11-22 00:00:00')
      AND (post.dateline - user.joindate) < 172800
      AND usergroupid = 2
      GROUP BY


      widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.