SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru coiL's Avatar
    Join Date
    Sep 2001
    Location
    QLD, Australia
    Posts
    666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to compare day and month only?

    Hi,
    I couldn't quiet figure out how you could compare only the day and month of timestamps stored in the database.

    eg; If I wanted to compare only the day and month of today's date (using time() ) to a timestamp in the database, how would I do this?

    thanks.
    coiL
    "cradled in the learning curve"

  2. #2
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The easiest way that I can think of is to put the database date to mktime() and then put your current date to mktime() as well, then subtract them.

    If you end up with zero (based on date only - not hours, minutes, seconds) then you have a match, else you don't have a match.

    It shouldn't matter if you use either DATE or TIMESTAMP since you only want the actual date only - just zeroise the time elements of mktime().

  3. #3
    SitePoint Guru coiL's Avatar
    Join Date
    Sep 2001
    Location
    QLD, Australia
    Posts
    666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks

    could you please give me an example of how I use mktime on a date from the database

    eg; $date = 1047859316

    coiL
    "cradled in the learning curve"

  4. #4
    SitePoint Guru coiL's Avatar
    Join Date
    Sep 2001
    Location
    QLD, Australia
    Posts
    666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anyone?

    I really need some help on this


    thanks
    coiL
    "cradled in the learning curve"

  5. #5
    SitePoint Guru coiL's Avatar
    Join Date
    Sep 2001
    Location
    QLD, Australia
    Posts
    666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok I tried

    PHP Code:
    <?
     
    $time 
    time() + 61200;
    $today date("dmY",$time);
     
    $sql "SELECT *
    FROM jobs
    INNER JOIN bids on bids.job_id = jobs.id
    WHERE bids.mem_id='
    $mem_id' AND DATE_FORMAT(dmY,bids.bidDate)='$today'";
     
    ?>

    help please
    coiL
    "cradled in the learning curve"

  6. #6
    SitePoint Zealot Egghead's Avatar
    Join Date
    Feb 2002
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Coil,
    There are many date and time functions that will help you out. As you don't say which database you are using, I will give you the standard SQL first, then the more specific MySQL.

    The SQL EXTRACT() function isolates a single field of a datetime and returns it as a number.
    E.g. EXTRACT(field FROM datetime)
    Field can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND (and a few others)

    MySQL has its own set of functions which you will be particularly interested in:
    YEAR(column) returns just the year value of a stored date
    MONTH(column) returns just the numerical month value
    DAYOFMONTH(column) returns just numerical day value
    CURDATE() returns the current date

    Also there are the really useful ADDDATE() and SUBDATE() functions (or DATE_ADD() and DATE_SUB() if you prefer)
    If you insist on just retrieving the date from the database and then using PHP to do the calculations, then why not just use the substr() function to get the part of the date string you want?
    PHP Code:
    <?php
    $sql 
    "SELECT date FROM table";
    $result mysql_query($sql);
    while (
    $row mysql_fetch_array($result))
    {
       
    $date $row["date"];
          
    // convert date format into seperate items
          
    $year  substr($date04);
          
    $month substr($date52);
          
    $day   substr($date82);
    }
    ?>

  7. #7
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Doubtful if DATE_FORMAT can be of any use here; in fact I don't see the need for it either.

    In PHP, the mktime accepts six varaibles, 3 for date and 3 for time. At this moment I don't have any example script, though the Manual is easy to follow.

    For your purpose, you can leave the last 3 variables as ZERO since you are using date only.


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
  •