SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    stats: get data by days

    Hello,

    I am working on a project where I will have to store stats (how many times a website is visited etc.). I will have to manipulate the data by day, month, year. As in: select the last 7 days / select last month etc. I was wondering if I should go with timestamp (INT) or datetime.

    Regards,

    -jj.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i prefer datetime, because you can actually understand it when looking at the data, e.g. when using the dreaded, evil "select star" during preliminary testing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And here comes the trouble...

    It seems fairly easy to me to create unixdate with php using time(). I have no idea how to record a date in the datetime format. Is it done with php? Through a mysql query?


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sorry, man, i don't do php (and this ain't the php forum, eh)

    but it's definitely doable, all you have to do is use some php function to generate the date in this format: YYYY-MM-DD HH:MM:SS
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    @jjshell

    Try this:
    Code:
    INSERT INTO sometable 
      (date_name, date_and_time)
    VALUES
      ('date_field_name', '2012-7-4 04:13:54');
    or this
    PHP Code:
     $sql "
    INSERT INTO sometable 
      (date_name, date_and_time)
    VALUES
      ('date_field_name','" 
    NOW() . "')";
    [/
    CODE]
    //Run your SQL query with PDO, MSQLi... 
    Steve
    ictus==""

  6. #6
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your replies.

    Just out of curiosity, and so I can make my mind, before I dive into this, how would a query grabbing the 7 days prior to today look like with datetime? Would it be a lot different with a timestamp?


  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    compare...

    using a timestamp column (integer) --
    Code:
    WHERE timestampcolumn >= UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 7 DAY)
      AND timestampcolumn  < UNIX_TIMESTAMP() -- defaults to today
    using a datetime column --
    Code:
    WHERE datetimecolumn >= CURRENT_DATE - INTERVAL 7 DAY
      AND datetimecolumn  < CURRENT_DATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    @jjshell

    While deciding you may also want to read this http://billauer.co.il/blog/2009/03/m...och-unix-time/ blog post as well as the comments to decide what date/time format you want to use.

    Keep in mind that MySQL has advanced a lot since this article was written but the article does point out some other factors that might influence what you use.

    Steve
    ictus==""

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the comments in that article make a more convincing case ~against~ using the unix timestamp integer

    see also my point in post #2 above, which nobody has yet refuted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by r937 View Post
    the comments in that article make a more convincing case ~against~ using the unix timestamp integer

    see also my point in post #2 above, which nobody has yet refuted
    I read it the same way, hopefully jjshell sees it from both sides and has the same conclusion
    ictus==""

  11. #11
    SitePoint Wizard
    Join Date
    Jan 2005
    Location
    blahblahblah
    Posts
    1,447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sold!

    So, how should I enter a new datetime? Using NOW()?
    Is datetime going to be harder to manoeuver than date? I'm thinking, why not store hours and seconds, I may need them in the future (but not right now).

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    use CURRENT_TIMESTAMP instead of NOW()

    manoeuver? you mean, like with date and time functions?

    and don't bother storing hours and seconds -- the minutes will feel so disrespected
    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
  •