Search code examples
phpmysqldatetimetimefloor

Rounding a MYSQL datetime to earliest 15 minute interval in milliseconds (PHP)

I'm fetching a datetime from MYSQL which looks like:

2010-08-11 11:18:28

I need to convert it into the "floor" or the earliest 15 minute interval and output in milliseconds for another function.

So, this case would be:

2010-08-11 11:15:00 in milliseconds

Whoops! Sorry - need to clarify - I need code that will transform it into milliseconds WITHIN php!

Doing a timing test revealed the following:

$time_start = microtime(true);
for($i=0;$i<10000;$i++)
    floor(strtotime('2010-08-11 23:59:59')/(60*15))*60*15*1000;
$time_end = microtime(true);
echo 'time taken = '.($time_end - $time_start);

time taken = 21.440743207932

$time_start = microtime(true);
for($i=0;$i<10000;$i++)
    strtotime('2010-08-11 23:59:59')-(strtotime('2010-08-11 23:59:59') % 900);
$time_end = microtime(true);
echo 'time taken = '.($time_end - $time_start);

time taken = 39.597450017929

$time_start = microtime(true);
for($i=0;$i<10000;$i++)
    bcmul((strtotime('2010-08-11 23:59:59')-(strtotime('2010-08-11 23:59:59') % 900)), 1000);
$time_end = microtime(true);
echo 'time taken = '.($time_end - $time_start);

time taken = 42.297260046005

$time_start = microtime(true);
for($i=0;$i<10000;$i++)
    floor(strtotime('2010-08-11 23:59:59')/(900))*900000;
$time_end = microtime(true);
echo 'time taken = '.($time_end - $time_start);

time taken = 20.687357902527

time taken = 19.32729101181

time taken = 19.938629150391

It appears that the strtotime() function is a slow one and we probably should avoid using it doubly for every time its required. The timetaken(60*15) != timetaken(900) was a little bit of a surprise...

Solution

  • Would this work? Isn't there some function that would do this better?

    echo floor(strtotime('2010-08-11 11:18:28')/(60*15))*60*15*1000;
1281505500000
Wednesday, August 11, 2010 11:15:00 AM

echo floor(strtotime('2010-08-11 11:28:28')/(60*15))*60*15*1000;
1281505500000
Wednesday, August 11, 2010 11:15:00 AM

echo floor(strtotime('2010-08-11 00:00:00')/(60*15))*60*15*1000;
1281465000000
Wednesday, August 11, 2010 12:00:00 AM

echo floor(strtotime('2010-08-11 23:59:59')/(60*15))*60*15*1000;
1281550500000
Wednesday, August 11, 2010 11:45:00 PM