views:

65

answers:

4

Hi,

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...

A: 

Give this a try and see what happens:

select DATE(myColumn) 
       + CONVERT(HOUR(myColumn),CHAR(2)) + ':'
       + CASE 
         WHEN MINUTE(myColumn) < 15 THEN '00'
         WHEN MINUTE(myColumn) < 30 THEN '15'
         WHEN MINUTE(myColumn) < 45 THEN '30'
         ELSE '45'
       END 
       + ':00' as myDate
Fosco
A: 

Ok, let's see the worst solution:

<?php

$ts    = explode(' ', '2010-08-11 11:18:28');
$date  = explode('-', $ts[0]);
$time  = explode(':', $ts[1]);
$ts    = mktime($time[0], $time[1], $time[2], $date[1], $date[2], $date[0]);
$floor = mktime($time[0], round($time[1]/15)*15, 0, $date[1], $date[2], $date[0]);
fabrik
+1  A: 

The following will give you the earliest 15 minute interval :

select UNIX_TIMESTAMP(YOURDATETIME)-MOD(UNIX_TIMESTAMP(YOURDATETIME), 900) 
from YOURTABLE

The result is in epoch seconds (seconds since '1970-01-01 00:00:00' UTC), if you want that value in milliseconds you should multiply by 1000 (either in the query or in PHP, as you see fit).

EDIT

In PHP for your set of values that would be :

$values = array('2010-08-11 11:18:28', '2010-08-11 11:28:28', '2010-08-11 00:00:00', '2010-08-11 23:59:59');
foreach($values as $value) {
    $result = (strtotime($value)-(strtotime($value) % 900));
    echo "$value -> $result\n";
}

When you multiply $result by 1000 (to get the value in ms) you will probably get an overflow and the result will be converted to a float. This is probably not what you want, so you might be better of using bcmul :

$result = bcmul((strtotime($value)-(strtotime($value) % 900)), 1000);
wimvds
Hi! That looks rad for mysql, is there a smart way like this to do the same in php?
DrMHC
Sure, see my edit.
wimvds
A: 

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
DrMHC