Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6

Thread: Query timestamp

  1. #1
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query timestamp

    Hello guys I need a little help. the application i ma using stores a timestamps in the database that looks like this 1327582975 I am trying to get data from the database based on the timestamps so here is my code. please tell me where i am going wrong.

    PHP Code:
    $today2 date("Y-m-d");
    $query_str "SELECT addtime , COUNT(addtime) AS newmembers "
    ." FROM signup "
    ." WHERE DATE_FORMAT(addtime, '%Y %m, %d') = '$today2'  "

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Your format is different. Your $today2 is in format 2012-1-26 while your date_format in SQL is 2012 1 26. You can bypass this directly:
    Code:
    $query_str = 'SELECT addtime, count(addtime) AS newmembers
            FROM signup
            WHERE addtime >= UNIX_TIMESTAMP(CURRENT_DATE())';
    I *believe* that the unix_timestamp will work with current_date to push it to midnight this morning.

  • #3
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    so in that case would this be correct


    PHP Code:
    $query_str 'SELECT addtime, count(addtime) AS newmembers
            FROM signup
            WHERE addtime >= UNIX_TIMESTAMP(CURRENT_DATE()- INTERVAL 1 MONTH)'


  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I can't test this (and far from a SQL expert, but need to get re-practiced up), but I believe that any DateTime handling type in SQL overloads +/- as you have above without an explicit call to DATE_ADD/DATE_SUB.
    If it syntax errors out, then use UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)) (but as mentioned I believe what you have will work perfect).

  • #5
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts
    your right is does work mine and your I tested it. but it adds all of the sign ups for the pass month. another question

    why is this not working BTW the viewtime Column has data like this "2012-01-26 01:56:43" basically timestamps
    PHP Code:
    SELECT viewtimeCOUNT(viewtime) AS dash_daily_view_Number
    FROM video
    WHERE DATE
    (viewtime) = MONTH(NOW(viewtime)) 
    GROUP BY viewtime
    ORDER BY viewtime 

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    viewtime is a datetime datatype from the looks of it. You are comparing only a month, which is a numeric datatype to a date. NOW() also doesn't accept any arguments.
    Pull both as month: WHERE MONTH(viewtime) = MONTH(NOW()), which will match anything from the start of the current month (as opposed to within the past month, which is what I figure you are trying to replace).


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •