SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    time difference between database value and NOW()

    Hello,

    Using NOW() i am updating one of my column value.

    and it is getting stored something like this.
    2009-05-25 14:56:29
    Now i want to check this coulmn value should not be more than 15 minute.
    so i was using this

    $time = time() - (60*15);
    echo time();

    but when i echo time. it is giving me something like this
    1243245041. that i don't want so i used there NOW() but that is giving me fatal error.

    so can anyone tell me how can i compare my database value with php. so i can probably get the 15 minutes. difference??

    thanks in advance.

  2. #2
    SitePoint Zealot adam.jimenez's Avatar
    Join Date
    May 2009
    Location
    Ware, UK
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    u could do this when u select from mysql

    PHP Code:
    select UNIX_TIMESTAMP(date_column) AS 'date' FROM table 
    ...

    or let mysql do the heavy-lifting by using SUBTIME

    http://dev.mysql.com/doc/refman/5.1/...nction_subtime

  3. #3
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your reply adam.jimenez,

    I dont want to select record yet. want to update one column value(status) depends on(last_access) another column.

    so i need to calculate the time difference.

    can anyone help me?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by deepson2 View Post
    can anyone help me?
    sure

    please show your table layout, using SHOW CREATE TABLE

    then please explain what you want to update
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hello r937,

    here is my table structure.

    CREATE TABLE `author` (
    `id` int(11) NOT NULL,
    `visitor_id` int(11) NOT NULL,
    `last_access` datetime NOT NULL default '0000-00-00 00:00:00',
    `status` Enum('0','1' )NOT NULL default 0,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


    Firstly my last_access column is updating with each logs in.

    1)then as soon as it's updating i want update my status column to 1 as user is online.

    2) calculate the time since logged in from current time if it is more than 15 minute. update status to 0 again.

    I want to show the user's status whether he/she is online or not. do you think this method is feasible?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sure, everything is feasible

    however, i would show whether the user is online without using a status column -- just select the last login time and calculate the time difference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just select the last login time and calculate the time difference
    coluld you please tell me. how can i do that?

    is somthing like this could be done.
    Code:
     f(TIMESTAMPDIFF(MINUTE,last_access, NOW() <=15))
    {
    online
    }else{
    offline}
    
    I am not sure about it. because with every refresh this timediff is starting again from the zero.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT id
         , visitor_id
         , last_access
         , CASE WHEN TIMESTAMPDIFF( MINUTE
                                  , last_access
                                  , CURRENT_TIMESTAMP ) <=15
                THEN 'online'
                ELSE 'offline' END AS status
      FROM author
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru deepM's Avatar
    Join Date
    Dec 2007
    Location
    India
    Posts
    705
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, you are the king of sql

    Its working. after 15 minutes its showing me user is offline.

    This was my second condition actually to check user is still online or not. but if i have to show that user is forever online(like sitepoint shows) till user himself dont do logout. so can we check that with query?

    or i have to make it work with php(destroying session)


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
  •