Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2010
    Posts
    20

    Unanswered: Difference between actual date and past date

    Hello everybody,

    I'm writing in this forum to ask some advice about do a difference between two dates.
    I've seen a lot of material about this online, but I've not found what could be the better solution for me...

    What I would like to get is the difference between the actual date-time and a past date-time... so something like "5 minutes ago", "1 hour ago", "1 day ago", etc.

    In my table I've at the moment 5 columns: id, cat, rif, comment, date (datetime).
    In the 'date' fields I have recorded the time when the comment has been sent.

    Now, before to become too much crazy I was asking me what could be the better way to proceed...
    For example I've seen the DATEDIFF function, but how do I use it in my case?

    Thanks a lot for your time

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    handle it in your front end

    if you do want to do it on the server you will ave to return the number of units (timediff looks promising) bear in mind datedff returns the difference between two dates.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    a better solution would be to use UNIX_TIMESTAMP function to convert the date time value into a value representing the number of seconds since UNIX epoch seconds since '1970-01-01 00:00:00' UTC. Comparing the difference between these returns the number of seconds which can be converted to whatever you want.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Jul 2010
    Posts
    20
    I was doing some trial with the UNIX_TIMESTAMP, but I don't understand how to do... this the code I put down but I get a syntax error: "... check the manual that corresponds to your MySQL server version for the right syntax to use near '10:40:16) - UNIX_TIMESTAMP(2010-09-29 18:38:16)' at line 1"

    $date = date('Y-m-d H:i:s',time());
    $selData = "SELECT dataComm FROM comments";
    $queryData = @mysql_query("$selData",$link) or die("Errore query database: " . mysql_error());
    while ($rowData = mysql_fetch_array($queryData, MYSQL_NUM)) {
    $diffTime = "SELECT UNIX_TIMESTAMP($date) - UNIX_TIMESTAMP($rowData[0]) ";
    @mysql_query("$diffTime",$link) or die("Errore query database: " . mysql_error());
    echo $diffTime;
    }

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so is this now a MySQL problem or a PHP problem?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jul 2010
    Posts
    20
    It is a MySQL problem: Errore query database: You have an error in your SQL syntax;

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by supadema View Post
    In my table I've at the moment 5 columns: id, cat, rif, comment, date (datetime).
    Code:
    SELECT UNIX_TIMESTAMP -
           UNIX_TIMESTAMP(daTable.date) AS difference_in_seconds
      FROM daTable
    and now it's a php problem

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2010
    Posts
    20
    Unluckly is still a MySQL problem...

    I've tried your code but I get this error:
    Errore query database: Unknown column 'UNIX_TIMESTAMP' in 'field list'

    So I've tried to add a column called dateNow (type: TIMESTAMP, default: CURRENT_TIMESTAMP) in my database and change the code like this:

    $diffTime = "SELECT UNIX_TIMESTAMP(comments.dateNow) - UNIX_TIMESTAMP(comments.dataComm) AS difference_in_seconds FROM comments;";

    $diffSec = $diffTime['difference_in_seconds'];
    echo $diffSec;

    Like this I don't get any error, but neither I get my result...
    "echo $diffSec;" returns this: "SSSSSS"


  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    here are a few ways in which this should be called. The last entry reproduces your error. Make sure that this is being called correctly.

    Code:
    mysql> select unix_timestamp();
    +------------------+
    | unix_timestamp() |
    +------------------+
    |       1285933281 | 
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> select unix_timestamp() - unix_timestamp('2007-11-30 00:00:00');
    +----------------------------------------------------------+
    | unix_timestamp() - unix_timestamp('2007-11-30 00:00:00') |
    +----------------------------------------------------------+
    |                                                 89556115 | 
    +----------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select unix_timestamp - unix_timestamp('2007-11-30 00:00:00');
    ERROR 1054 (42S22): Unknown column 'unix_timestamp' in 'field list'
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  10. #10
    Join Date
    Jul 2010
    Posts
    20
    That's right, with "unix_timestamp()" instead of "unix_timestamp" I don't get anymore that error... but I don't understand what is wrong, because I always get "S" as result!!

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by supadema View Post
    but I don't understand what is wrong, because I always get "S" as result!!
    try running your query outside of php

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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