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]