0

I'm having trouble with getting a timestamp difference.

I have a Table with a timestamp (Type is datetime). I want my query to output the difference from current time - timestamp.

I've tried various queries using the timediff() class. But for some odd reason I get a difference that is 8 or so hours in the future.

My Table setup is as follows

Table A
*--------------------------------*
| PK | Item | TimeStamp          |
|  1 | Apple| 2016-09-15 03:21:59|
*--------------------------------*

I would like the difference to output as such:

Output Table 
*--------------------------------*
| Item |      TimeStamp          |
| Apple|    0Days 18Hrs 45Min    |
*--------------------------------*

I've referenced a few other questions but didn't seem to work for me.

Like these:

Converting epoch number to human readable date in mysql

Is timestampdiff() in MySQL equivalent to datediff() in SQL Server?

I'm trying to get this result currently with a function and set both times up as variables.

Community
  • 1
  • 1
cpt-crunchy
  • 391
  • 4
  • 23
  • "I've tried various queries". Show at least one of them. We can't tell what you did wrong without seeing it. – Barmar Sep 15 '16 at 23:06

1 Answers1

0

I was able to get the result I wanted by setting up variables:

date_default_timezone_set ("America/Chicago");
$timenow = new DateTime(date("Y-m-d H:i:s"), new DateTimeZone("America/Chicago"));
while($row = mysqli_fetch_assoc($ticket)) {
    $tickettime         = new DateTime($row['ticketopen'], new DateTimeZone("America/Chicago"));
    $sincestart         = $tickettime->diff($timenow);
    $hours              = (int)$sincestart->h;
    $ticket_time        = strval($hours-12)." Hrs ".$sincestart->i." Min ";

Not sure if this was the best way to do this but it gave me the result I wanted. Hopefully this can help someone else.

Also, I had to subtract 12 hours in order to get the correct hour difference.

cpt-crunchy
  • 391
  • 4
  • 23