Mysql query date help.

Discussion in 'PHP' started by Josh-H, Aug 12, 2007.

0
  1. #1
    What kind of mysql query would I need for it to only select rows that have the 'boughton' date less than 31 days ago?

    Like so if the row date was 2007-08-08 it would not be selected if it was 2008-08-08 as its more than 31 days ago. Just for example.




    Thanks for reading.
     
    Josh-H, Aug 12, 2007 IP
  2. void

    void Peon

    Messages:
    119
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #2 
    SELECT * FROM YourTable WHERE boughton < DATE_SUB(NOW(), INTERVAL 31 DAY)
    Code (markup):
    If you prefer it to do it by month rather than 31 days you'd use
    SELECT * FROM YourTable WHERE boughton < DATE_SUB(NOW(), INTERVAL 1 MONTH)
    Code (markup):
     
    void, Aug 12, 2007 IP
  3. Josh-H

    Josh-H Active Member

    Messages:
    406
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    55
    #3
    That doesnt seem to work for me. Does the mysql table field type need to be date? And how should I be inputting it into that. Like what function?
     
    Josh-H, Aug 12, 2007 IP
  4. l0gic

    l0gic Peon

    Messages:
    22
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Yes, the column type would have to be DATE. How are you storing the date now?

    You might consider storing epoch time (number of seconds since 1 Jan 1970); this provides a 32-bit integer value which can be broken down to second-long intervals. It's much more flexible when comparing timestamps.
     
    l0gic, Aug 12, 2007 IP
  5. void

    void Peon

    Messages:
    119
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #5
    You can enter dates as strings in the format YYYY-MM-DD e.g.

    INSERT INTO yourtable (boughton, quantity) VALUES ('2007-06-03', 12)
    Code (markup):
     
    void, Aug 12, 2007 IP