SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Return rows older than a certain date?

    For clean-up purposes of removing drafts I would like to select all posts older than 7 days then delete them. I read about Date_Sub but it doesn't seem to be giving me the results I would like as it is returning all of them.

    ("SELECT post_id FROM posts WHERE post_status = 'auto-draft' AND DATE_SUB( NOW(), INTERVAL 7 DAY ) > date_created" )

    My dates are stored in unix time so I'm not sure if Date_Sub won't work with that? The websites I've looked at aren't very clear on its usage. How would I go about this?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by darkwarrior View Post
    My dates are stored in unix time ...
    there's your problem -- integers and dates don't mix, not without converting one of them to the other

    and the trick here is ~not~ to convert the column value
    Code:
    SELECT post_id 
      FROM posts 
     WHERE post_status = 'auto-draft' 
       AND date_created < UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 7 DAY)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    http://dev.mysql.com/doc/refman/5.5/...unix-timestamp

    Code:
    UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 7 DAY )) > date_created

    Edit: I'm getting slow...

  4. #4
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for the quick reply. This does not seem to be returning any results at all however. I even tried reducing the Interval to 1 Minute but it isn't returning any rows.

    Just to clarify and make sure I'm providing the right information.

    The date's are like this 1320250921 and are stored as an 11 digit long int.

    I've tried running it in the myAdmin but it also returns 0 rows there, but there are quite a few rows that could be returned.

    Untitled-12.jpg

    EDIT: I'm not sure what the difference is but using GUIDO's code in myAdmin seems to be working. Thanks to you both.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    another point --

    i've seen many people write queries to retrieve data based on "the last X days"...

    ... and then use NOW() instead of CURRENT_DATE

    can anyone see why that's probably wrong?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by darkwarrior View Post
    ...but there are quite a few rows that could be returned.
    not in the sample data display that you posted

    the values would have to be less than 1319688000
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I change it to current_date it doesn't return anything. I'm starting to get lost here, perhaps I am storing the date wrong?

    It's set to just use time() unless a date is specified in which case it uses strtotime() to convert it.

    So for instance I'm using this now in myAdmin

    Code:
    SELECT post_id
    FROM posts
    WHERE post_status =  'auto-draft'
    AND UNIX_TIMESTAMP( DATE_SUB( NOW( ) , INTERVAL 1 
    MINUTE ) ) > date_created
    This returns 29 rows, the earliest date being:
    Wed, 02 Nov 2011 14:42:43 GMT
    And the latest:
    Wed, 02 Nov 2011 15:26:12 GMT
    But doesn't return the next post along:
    Wed, 02 Nov 2011 15:26:13 GMT

    That's only .1 second older and the latest post I created was made only 20 minutes ago, so they should all be showing up under there. Is this an issue with using NOW()?

    I assume nothing is showing up under current_date because it is using the date not the immediate time?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please humour me and run the following query
    Code:
    SELECT MIN(date_created) as mini
         , MAX(date_created) as maxi
         , UNIX_TIMESTAMP( DATE_SUB( NOW( ) , INTERVAL 7 DAY ) ) AS humpty
         , UNIX_TIMESTAMP( CURRENT_DATE - INTERVAL 7 DAY ) AS dumpty
      FROM posts
     WHERE post_status =  'auto-draft'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mini - 1320164320
    maxi - 1320252864
    humpty- 1319648335
    dumpty - 1319605200

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    do you understand what you just ran?

    the "mini" value says that no rows will satisfy your "7 days ago" query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot darkwarrior's Avatar
    Join Date
    Dec 2010
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I changed the query to 1 day and it returned the 1 row I specifically altered to be 1 day old. I'm not quite clearl on why using NOW() - 1 minute doesn't return those older posts but I willl use Current_date as this seems to work fine now I understand what is happening.

    Thanks for the help.


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
  •