Solved

Convert mySQL timestamp to datetime, add 5 to time and subtract new time from old, give minutes and seconds left.

Posted on 2012-04-04
17
727 Views
Last Modified: 2012-04-07
I need to convert mySQL timestamp to datetime, add 5 minutes to time and subtract new time from old, give minutes and seconds left.

The mySQL timestamp looks like this,

2012-04-05 06:48:48

so I'd like to do

2012-04-05 06:53:48 - 2012-04-05 06:48:48 then output 5 minutes 0 seconds left and so on as the time counts down. I have tried all sorts of different variations without success. I'm using PHP Version 5.3.3. Any help will be much appreciated.

Thanks
0
Comment
Question by:JPERKS1985
  • 6
  • 6
  • 3
  • +1
17 Comments
 
LVL 7

Expert Comment

by:designatedinitializer
Comment Utility
Check this section of the MySQL documentation:
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Particularly the documentation for the unix_timestamp function:
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_unix-timestamp

If you do:

SELECT UNIX_TIMESTAMP(whatever_your_datetime_field_is_called)

...then you'll get the value as a Unix timestamp, which is the 'currency' ot the time functions in PHP.
Then you can use these PHP function to manipulate the dates:
http://pt.php.net/manual/en/ref.datetime.php
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Try this.  http://us3.php.net/manual/en/function.strtotime.php

$mytimestamp = "2012-04-05 06:48:48";
$myendtime = "2012-04-05 06:53:48";
$newtime = strtotime($mytimestamp);
$newend = strtotime($myendtime);
$timeleft = $newend - $newtime;
echo intval($timeleft/60)." minutes ".($timeleft % 60)." seconds";

Open in new window

0
 
LVL 1

Author Comment

by:JPERKS1985
Comment Utility
How would I add the 5 minutes to the first time stamp to get the "$myendtime" value?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
That's not hard but where are you going to store it?  PHP apps don't normally sit there and count down for you.

$newend = $newtime + (5*60);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Please read this.  While you do that I will try to see if there is a good answer to your question.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
In MySQL the term "timestamp" is a term of art.  It is used as a column data type, generally for the purpose of knowing when a row was last updated.  A string that looks like "2012-04-05 06:48:48" is an ISO-8601 format DATETIME string.  You may see TIMESTAMP columns displayed that way, irrespective of the way that MySQL stores the values internally.

I don't get this part of the question - when you add five minutes and subtract new from old, you get five minutes.  It's just arithmetic - I don't know what else you would expect.

To add five minutes to any ISO-8601 DATETIME string you would do something like this.
// DATETIME INFORMATION FROM AN EXTERNAL SOURCE
$old = '2012-04-05 06:48:48';
// TRY TO MAKE A TIMESTAMP
$ts = strtotime($old . ' + 5 minutes');
// IF THE TIMESTAMP FAILED
if (!$ts) die("$old IS BOGUS DATA");
// IF THE TIMESTAMP WORKED
$new = date('c', $ts);

Open in new window

0
 
LVL 1

Author Comment

by:JPERKS1985
Comment Utility
I have this code so far,

$newtime = strtotime($Time);
$newend = $newtime + (5*60);


$timeleft = $newend - $newtime;
echo intval($timeleft/60)." minutes ".($timeleft % 60)." seconds<BR>";


I should explain the countdown, this is for a PHP script which is refreshed every second using AJAX. The code up top always reads 5 minutes which leads me to believe the mySQL time stamp is not being handled properly?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
No, that's why I asked how you were storing the value.  PHP script finishes and the values disappear until it is loaded again.  For this to work, you have to store $newend somewhere the First time the script runs and retrieve it each time until the time goes to 0.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thought I had posted this, but I guess not... D'Oh.

The goal here is to send the timer value to the client machine and let the client do the countdown.  I have it set to seven seconds, but it could just as easily be set to anything.  To DaveBaldwin's point, the PHP script runs and the HTTP request is finished before the browser sees the output.  
<?php // RAY_countdown_timer.php
error_reporting(E_ALL);

// SET THE TIMER VALUE IN THE PHP SCRIPT
$timer = '7';
?>

<script type="text/javascript">
var interval = "";
var sekonds  = <?php echo $timer; ?>;

function startInterval()
{
    interval = window.setInterval("tTimer()",1000);
}
function stopInterval()
{
    window.clearInterval (interval);
    interval="";
}
function tTimer()
{
    document.thinkTime.tDisplay.value = sekonds--;
    if (sekonds == -1)
    {
        stopInterval();
    }
}
</script>

<script>
document.write('<meta http-equiv=\"Refresh\" content=\"<?php echo $timer; ?>;url=http://lmgtfy.com?q=JavaScript+Timer\">');
</script>

<form name="thinkTime">
This page will search for a JavaScript Timer in
<input readonly class="timerBox" size="1" type="text" name="tDisplay" value="">
<script>startInterval();</script>
seconds.
</form>
<a href="http://lmgtfy.com?q=JavaScript+Timer">Click here if you don't want to wait.</a>

Open in new window

0
 
LVL 1

Author Comment

by:JPERKS1985
Comment Utility
Normally I would, however there is going to be several hundred count downs. I don't trust java script to that extent, not to mention I need to do an ajax refresh either way. The code below works great until the timer hits zero then it starts counting down from 59 minutes instead of 5 where I need it to just say 0 Seconds Remaining. Can we fix this?


$StartTime = strtotime($Time);


$request_date = $StartTime;
$expiration  = '5 minutes';
$future_time = strtotime("+" . $expiration, $request_date);
$future_time2 = $request_date;

$timeleft = sec2hms($timeleft);







function sec2hms ($sec, $padHours = false)
  {

    // start with a blank string
    $hms = "";
   
    // do the hours first: there are 3600 seconds in an hour, so if we divide
    // the total number of seconds by 3600 and throw away the remainder, we're
    // left with the number of hours in those seconds
    $hours = intval(intval($sec) / 3600);

    // add hours to $hms (with a leading 0 if asked for)

    // dividing the total seconds by 60 will give us the number of minutes
    // in total, but we're interested in *minutes past the hour* and to get
    // this, we have to divide by 60 again and then use the remainder
    $minutes = intval(($sec / 60) % 60);

    // add minutes to $hms (with a leading 0 if needed)
    $hms .= str_pad($minutes, 2, "0", STR_PAD_LEFT). " Minutes, ";

    // seconds past the minute are found by dividing the total number of seconds
    // by 60 and using the remainder
    $seconds = intval($sec % 60);

    // add seconds to $hms (with a leading 0 if needed)
    $hms .= str_pad($seconds, 2, "0", STR_PAD_LEFT)." Seconds";

    // done!
    return $hms;
   
  }
0
 
LVL 1

Author Comment

by:JPERKS1985
Comment Utility
In response to Dave the start time is stored in a mysql timestamp that's pulled from the database each refresh of the PHP script so that value does not change.
0
 
LVL 7

Assisted Solution

by:designatedinitializer
designatedinitializer earned 250 total points
Comment Utility
Let us follow the KISS principle:

<?php
$time_from_db = '2012-04-06 21:48:48';
echo timeHMS(strtotime($time_from_db)+5*60 - time());

function timeHMS($seconds, $min=0, $max=359999){
	$seconds = (int) $seconds;
	// Lower limit
	if($seconds<$min) $seconds = $min;
	// Higher limit
	if($seconds>$max) $seconds = $max;
	// do the math
	$hours   = floor($seconds / 3600);
	$minutes = floor(($seconds-$hours*3600) / 60);
	$seconds = $seconds-$minutes*60-$hours*3600;
	// format the output
	$remaining = str_pad($hours, 2, "0", STR_PAD_LEFT);
	$remaining.= ":".str_pad($minutes, 2, "0", STR_PAD_LEFT);
	$remaining.= ":".str_pad($seconds, 2, "0", STR_PAD_LEFT);
	return $remaining;
}
?>

Open in new window


There you go.
This is clipping the values at the lower and the higher end.
From 0 to 99 hours, 59 minutes and 59 seconds.

PS: Notice that in the code you last posted, $timeleft never gets initialized. You didn't pasted the code right.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I'm not sure where we are on this question now.  If you want to format the DATE or TIME information the date() function works pretty well.
<?php // RAY_temp_jperks.php
error_reporting(E_ALL);

// REQUIRED FOR PHP 5.1+
date_default_timezone_set('America/Chicago');

function RAY_sec2hms($seconds, $format='H:i:s')
{
    return date($format, strtotime('Today + ' . $seconds . 'Seconds'));
}

echo '<br/>' . RAY_sec2hms(3599);
echo '<br/>' . RAY_sec2hms(3600);
echo '<br/>' . RAY_sec2hms(3601);
echo '<br/>' . RAY_sec2hms(180);
echo '<br/>' . RAY_sec2hms(179);
echo '<br/>' . RAY_sec2hms(86399);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
This seems to work OK for a session-based countdown timer.   You can use this design pattern for your backend script that is called via AJAX.  A couple of notes are in order.  The amount of time is set at line 11.  You could also pass this to the script via a GET request by using a URL argument.  The key for the countdown timer is set at line 14.  This uses the REQUEST_URI variable so that the URL arguments become part of the md5() string.  By doing this you can uniquely identify multiple timers - just set the URL arguments in a way that makes each timer unique.
<?php // RAY_temp_jperks.php
error_reporting(E_ALL);

// SESSION-BASED COUNTDOWN TIMER
session_start();

// REQUIRED FOR PHP 5.1+
date_default_timezone_set('America/Chicago');

// HOW MANY SECONDS FOR THE COUNTDOWN
$sec = 30;

// MAKE A KEY BASED ON THE URI (INCLUDES URL VARIABLES)
$key = md5($_SERVER['REQUEST_URI']);

// GET THE TIME IN UNIX EPOCH SECONDS
$now = time();

// DO WE HAVE ANY TIMERS?
if (!array_key_exists('timers', $_SESSION)) $_SESSION['timers'] = array();

// IS THIS TIMER ALREADY RUNNING?
if (array_key_exists($key, $_SESSION['timers']))
{
    $fut = $_SESSION['timers'][$key];
    $lap = $fut - $now;
    if ($lap >  0) echo RAY_sec2hms($lap) . ' Remains';
    if ($lap <= 0) echo 'Expired at '. date('c', $fut);
}

// IF THE TIMER IS NOT STARTED YET, START IT
else
{
    $_SESSION['timers'][$key] = $now + $sec;
    echo 'Timer ' . RAY_sec2hms($sec) . ' Started';
}

// A FUNCTION TO FORMAT THE REMAINING TIME
function RAY_sec2hms($seconds, $format='H:i:s')
{
    return date($format, strtotime('Today + ' . $seconds . 'Seconds'));
}

Open in new window

HTH, ~Ray
0
 
LVL 1

Author Comment

by:JPERKS1985
Comment Utility
The code works fine that designatedinitializer posted except it starts at 06:05:59 and then goes down to 05:59:59 instead of starting at 00:05:00 and just going to 00:00:00. That's the last part that needs to be worked out. We only use the mySQL stored timestamp to see if the item has expired yet from that five minute mark.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
Comment Utility
... code works fine ... except ...
In computer science we usually write "the output from the code does not meet the specification."

Please install and test the script posted at ID: 37817507.  Please adjust the time on line 11.  If you want it to say '00:00:00' you can change the output message on line 28.  

If you want to take a step back from the narrow specification of this question and tell us why there would "be several hundred count downs" and whether these are for one client or many clients, etc. we may be able to help with a more commonly understood design pattern.  If you decide to do that, please post a new question here at EE.  If you can show us a web site that does something like what you're trying to implement, please post a link to that site when you post the new question.

Thanks and regards, ~Ray
0
 
LVL 1

Author Comment

by:JPERKS1985
Comment Utility
It looks like it was a time zone problem. Thanks guys.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now