By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,151 Members | 1,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,151 IT Pros & Developers. It's quick & easy.

problem with time manipulation

P: n/a
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

Jul 16 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Tue, 08 Jul 2003 16:26:02 -0500, Marcus <Ju********@aol.com> wrote:
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.


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.

http://uk.php.net/mktime
http://uk.php.net/date

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Jul 16 '05 #2

P: n/a
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_time - $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" <Ju********@aol.com> wrote in message
news:3F**************@aol.com...
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

Jul 16 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.