SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict ibeblunt's Avatar
    Join Date
    Jan 2001
    Location
    Jersey City, NJ
    Posts
    312
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A little confused.

    Ok, I got this query statement and I want it to pull all the rows that are OLDER than 30 days.

    Using todays date as a timestamp and subtracting 30 days from that gives me a total of 1009914675.

    So would my query statment be:

    1. SELECT * FROM table WHERE timestamp > 1009914675

    or

    2. SELECT * FROM table WHERE timestamp < 1009914675

    I've been up too long.
    <///////~

    www.sohh.com

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this going to be a recurring query, as in run repeatedly, or is it a one time deal?

    Sketch
    Aaron Brazell
    Technosailor



  3. #3
    SitePoint Addict ibeblunt's Avatar
    Join Date
    Jan 2001
    Location
    Jersey City, NJ
    Posts
    312
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes
    <///////~

    www.sohh.com

  4. #4
    Action! filmfoto's Avatar
    Join Date
    Dec 2001
    Location
    Sweden
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried this:

    PHP Code:
    SELECT FROM table WHERE timestamp NOW() - interval 1 month 
    Check out the MySQL manual.




    Cheers.

  5. #5
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, what? Yes recurring?

    If so, youse mktime() to get the current time of execution and then subtract 30 days that...or add 30 ays to set a 30 day expiration.

    Sketch
    Aaron Brazell
    Technosailor



  6. #6
    SitePoint Addict ibeblunt's Avatar
    Join Date
    Jan 2001
    Location
    Jersey City, NJ
    Posts
    312
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So:

    $timestamp = time()-(30 * 86400);

    Isn't the same as mktime()?

    Basically, I'm running vB and I want to erase all private messages that are over 30 days old that have been read and replied to residing in the inbox.

    So I wrote the query as:

    SELECT * FROM privatemessage WHERE folderid = 0 AND dateline < $timestamp AND messageread = 2 ORDER BY dateline

    But not sure which way the greater than/less than sign should go. Since I get results either way.
    <///////~

    www.sohh.com

  7. #7
    SitePoint Addict exhale81's Avatar
    Join Date
    Aug 2001
    Location
    Switzerland
    Posts
    395
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i believe the INTERVAL comand is only available in the most recent versions of mysql so beware.
    Sebastien Rosset
    :: Commercial: BlackSonic | ScriptsCenter
    :: Non-Profit: VJCentral | ReclaimYourSoul

  8. #8
    Action! filmfoto's Avatar
    Join Date
    Dec 2001
    Location
    Sweden
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by exhale81
    i believe the INTERVAL comand is only available in the most recent versions of mysql so beware.

    It is new from version 3.22.x. But who hasn't upgraded to at least that version?



    Cheers.

  9. #9
    SitePoint Addict exhale81's Avatar
    Join Date
    Aug 2001
    Location
    Switzerland
    Posts
    395
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah but if like me you write scripts for others, you don't want to force them to upgrade mysql (most users can't or don't know how to do that) just to run a query
    Sebastien Rosset
    :: Commercial: BlackSonic | ScriptsCenter
    :: Non-Profit: VJCentral | ReclaimYourSoul

  10. #10
    Action! filmfoto's Avatar
    Join Date
    Dec 2001
    Location
    Sweden
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, good point.

  11. #11
    Your daddy. WALoeIII's Avatar
    Join Date
    Apr 2001
    Location
    USA
    Posts
    526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm pretty sure it goes this way:
    Code:
    <

  12. #12
    Your daddy. WALoeIII's Avatar
    Join Date
    Apr 2001
    Location
    USA
    Posts
    526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I'm correct, thats the number of seconds since Jaunary 1 1970, so fewer seconds would be older.


Bookmarks

Posting Permissions

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