1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

DateTime MySQL query?

Discussion in 'MySQL' started by anton-io!, Mar 8, 2006.

  1. #1
    Anyone know of any simple tutorials, links or how to perform
    a MySQL query with PHP on the DateTime field in the database?

    MySQL Database has DateTime Values (Example: 2/9/2006 3:30:06PM)

    Want to be able to perform the following queries:

    1: specific date (show all records on that date)

    2. range of dates (show all records in between 2 dates. example: form
    1/1/2006 to 1/3/2006)

    Thanks!!!
     
    anton-io!, Mar 8, 2006 IP
  2. jestep

    jestep Prominent Member Premium Member

    Messages:
    3,542
    Likes Received:
    203
    Best Answers:
    18
    Trophy Points:
    330
    #2
    
    
    //select specific date
    $sql = "SELECT * FROM my_table WHERE YEAR(date_field) = '2006' AND MONTH(date_field) = '9' AND DAY(date_field) = '11'";
    
    
    PHP:
    Not sure how to do the range off the top of my head, but I'll post if I remember
     
    jestep, Mar 8, 2006 IP
  3. onlyican.com

    onlyican.com Peon

    Messages:
    206
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    SELECT * FROM table WHERE date > '".$from_date."' AND < '".$to_date."'

    A little tip

    Run an If statement

    If ($to_date){

    $query = "SELECT * FROM table WHERE date > '".$from_date."' AND < '".$to_date."';
    }else{
    $query = "SELECT * FROM table WHERE date = '".$from_date."'";
    }
    $result = mysql_query($query);
     
    onlyican.com, Mar 8, 2006 IP
  4. lemming

    lemming Peon

    Messages:
    13
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    jestep's code seems like it should work, but I tend not to use functions in the where clause since they often don't use the index and might not perform as well.

    onlyican's example seems good, but be careful about:

    SELECT * FROM table WHERE date = '".$from_date."'";

    Since you're using datetime, you'll probably have to always do range queries to find all the records from a specific date. If you do a comparison with "=" on today's date '20060308000000', there probably aren't exact matches for the time part. What you probably want is:

    $query = "SELECT * FROM table WHERE date >= '20060308000000' AND < '20060309000000';

    It's trickier than it sounds since if you only have the date, you have to create a range by adding 1 day to the start date. So if you wanted to get info from a form and then form a query it would be something like:

    $month = $_REQUEST['month'];
    $day = $_REQUEST['day'];
    $year = $_REQUEST['year'];

    // You should probably do some error checking on the above values here...

    $php_start_time = mktime(0,0,0, $month, $day, $year);
    $php_end_time = $php_start_time + (24 * 60 * 60); // Add 1 day to start date.
    $start_date = date('Y-m-d', $php_start_time) . '000000';
    $end_date = date('Y-m-d', $php_end_time) . '000000';

    $query = "SELECT * FROM table WHERE date > '".$start_date."' AND < '".$end_date."';

    I haven't actually tested this code, but hopefully it gives you an idea of the tricky areas. For guidance, I usually go straight to the php.net and mysql.com manuals. The reference manuals often have comments that are helpful.
     
    lemming, Mar 9, 2006 IP
    falcondriver likes this.
  5. anton-io!

    anton-io! Active Member

    Messages:
    540
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    58
    #5
    thanks for all the help ..

    I ended up coming accross something really simple ...

    Can it get easier then that? It works!

    One thing I did need to add though was

    to take into consideration if calculating records from 1 day.
    thx!!!
     
    anton-io!, Mar 9, 2006 IP
  6. amwassil

    amwassil Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    I have a similar question, but none of the posted suggestions work.

    At a prototype site I have a mySQL query on range of dates that works perfectly. In the user form, you can specify any combination of "from" and "to" dates in the format YYYY-MM-DD and either by year only, year-month, month or day. I have the exact duplicate of this query at another site (with the database connection info modified, of course) and it does not work. I am wondering if the following might be the problem:

    Working location: MySQL version 4.1.18-standard-log
    nonWorking location: MySQL version 4.0.18-standard

    Here's the query:

    $query = "select * FROM Enhancements WHERE Integrated between '".$var1."' AND '".$var2."' ORDER by Sys";

    I would much appreciate any suggestions.

    Michael
     
    amwassil, May 8, 2006 IP
  7. onlyican.com

    onlyican.com Peon

    Messages:
    206
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Try your query with all lowercase letters for the table and field names.

    Some earlier versions dont support UpperCase Field and Table Names.

    If you add this statement under the mysql_query($query);

    echo mysql_error();

    It will tell you what the problem is.
     
    onlyican.com, May 8, 2006 IP
  8. scamp81

    scamp81 Peon

    Messages:
    25
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Anybody get anywhere with this? I'm having the same issue with a query on one of my sites.
     
    scamp81, Apr 10, 2008 IP
  9. rschalch

    rschalch Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    I´m beggining with php and mysql and I would like to know about a query where I can get all the days of the month except weekends !
     
    rschalch, Nov 15, 2008 IP