Re: [PHP-DB] Date operations.
Garry,
CREATE TABLE breeding (
rec SMALLINT(4) UNSIGNED AUTO_INCREMENT,
sire SMALLINT(4) UNSIGNED,
dam SMALLINT(4) UNSIGNED,
paired DATE,
split DATE,
num_offspring TINYINT(1) ZEROFILL,
PRIMARY KEY (rec)
);
First, we define if a pair was breeding for a given date by seeing if that
date lies between the paired date and the split date. In mysql:
SELECT FROM breeding WHERE TO_DAYS(paired) = TO_DAYS('$given_date') AND
TO_DAYS('$given_date') = TO_DAYS(split);
=This seems part of the problem (as well as part of the solution). Procedural logic
suggests that we should
iterate through the days of the month, taking note of the facts, and then perform the
statistical calculation at
the end. Relational logic doesn't work like this, it requires us to process through
the data in the table(s).
This table is not organised by date, indeed it notes periods of time, which are
notoriously problematic to sort
into a 'date order'! However all is not lost...
There is no need to use TO_DAYS() because paired and split are both
dates so you can operate on them directly. I recommend that you take a
look at the manual's section on Time and Date Functions.
You are right. I works without the TO_DAYS(). But I did get them from the
manual :-)
=thanks for the note. It's difficult to know whether to risk insulting someone by
saying RTFM!
=ironically I think you're going to end up right back there, but other functions of
interest may include Month,
MonthName, Extract, Date_Sub, and even Timestamp.
=general advice would be to define the dates in PHP (with MySQL in mind), but keep all
the date processing in
MySQL (if at all possible).
Where it gets messy is this has to be put into a loop, between the given
start and end dates (year and month only). For each day in the loop, I
have to do a query and count the number returned to php. Then average that
figure for the month.
So if I understand correctly, you are thinking in terms of a 'result
table' which would (ideally) have the days 1-28/29/30/31 in the
left-hand column, and the number of pairs who were put together on
that day (paired = day/date = split). Then at the bottom you want to
sum the right-hand column and divide it by the number of days in the
month to give some sort of average mating possibilities per day. Does
that sound right?
Basically for statistical reasons I need average pairs per month, just a
single number. But the months/years to be queried on would be input via a
form. I was thinking of generating the data on-the-fly, but as you suggest
above and from something Frank Flynn suggested, I would be better off
having a separate table with monthly results. They could all be
pre-calculated except the current month.
=I noted Frank's suggestion. Obviously no one wants to duplicate 'the calendar'
unnecessarily, but by having a
calendar table, it does open up the possibility of processing through that table (from
start-date to end-date)
and using relational logic to extract (date) 'related' rows from the breeding tbl. In
many ways, when I have
finished, you will recognise that there is some attraction to that approach!
=You may have missed one of the points that I was making: what is the math behind this
calculation? I'm going to
set up some sample data/an approach, but only talk about a 'week' rather than a
'month' for brevity/ease of
comprehension... So if we have breeding pairs A through E, the breeding tbl
(representative) looks like:
sire dam paired split
Am Af Mon Fri
Bm Bf Tue Thu
Cm Cf Tue Fri
Dm Df history Sun
Em Ef Sat future
where m=male, f=female, and we're only interested in the period Mon-Fri, so rows D and
E are included only to
show exclusion (yes, that last clause does make logical sense...I think - they're
included only so that they can
be excluded!)
=Our first thought is to think in terms of say Monday, and to scan the breeding tbl
to count how many pairs
were breeding that day, and make a note of the count; then move to Tuesday and do
the same; finally summing
and averaging.
=Thus 'temporal logic' (working one day at a time) says:
Mon = 1 pair
Tue = 3 pairs
Wed = 3 pairs
Thu = 3 pairs
Fri = 2 pairs
Sum = 12
nDays = 5
Avg = 2.5
=Now, crucial question: have I understood the logic of the computation correctly? I
shall blythly carry on
regardless...
=Ok, so now we're in a quandry because constructing a SQL statement to look at Monday,
the Tuesday, ... is not
possible - enter Frank (trumpet calls offstage, left).
=However there's more than one way to skin a cat, and according to my (admittedly not
fantastic) math ability,
we could arrive at the same numbers by coming at things the other way around! Let's
count the number of days for
which a pair were breeding:
A = 5
B = 3
C = 4
D = ?
E = ?
Sum = 12
nDays = 5
Avg = 2.5
Proceed directly to Go!
=Let's ignore the ? rows for a
Re: [PHP-DB] Date operations.
Wow Garry, I know that I like to kick-start my brain by getting 'into' a list problem/challenge in the (London) mornings, but today I'm completely beaten. Thanks for the broad explanation, which is probably quite meaningful, but I'm not into breeding (could that statement be misunderstood?) and so don't understand the terminology, but let's soldier on... Can I request some db-speak please? You already have a tbl (or more). So let's start by asking for the table definitions Do you already have some SQL as a 'first attempt'? Could you also post that, together with a note about its short-comings/what needs to be added? That would also assist (my, feeble) comprehension. Usually in this sort of problem, the trick is to work out how to order/group the data, and then bearing in mind the various table-relationships, organise the join(s) and apply the requisite statistical functions. (that's the fun part) (if you haven't got anything started yet) Along with the definitions, how about starting a SELECT by listing the data you want to see, filling in the FROM clause, and then skipping to the WHERE clause and putting in the last or last-two criteria, eg the year/date-range to be used in the analysis - well do what you can/makes sense to you, so far. That might be enough to 'inspire' a solution - or start us on the way... Please advise, =dn Hi All, I have a problem working out a suitable algorithm either in PHP or MySQL. Basically I have a DB that keeps track of breeding records. Each record has a paired data, and a split-up date. I need to generate some statistics to work out average numbers of pairs per month, averaged on a daily basis, for a given start and stop date, typically a year or year-to-date. All the algorithms I can think of are messy, where I have to loop through all the breeding records for every day of the year, and count how many pairs are breeding by seeing if the date is between the start and stop dates, and then average that on a monthly basis. I can't see that scaling very well, as there might be several hundred breeding records for a given year, multiplied by 365 days. Has anyone any hints/pointers for an efficient way to do this? Regards, Garry. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Date operations.
On Wed, 30 Jan 2002, DL Neil wrote:
Can I request some db-speak please? You already have a tbl (or more).
So let's start by asking for the table definitions
The table definition is as follows:
CREATE TABLE breeding (
rec SMALLINT(4) UNSIGNED AUTO_INCREMENT,
sire SMALLINT(4) UNSIGNED,
dam SMALLINT(4) UNSIGNED,
paired DATE,
split DATE,
num_offspring TINYINT(1) ZEROFILL,
PRIMARY KEY (rec)
);
Do you already have some SQL as a 'first attempt'? Could you also post
that, together with a note about its short-comings/what needs to be
added? That would also assist (my, feeble) comprehension.
First, we define if a pair was breeding for a given date by seeing if that
date lies between the paired date and the split date. In mysql:
SELECT FROM breeding WHERE TO_DAYS(paired) = TO_DAYS('$given_date') AND
TO_DAYS('$given_date') = TO_DAYS(split);
Where it gets messy is this has to be put into a loop, between the given
start and end dates (year and month only). For each day in the loop, I
have to do a query and count the number returned to php. Then average that
figure for the month.
Normally start and end dates would be input via a form.
I was wondering if anyone had done that sort of thing, or whether there
was a more efficient way of doing it than stepping through the database
one day at a time.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Date operations.
Garry,
Could you please explain (define) in one sentence(paragraph) what end
result you want. It might be possible to handle it in one SQL statement.
Miles Thompson
At 09:54 AM 1/30/2002 +1100, Garry Optland wrote:
On Wed, 30 Jan 2002, DL Neil wrote:
Can I request some db-speak please? You already have a tbl (or more).
So let's start by asking for the table definitions
The table definition is as follows:
CREATE TABLE breeding (
rec SMALLINT(4) UNSIGNED AUTO_INCREMENT,
sire SMALLINT(4) UNSIGNED,
dam SMALLINT(4) UNSIGNED,
paired DATE,
split DATE,
num_offspring TINYINT(1) ZEROFILL,
PRIMARY KEY (rec)
);
Do you already have some SQL as a 'first attempt'? Could you also post
that, together with a note about its short-comings/what needs to be
added? That would also assist (my, feeble) comprehension.
First, we define if a pair was breeding for a given date by seeing if that
date lies between the paired date and the split date. In mysql:
SELECT FROM breeding WHERE TO_DAYS(paired) = TO_DAYS('$given_date') AND
TO_DAYS('$given_date') = TO_DAYS(split);
Where it gets messy is this has to be put into a loop, between the given
start and end dates (year and month only). For each day in the loop, I
have to do a query and count the number returned to php. Then average that
figure for the month.
Normally start and end dates would be input via a form.
I was wondering if anyone had done that sort of thing, or whether there
was a more efficient way of doing it than stepping through the database
one day at a time.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Date operations.
On Thu, 31 Jan 2002, DL Neil wrote:
Garry,
I regret to say that I can't see an immediate answer.
CREATE TABLE breeding (
rec SMALLINT(4) UNSIGNED AUTO_INCREMENT,
sire SMALLINT(4) UNSIGNED,
dam SMALLINT(4) UNSIGNED,
paired DATE,
split DATE,
num_offspring TINYINT(1) ZEROFILL,
PRIMARY KEY (rec)
);
First, we define if a pair was breeding for a given date by seeing if that
date lies between the paired date and the split date. In mysql:
SELECT FROM breeding WHERE TO_DAYS(paired) = TO_DAYS('$given_date') AND
TO_DAYS('$given_date') = TO_DAYS(split);
There is no need to use TO_DAYS() because paired and split are both
dates so you can operate on them directly. I recommend that you take a
look at the manual's section on Time and Date Functions.
You are right. I works without the TO_DAYS(). But I did get them from the
manual :-)
Where it gets messy is this has to be put into a loop, between the given
start and end dates (year and month only). For each day in the loop, I
have to do a query and count the number returned to php. Then average that
figure for the month.
So if I understand correctly, you are thinking in terms of a 'result
table' which would (ideally) have the days 1-28/29/30/31 in the
left-hand column, and the number of pairs who were put together on
that day (paired = day/date = split). Then at the bottom you want to
sum the right-hand column and divide it by the number of days in the
month to give some sort of average mating possibilities per day. Does
that sound right?
Basically for statistical reasons I need average pairs per month, just a
single number. But the months/years to be queried on would be input via a
form. I was thinking of generating the data on-the-fly, but as you suggest
above and from something Frank Flynn suggested, I would be better off
having a separate table with monthly results. They could all be
pre-calculated except the current month.
Normally start and end dates would be input via a form.
I don't understand, at first you talked of $given_date and now we
have two dates! Are you inputting a month - asking for the average for
January for example. Or are you putting in two dates representing a
period of arbitrary length and asking for an average of that, eg 42
days? Please clarify.
Input is two dates, granularity one month. For example start Jan 2000,
finish Dec 2001. Then for each month in that period, I want the avarage
for each month. For example:
2000 January 56.2
2000 February 34.4
2001 December 48.6
There will be a whole lot of other statistics as well, such as number of
eggs laid, number hatched, number of surviving chicks etc. But I can
calculate _them_ quite easily.
If I have followed your explanation, and repeated your logic, then we
would indeed need to step through the days. However this is not the
'relational way' - there may be another way to achieve the 'numbers'.
Do you actually want/use the daily figures or is it only the
bottom/end-of-month average that is important?
End of month averages only.
Trouble is it's after midnight here. Apologies. Please would you
respond to the questions above, and if no one else steps in meantime,
I'll endeavor to return with a more lucid analysis in the morning...
Sorry to keep you awake :-)
Thanks for your help,
Regards,
Garry.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Date operations.
Hi All, I have a problem working out a suitable algorithm either in PHP or MySQL. Basically I have a DB that keeps track of breeding records. Each record has a paired data, and a split-up date. I need to generate some statistics to work out average numbers of pairs per month, averaged on a daily basis, for a given start and stop date, typically a year or year-to-date. All the algorithms I can think of are messy, where I have to loop through all the breeding records for every day of the year, and count how many pairs are breeding by seeing if the date is between the start and stop dates, and then average that on a monthly basis. I can't see that scaling very well, as there might be several hundred breeding records for a given year, multiplied by 365 days. Has anyone any hints/pointers for an efficient way to do this? Regards, Garry. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
