Page 1 of 1

Trouble with array

Posted: Tue Aug 03, 2010 8:06 pm
by cmckenna
Hello all,

Need a little help with grouping and summing using an array. I can't do this directly from the database because the vendor package we are using (Tivoli Data Warehouse) stores date in a proprietary format as a CHAR in the database and in GMT so I have to get the data first and then manipulate the date to get it human readable and in the right EST date.

I have my query (and my date conversion) from the database returning the data in a format that looks like this:

The reason each date has the service repeated is because it was selected from the database by hour (needed to convert Tivoli's weird timestamp and use of GMT)

Service TX_Count Date
-------------------------------------------------------------------------------------------
Service1 23451 2010-01-01
Service1 93874 2010-01-01
Service1 82363 2010-01-01
Service1 56245 2010-01-02
Service1 73453 2010-01-02
Service1 18965 2010-01-02

I have successfully gotten the data in to an array and I can group by date and then sum the tx_count, or I can group the services and sum the tx_count.

What I really need to be able to do is to get the transaction counts by service by day so that I would get something like this as a result

Service1 2010-01-01 199688
Service1 2010-01-02 148663

So I need to group by service and then date and then sum it all out of the array.

Any advice and direction is appreciated

Re: Trouble with array

Posted: Tue Aug 03, 2010 9:51 pm
by superdezign
Well, instead of retrieving all of the information from the database and then organizing the data, wouldn't it be faster and more manageable to alter the database query? You said that you want to count the number of transactions and group them by the day... MySQL actually has the keywords COUNT and GROUP BY which, when combined, will do exactly what you are requesting. And, it will do so without the lengthy process of retrieving the data, inserting the data into an array, and modifying the array.

Re: Trouble with array

Posted: Tue Aug 03, 2010 10:26 pm
by cmckenna
I have been down that route with the query. It is all because of the way Tivoli does their made up date format. It is not really a date that is recognized by any SQL operation because it is their own format and stored as CHAR.

Also because the date from the database is in GMT (albeit their own format) if I group by date then I get 2 groups since the difference between GMT and EDT is 4 hours right now (5 when EST) so my query has to say date between 1100803040000000 and 1100804040000000. this would give me 2 groups, one for 8/3 and one for 8/4 sine I have to do 4:00 AM to 4:00 AM when it should really all be 8/3. So I have to go through conversions to make all the dates the same (8/3).

The format of their date string in Tivoli is like this 1/10/08/03/04/00/00000 so translated means 2010-08-03 04:00:00000

So in the database an EDT 24 hour day is from 8/3 04:00 to 8/4 04:00. So grouping by day really gives me two days, not one.

See where my trouble is?

Regardless of all this though I would just like someone to help me with grouping by service, day and then sum.

Also, but it shouldn't matter, this isn't mySQL, the database is DB2 / Mainframe

Re: Trouble with array

Posted: Tue Aug 03, 2010 10:55 pm
by superdezign
If the array is your only means of handling this data, then just make a multi-dimensional array out of the data while traversing it. Just use the values of the columns to determine where in the array the data belongs and insert the tx_count if the index is empty, or add the tx_count to the current value if it exists.

The array would be in the format:

Code: Select all

array(
  'Service1' => array(
    '2010-01-01' => 199688,
    '2010-01-02' => 148663,
  ),
  'Service2' => array(
    // ...
  ),
  // ...
);