mysql query help

Discussion in 'MySQL' started by sbutt, Oct 12, 2012.

0
  1. #1
    Hi all,
    I am finding it difficult to build one mysql query.


    I have a table messages_stats_analysis_ota, with 4 columns: [brand_code, date_time_from, date_time_to, success_count].


    I want the value inside 'success_count' against individual 'brand_code', which lie within the last 7 days from the current date.


    u2, 01.10.2012, 07.10.2012, 100
    u2, 02.10.2012, 08.10.2012, 100
    u2, 03.10.2012, 07.10.2012, 100
    u2, 08.10.2012, 14.10.2012, 50
    fr, 01.10.2012, 07.10.2012, 200




    Now lets assume, the current date is 08.10.2012.


    I want only the follwoing 2 rows to be retrieved:


    u2, 01.10.2012, 07.10.2012, 100
    fr, 01.10.2012, 07.10.2012, 200


    I tried to build this query:


    SELECT * FROM messages_stats_analysis_ota WHERE date_time_from >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);


    but it gives me, the follwoing, which i dont want:


    u2, 01.10.2012, 07.10.2012, 100
    u2, 02.10.2012, 08.10.2012, 100
    u2, 03.10.2012, 07.10.2012, 100
    fr, 01.10.2012, 07.10.2012, 200


    Assumtpion is current date is: 08.10.2012.


    Coudl someone please help me out?


    Thanks.
     
    sbutt, Oct 12, 2012 IP
  2. G.N.C.

    G.N.C. Member

    Messages:
    40
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    26
    #2
    In unix timestamp:
    [COLOR=#111111]SELECT * FROM messages_stats_analysis_ota WHERE UNIX_TIMESTAMP(date_time_from) >= (UNIX_TIMESTAMP()-[/COLOR][COLOR=#111111]7*86400)[/COLOR]
    Code (markup):
     
    G.N.C., Oct 13, 2012 IP