problem with time manipulation

**Andy Hassall** · Jul 16 '05, 10:42 PM

Re: problem with time manipulation

On Tue, 08 Jul 2003 16:26:02 -0500, Marcus <JumpMan222@aol .com> wrote:
[color=blue]
>I am having some problems with trying to perform calculations on time
>fields. Say I have a start time and an end time, 1:00:00 and 2:30:00
>(on a 24 hour scale, not 12). I want to find the difference in minutes,
>divide this result by a predefined size of interval, and make a loop
>that runs this many times. For example, with an interval size of 15
>minutes, it will return 6 blocks... I have this all working fine, but am
>getting stuck on the next part.
>
>Given the above assumptions, I want to insert 6 tuples into my MySQL
>database:
>
>start end
>1:00:00 1:15:00
>1:15:00 1:30:00
>1:30:00 1:45:00
>1:45:00 2:00:00
>2:00:00 2:15:00
>2:15:00 2:30:00
>
>This is where it gets kind of tricky... I can accomplish for the most
>part what I want with a lot of checks through if/else statements. Right
>now I pull out the minute portion, check to see this value plus the
>interval becomes greater than or equal to 60, and if it does I set it
>equal to "00" and increment the hour portion... otherwise I simply add
>the interval to the current minute value. The problem is this is
>getting very messy and I keep finding new checks that I have to make...
>I have been all over the web trying to find a solution but haven't had
>any luck yet... I also checked out mysql related sites but since this is
>all happening before querying the database, it's more of a PHP related
>issue... there has to be some sort of functionality built in that allows
>comparision of two times, i.e. just adding 15 minutes through a loop and
>have the time formatted for you. Thanks so much in advance if anyone
>knows of how to do this, or of a more efficient way to do what I'm trying.[/color]

Use UNIX timestamps; they're an integer, which is the number of seconds since
Jan 1st 1970.

Adding x minutes then becomes trivial; add 15*60. Splitting it up into
sections is also easier; just take the difference (giving you seconds between
the two date/times), and divide by the number of blocks.

Then use date() to format them back to text for insertion into MySQL, or use
FROM_UNIXTIME($ unix_timestamp) in the SQL to get MySQL to do it for you.

PHP: mktime - Manual

http://uk.php.net/mktime

Get Unix timestamp for a date

PHP: date - Manual

http://uk.php.net/date

Format a Unix timestamp

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

**Zac Hester** · Jul 16 '05, 10:42 PM

Re: problem with time manipulation

Marcus,

You might consider converting everything to UNIX timestamps. It saves a
world of hurt in almost every aspect of time/date manipulation.

The UNIX timestamp is just an integer of the number of seconds since January
1st, 1970. Therefore, you can add, subtract, divide, multiply to your
heart's content and keep it accurate down to a second. There's no need to
do any kind of sanity checks on the integrity of the data since the PHP
date() and mktime() functions do all of that for you. The mktime() function
is the most useful since it allows some seriously easy time/date arithmatic
using any combination of time units (ie. adding two months to 30 seconds).

In some of my databases, I stopped using the SQL ISO date, time, and
datetime column types. Instead I just allocate a 12-place integer and drop
the timestamp in the field from either PHP or MySQL (since they both
understand timestamps).

In your situation, you could simply take two times and build a list of
timestamps between the time fields at a given interval:

function save_intervals( $start_time, $end_time, $interval) {

//Find number of intervals.
$num_intervals = floor(($end_tim e - $start_time) / $interval);

//Build a list of times.
$buffer_time = $start_time;
for($i = 0; $i < $num_intervals; $i++) {
$times[$i]['start'] = $buffer_time;
$times[$i]['end'] = $buffer_time += $interval;
}

//Do whatever with the "times" array...
}

In this case, if you passed in the arguments as timestamps (seconds), you
would have an array full of entries in timestamps:

save_intervals( mktime(1, 0, 0, 1, 1, 1970), mktime(2, 30, 0, 1, 1, 1970),
60*15);
//This produces the same list of times you have in your example.

Either store the timestamps directly in your database, or format them into
ISO time fields for MySQL:

$iso_time = date('H:i:s', $times[0]['start']);

If you ever want to make a query against a raw timestamp, just have PHP
figure out what stamp you need for your purposes:

$oldest_record = mktime(15, 55, 0, 7, 8, 2003);
$query = "select * from table_name where time_field >= $oldest_record" ;

The great thing about this system, is that the mktime() function
automatically corrects for things like adding 5 seconds to 56 seconds in the
seconds argument or even spanning leap year issues and other bothersome
time/date manipulation tasks.

Required reading:

http://us4.php.net/manual/en/function.date.php

http://us4.php.net/manual/en/function.mktime.php

I wish I was told how easy manipulating timestamps was when I first started
working with PHP/MySQL.

HTH,
Zac

"Marcus" <JumpMan222@aol .com> wrote in message
news:3F0B36EA.6 050000@aol.com. ..[color=blue]
> I am having some problems with trying to perform calculations on time
> fields. Say I have a start time and an end time, 1:00:00 and 2:30:00
> (on a 24 hour scale, not 12). I want to find the difference in minutes,
> divide this result by a predefined size of interval, and make a loop
> that runs this many times. For example, with an interval size of 15
> minutes, it will return 6 blocks... I have this all working fine, but am
> getting stuck on the next part.
>
> Given the above assumptions, I want to insert 6 tuples into my MySQL
> database:
>
> start end
> 1:00:00 1:15:00
> 1:15:00 1:30:00
> 1:30:00 1:45:00
> 1:45:00 2:00:00
> 2:00:00 2:15:00
> 2:15:00 2:30:00
>
> This is where it gets kind of tricky... I can accomplish for the most
> part what I want with a lot of checks through if/else statements. Right
> now I pull out the minute portion, check to see this value plus the
> interval becomes greater than or equal to 60, and if it does I set it
> equal to "00" and increment the hour portion... otherwise I simply add
> the interval to the current minute value. The problem is this is
> getting very messy and I keep finding new checks that I have to make...
> I have been all over the web trying to find a solution but haven't had
> any luck yet... I also checked out mysql related sites but since this is
> all happening before querying the database, it's more of a PHP related
> issue... there has to be some sort of functionality built in that allows
> comparision of two times, i.e. just adding 15 minutes through a loop and
> have the time formatted for you. Thanks so much in advance if anyone
> knows of how to do this, or of a more efficient way to do what I'm trying.
>
> Marcus
>[/color]

problem with time manipulation

problem with time manipulation

Comment

Comment