SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Storing "Last Activity"

    I am in the processing of adding an "Online Status" for my Users, so when you look under the "Article Comments" section of my website, you will be able to see if a User is either...
    - Online and Active
    - Online but Idle
    - Offline


    I haven't figured out the coding yet, but I will obviously need to compare a User's "lastActivity" against the current time.

    What would be the best Data Type in MySQL to use (e.g. DATETIME vs TIMESTAMP)?

    And would it be better to work with MySQL's Native Date/Time Format or use the Unix Format?

    Thanks,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    What would be the best Data Type in MySQL to use (e.g. DATETIME vs TIMESTAMP)?
    either... pick one

    Quote Originally Posted by DoubleDee View Post
    And would it be better to work with MySQL's Native Date/Time Format or use the Unix Format?
    your choice... pick one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    either... pick one

    your choice... pick one
    Try not to pick sides, okay?!


    Debbie

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm with Rudy, it really doesn't matter in either question you have post which choice you make. Pick the one you are comfortable with and go from there.

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    I'm with Rudy, it really doesn't matter in either question you have post which choice you make. Pick the one you are comfortable with and go from there.
    What about chosing the Integer data type and storing a PHP Unix Timestamp (i.e. time()) in the record?

    Some have told me that that format is much easier to work with in PHP...


    Debbie

  6. #6
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    What kind of work do you want to do with those timestamps?

    Converting a database type datetime value to a unix timestamp and then working with it is super easy:

    PHP Code:
    $row fetch_something_from_the_database_or_summots();

    $ts = new DateTime($row['last_activity']);
    echo 
    $ts->format('m/d/Y H:i:s'); // outputs 03/07/2012 07:48
    echo $ts->getTimestamp(); // outputs the unix timestamp
    $ts->add(new DateInterval('P10D')); // add 10 days to the datetime thingy
    echo $ts->format('m/d/Y H:i:s'); // outputs 03/17/2012 07:48 

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Immerse View Post
    What kind of work do you want to do with those timestamps?
    The field "last_activity" keeps track of the last time the User did *something* on my website (e.g. Logged In, Submitted a Form, Navigated to a New Page, etc)

    If a User have had "activity" in the last 15 minutes then a green light appears next to their profile info when Comments are listed beneath an Article.

    If the User has had 'activity" between 15 and 30 minutes then a yellow light appears next to their profile info when Comments are listed beneath an Article.

    If the User has not had "activity" in the past 30 minutes then I treat them as "logged out" a gray light appears next to their profile info when Comments are listed beneath an Article.

    I was told that would be easiest to do using PHP's Unix Timestamp which is just the # of seconds isnce the Unix Epoch.


    Converting a database type datetime value to a unix timestamp and then working with it is super easy:

    PHP Code:
    $row fetch_something_from_the_database_or_summots();

    $ts = new DateTime($row['last_activity']);
    echo 
    $ts->format('m/d/Y H:i:s'); // outputs 03/07/2012 07:48
    echo $ts->getTimestamp(); // outputs the unix timestamp
    $ts->add(new DateInterval('P10D')); // add 10 days to the datetime thingy
    echo $ts->format('m/d/Y H:i:s'); // outputs 03/17/2012 07:48 
    No OOP please!


    Debbie

  8. #8
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    No OOP please!
    Why not?


    PHP is becoming more and more OO, you'll have to dive in at some point. And to be honest, the examples above are a good starting points.
    But the php.net page for DateTime also has procedural variations of the above, if you must.


    As for the coloured thingies, you can use the strtotime() to convert a database datetime to a unix timestamp. If it's only for this use, then it doesn't really matter what format you save them in. But, since it's a date and a time, might as well go for datetime!

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Immerse View Post
    Why not?


    PHP is becoming more and more OO, you'll have to dive in at some point.
    And today is NOT when it's going to happen!!!


    As for the coloured thingies, you can use the strtotime() to convert a database datetime to a unix timestamp. If it's only for this use, then it doesn't really matter what format you save them in. But, since it's a date and a time, might as well go for datetime!
    But the last_activity is being set by PHP, and I thought that time() would give me the # of seconds from the Unix Epoch as an Integer. And then I could store that as an Integer and compare to to the current time() to see if the User should or should not drive.

    If I wanted my script to capture now() and then save that as a DATETIME in the database, how would I do that?


    Debbie

  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 DoubleDee View Post
    And today is NOT when it's going to happen!!!
    Hi Debbie,

    Starting with PHP 4 some form of OOP was possible, since that time more and more work has gone into adding features that are typical and useful in other OOP languages. PHP (until 5.4) was only able to to single inheritance (an OOP concept); however that did not matter as much because many people overcame this by using composition (another OOP concept) which prior to 5.4 was possible. In 5.x Interfaces where added, 'Magic' features where improved and the memory stack management became a lot better for classes and other OOP constructs. In later 5.x releases Reflection (being able to look inside a class for its methods and properties) became possible - this is a very powerful feature. In 5.4 much of the lack of multiple inheritance has be circumvented by Traits which allow us to do a 'copy and past' of logic between classes. Also reflection has been improved. It is now quite possible to create a fairly elegant class based application with PHP. So like Immerse says 'why not dive in'?

    Regards,
    Steve
    ictus==""

  11. #11
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why would you just not simply use NOW() in your mysql insert? No PHP worries at all.

  12. #12
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    Hi Debbie,

    Starting with PHP 4 some form of OOP was possible, since that time more and more work has gone into adding features that are typical and useful in other OOP languages. PHP (until 5.4) was only able to to single inheritance (an OOP concept); however that did not matter as much because many people overcame this by using composition (another OOP concept) which prior to 5.4 was possible. In 5.x Interfaces where added, 'Magic' features where improved and the memory stack management became a lot better for classes and other OOP constructs. In later 5.x releases Reflection (being able to look inside a class for its methods and properties) became possible - this is a very powerful feature. In 5.4 much of the lack of multiple inheritance has be circumvented by Traits which allow us to do a 'copy and past' of logic between classes. Also reflection has been improved. It is now quite possible to create a fairly elegant class based application with PHP. So like Immerse says 'why not dive in'?

    Regards,
    Steve
    Because finishing Release #2 and having a working site is more important than learning a new language in the 11th hour...

    OOP can wait a few months.


    Debbie

  13. #13
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    why would you just not simply use NOW() in your mysql insert? No PHP worries at all.
    Sorry, I am getting lost in what this - my own - thread is about?! (Been a long week...)

    In order to determine which color indicator to display for a User's Online Status, I need to compare the User's "last_activity" in MySQL with the current time when the User's Comments are displayed.

    I guess that could be done calculating the Time differences using either PHP or MySQL. (Personally, I'd prefer storing "last_activity" as a Date/Time Stamp so it is human-readable versus the Unix Time thingy.)

    If I wanted to do everything in MySQL, what Data-Type should I choose - asked above - and how would I go about comparing the Current Time to what is stored in MySQL in whatever format that would be?!

    Thanks,


    Debbie

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Personally, I'd prefer storing "last_activity" as a Date/Time Stamp so it is human-readable versus the Unix Time thingy.
    there you go, you almost made a choice

    as between DATETIME and TIMESTAMP, choose the former

    now all you have to do is make the comparison in php after extracting the last activity value

    you could also do it in the SELECT but that makes the query more clunky
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there you go, you almost made a choice

    as between DATETIME and TIMESTAMP, choose the former
    I don't understand what the difference is between DATETIME and TIMESTAMP.


    now all you have to do is make the comparison in php after extracting the last activity value

    you could also do it in the SELECT but that makes the query more clunky
    I don't know how to do that?!

    That is why I was leaning towards the Unix Time, because then it is just subtracting two numbers.

    How would I take a DATETIME value in my "last_activity" field in MySQL and compare it against the current time() and determine the difference in Minutes between the two?!

    Sorry, but the more I read online the more I get confused on this topic...


    Debbie

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Sorry, but the more I read online the more I get confused on this topic...
    well, this forum is online, so i don't know if i give you any information whether that will confuse you further

    but i am sure as shootin not going to read da manual for you

    the difference between DATETIME and TIMESTAMP is in da manual

    if you don't like reading it online, may i suggest you print it off

    calculating the difference between two datetimes in minutes is simple if you use one of more of the datetime functions (may i suggest TIMESTAMPDIFF, which is also documented in da manual)

    come on, debbie, you can do it, show a little independence and self-sufficiency

    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
  •