SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    NC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problems searching timestamps using DATETIME formats

    I'm having problems searching date ranges in a table with dates that are set to int(14) timestamps. I'm trying to search against these timestamps
    with a datepicker that sets dates in the Mysql DATETIME format (YYYY-MM-DD). The problem is that the results are being returned w/ dates that are all over
    the place.

    Here's some examples of some queries I've been trying that either yield zero results when I know for a fact that there are dates w/in these ranges or just
    return no resuls at all:


    Code:
    SELECT SQL_CALC_FOUND_ROWS segment.segment_id,  segment.modify_date, segment.coder, segment.modify_date FROM segment
     WHERE segment.segment_id > 0 AND ((segment.modify_date >= UNIX_TIMESTAMP(2007-04-01)) AND (segment.modify_date <= UNIX_TIMESTAMP(2009-05-31))) AND (segment.modify_date != 0 ) AND (segment.modify_date != 0 )

    Code:
    SELECT SQL_CALC_FOUND_ROWS segment.segment_id,  segment.modify_date, segment.coder FROM segment
     WHERE segment.segment_id > 0 AND (segment.modify_date >= '2007-04-01') AND segment.modify_date != 0
    Any help would be greatly appreciated.

    Best,
    Clem C

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This should demonstrate why your first query doesn't work as expected:

    Code:
    mysql> select unix_timestamp('2007-04-01');
    +------------------------------+
    | unix_timestamp('2007-04-01') |
    +------------------------------+
    |                   1175400000 |
    +------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select unix_timestamp(2007-04-01);
    +----------------------------+
    | unix_timestamp(2007-04-01) |
    +----------------------------+
    |                          0 |
    +----------------------------+
    1 row in set, 1 warning (0.00 sec)
    Your second query compares integers to strings. If you stored the times in DATETIME columns, MySQL would be smart enough to cast the strings to dates and do the comparison correctly, I think... but it had no chance without a type hint on either side.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    by the way, when you do get it working, so that you find a row where segment.modify_date falls within the range of dates specified, you should probably remove this --
    Code:
    AND (segment.modify_date != 0 ) AND (segment.modify_date != 0 )
    you certainly don't have to code this twice, but the point is, if it's zero, it won't fall within the range, so you don't have to code it even once

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    NC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello - I found a solution to this problem and it all came down to single quotes.

    I changed this:
    Code:
    SELECT SQL_CALC_FOUND_ROWS segment.segment_id,  segment.modify_date, segment.coder, segment.modify_date FROM segment
     WHERE segment.segment_id > 0 AND ((segment.modify_date >= UNIX_TIMESTAMP(2007-04-01))
    to this:
    Code:
    SELECT SQL_CALC_FOUND_ROWS segment.segment_id,  segment.modify_date, segment.coder, segment.modify_date FROM segment
     WHERE segment.segment_id > 0 AND ((segment.modify_date >= UNIX_TIMESTAMP('2007-04-01'))
    and it worked great.

    Thanks for your help

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by clem_c_rock View Post
    Hello - I found a solution to this problem
    some guy on devshed, right?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •