Struggling with constructing mysql datetime between query

Discussion in 'MySQL' started by oscillate, Jun 4, 2011.

0
  1. #1
    hi guys,
    really having trouble writing a query.. i've been at it for hours so i thought it might be time to enlist some pro help!

    I'm trying to select records which are 5 minutes either side of the current time, 3 days ago

    example, if today's date/time is 2011-06-04 21:15:00,
    find records between the below dates:
    2011-06-01 21:10:00
    2011-06-01 21:20:00

    thanks very much!
     
    oscillate, Jun 4, 2011 IP
  2. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #2
    Use MySQL's SUBDATE(), SUBTIME() and ADDTIME() functions to get your desired results.
     
    mwasif, Jun 4, 2011 IP
  3. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #3
    How about trying following?

    
SELECT column_names FROM table_name WHERE date_column BETWEEN FROM_UNIXTIME(UNIX_TIMESTAMP() - 259500) AND FROM_UNIXTIME(UNIX_TIMESTAMP() - 258900);
    Code (markup):
    If the queries are gonna be frequent and database is gonna be large, i suggest to keep column as bigint and store date as unix timestamp in it. The searching will be faster that way.
     
    mastermunj, Jun 14, 2011 IP