SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist stonedeft's Avatar
    Join Date
    Aug 2009
    Posts
    589
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    php subtract current time from string

    Hello forums

    How do I substract the current time from a string and return me the number of hours.

    PHP Code:
    $t '2010-05-06 15:33:45';
    $s =      strtotime($t);
    $h floor(time() - $s 60 );
    echo  
    $h
    ^ didn't work
    Don't Panic

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    time() returns a unix timestamp, which is represents seconds.
    --How many seconds are in an hour?

    Like when you do math on paper, certain arithmetic operators have higher precedence, and must be evaluated first unless parenthesis are used to force the order of operations.

    6 - 4 / 2 = ?
    --What's the correct result? 4 or 1?

  3. #3
    SitePoint Evangelist stonedeft's Avatar
    Join Date
    Aug 2009
    Posts
    589
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    time() returns a unix timestamp, which is represents seconds.
    --How many seconds are in an hour?

    Like when you do math on paper, certain arithmetic operators have higher precedence, and must be evaluated first unless parenthesis are used to force the order of operations.

    6 - 4 / 2 = ?
    --What's the correct result? 4 or 1?
    Ok I see however how do I evaluate arithmetic operators inside a mysql query. Here's my scenario:



    I want to get the hour difference of s_in and s_out from the table and insert it to the column hours_today
    here's my query to get the seconds difference of the two columns

    PHP Code:
    $q mysql_query("SELECT UNIX_TIMESTAMP(s_out) - UNIX_TIMESTAMP(s_in) AS dif FROM sd_record WHERE id = 1"); 
    and to convert it to hours I need to : floor(dif / (60 * 60)) it. How then can I do that?

    Tnx
    Don't Panic

  4. #4
    SitePoint Evangelist stonedeft's Avatar
    Join Date
    Aug 2009
    Posts
    589
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I might need a new thread for this over at mysql forums
    Don't Panic

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT FLOOR(
            (UNIX_TIMESTAMP(s_out) - UNIX_TIMESTAMP(s_in)) / 3600
           ) AS hrs
      FROM ...
    But, most databases have a lot of date and time manipulation capabilities. Another one of many ways

    Code:
    SELECT HOUR(TIMEDIFF(s_out, s_in)) AS hrs
      FROM ...

    If you wanna update the whole table
    Code:
    UPDATE sometable
       SET hours_today = HOUR(TIMEDIFF(s_out, s_in))
    The column seems a bit redundant though, because it stores information that already exists(just needs to be calculated).

  6. #6
    SitePoint Evangelist stonedeft's Avatar
    Join Date
    Aug 2009
    Posts
    589
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    [code]
    The column seems a bit redundant though, because it stores information that already exists(just needs to be calculated).
    I need to get the total number of hours by adding all the rows in the hours_today latter

    Anyway I, trying to make your second query work but it's throwing me syntax errors:

    $q = mysql_query("SELECT HOUR(TIMEDIF(s_out, s_in)) AS hrs FROM sd_record WHERE id = 1");
    Don't Panic

  7. #7
    SitePoint Evangelist stonedeft's Avatar
    Join Date
    Aug 2009
    Posts
    589
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops sori I just need to remove the extra space TIMEDIFF(s_out,s_in)

    I see that this one is much better. it will enter the hour including the mins and seconds

    Code MySQL:
    UPDATE sometable
       SET hours_today = HOUR(TIMEDIFF(s_out, s_in))


    Question: How then can I get the total number of hours in all the rows in the hours_today column?

    TNX
    Don't Panic

  8. #8
    SitePoint Evangelist stonedeft's Avatar
    Join Date
    Aug 2009
    Posts
    589
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK here's to get the total

    Code SQL:
    $q = mysql_query("SELECT SUM(hours_today) AS tot FROM sd_record");

    Thanx crmalibu you are a great help
    Don't Panic

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT SUM(hours_today) AS total
    or
    Code:
    SELECT SUM(HOUR(TIMEDIFF(s_out, s_in))) AS total


Tags for this Thread

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
  •