SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grabbing data based on date with a timestamp

    I'm storing my dates as a UNIX timestamp.
    I also have my news system, and in my archive system I want to be able to grab the data based on the month.

    I was thinking of using DATE_FORMAT to get the timestamp into something readable and compareable.
    My problem is mysql.com (in it's infinite knowledge) didn't specify what can be used in the "date" field. Not to mention I don't know where to put the column in the WHERE clause.

    Does it accept UNIX timestamps? And if not how would I go about grabbing the data I need?
    Last edited by Defender1; Aug 11, 2002 at 23:01.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how about using the UNIX_TIMESTAMP() function? something like

    Code:
    ... WHERE timestamp_col=UNIX_TIMESTAMP('2002-08-12 00:00:00')
    or for a range

    Code:
    ... WHERE timestamp_col BETWEEN UNIX_TIMESTAMP('2002-08-12 00:00:00') AND UNIX_TIMESTAMP('2002-08-12 23:59:59')
    maybe that gives you something to work with.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Almost, I need to compare the month only.

    Basically the user will click a link, and that'll tell the script which month I need to grab. Then I need to compare that to the timestamp I have stored.

    Would it just be better to store a formatted date?
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, so you want all entries in a certain month? and a single year too? so like for August right now, maybe you could use this

    Code:
    ... WHERE timestamp_col BETWEEN UNIX_TIMESTAMP('2002-08-01 00:00:00') AND UNIX_TIMESTAMP('2002-08-30 23:59:59')
    if you were using a DATETIME column etc. the query would be like this i guess

    Code:
    ... WHERE YEAR(date_col)=2002 AND MONTH(date_col)=8
    i'm not sure if that would use an index if it existed on the column or not. i know the first method would though. and if you were searching for a certain month in any year, i know that nothing would use an index (just something i was keeping in mind for performance ).

  5. #5
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, usin the range.
    And I just want it for a specific year.
    I've started coding it. Just gotta test if it's a leap year.
    (I'll post the code when it's done. Should be just a min. )

    [Edit]
    Ok, here we go
    PHP Code:
    $month $_GET["m"];
    $year $_GET["y"];
    if(
    $month == || || || 11)
    {
        
    $day 30;
    }
    else if(
    $month == 2)
    {
        if(
    date("L") == 1)
        {
            
    $day 29;
        }
        else
        {
            
    $day 28;
        }
    }
    else
    {
        
    $day 31;
    }

    $ts1 $year "-" $month "-" $day " 00:00:00";
    $ts2 $year "-" $month "-" $day " 00:00:00";

    $sql "SELECT * from news_archive
        WHERE date_added BETWEEN UNIX_TIMESTAMP('
    $ts1') AND UNIX_TIMESTAMP('$ts2')
        ORDER BY date_added DESC LIMIT 10"

    How's that look? I haven't tested it yet.
    Last edited by Defender1; Aug 11, 2002 at 23:40.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    better than i could've came up with that quick. one problem is that

    if($month == 4 || 6 || 9 || 11)

    will always be true. you need to use

    if ($month == 4 || $month == 6 || $month == 9 || $month == 11)

    or use in_array() like i did.

    and i'm not sure if the month/day has to be at least 2 characters long or not? i added a 0 to the front for months/days < 10 just in case.

    also, $ts1 and $ts2 were a little off so i think i fixed them.

    PHP Code:
    $month = (int) $_GET['m'];
    $year  = (int) $_GET['y'];

    if (
    in_array($month, array(135781012)))
    {
        
    $day 31;
    }
    else if (
    $month != 2)
    {
        
    $day 30;
    }
    else
    {
        if (
    date('L'))
        {
            
    $day 29;
        }
        else
        {
            
    $day 28;
        }
    }

    if (
    $month 10) { $month '0' $month; }
    if (
    $day 10)   { $day '0' $day; }

    $ts1 "$year-$month-01 00:00:00";
    $ts2 "$year-$month-$day 23:59:59";

    $sql "SELECT * FROM news_archive
        WHERE date_added BETWEEN UNIX_TIMESTAMP('
    $ts1') AND UNIX_TIMESTAMP('$ts2')
        ORDER BY date_added DESC LIMIT 10"


    try that.

    P.S. i didn't realize that August had 30 days in my previous example.
    Last edited by DR_LaRRY_PEpPeR; Aug 12, 2002 at 00:00.

  7. #7
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heh, yea I code quick, but as the title implies, I leave quite the error trail.

    Though I always iron em out as I'm testing myself.

    Thx for pointin out the errors. Wasn't sure on that if either. Was hoping it'd let me do that shortly, but that's PHP for ya.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  8. #8
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DR_LaRRY_PEpPeR
    P.S. i didn't realize that August had 30 days in my previous example.
    ooops, i meant i didn't know it had 31 days! i couldn't even get it right the second time.

  9. #9
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ya got a problem with august or somethin?
    Itchin to get back to school maybe? heh
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*


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
  •