Dean Karres <[EMAIL PROTECTED]> wrote on 02/09/2005 12:46:46 PM: > Hi, > > I have a table full of activity records. Each record has a DATETIME > field for when the activity started and one for when it ended. > I want to post process this table and produce billing records from it. > > Billing records will have a link back to the activity record that > produces them; i.e. there could be more than one billing record for > each event. The reason for this is that there are different billing > rates per event type and also different rates depending on the time > of day ["office hour" rates are different than early morning, evening, > night/weekend rates]. > > Let's say that office-hour-rates start from 8am - 4:59:59pm Monday > - Friday. Everything else is a night/weekend rate. > > It is easy to get the records where the event start/stop DATETIMES are > in the same day and are both in or out of the "office hours" range. > it is fairly easy to get the records the event start/stop DATETIMES > are in the same day and the start record is "early morning" and the > stop record is "business hours" or the start record is "business" > and the end os evening. > > Let's say that I have an activity record that starts at 6am. > It continues all day until 9pm that same evening. I would want to > prodauce three billing records: > > 06:00:00 - 07:59:59 > 08:00:00 - 16:59:59 > 17:00:00 - 21:00:00 > > What if the activity flows over a day change? More than one day > cahnge? > > Should I just punt and return the activity DATETIMES as UNIX_TIMESTAMPs > and do the date manipulation myslef? > > Dean...K... > > -- > Dean Karres / karres at itg dot uiuc dot edu / www.itg.uiuc.edu > > Imaging Technology Group / Beckman Institute > University of Illinois > 405 North Mathews / Urbana, IL 61801 USA >
I believe your options depend on the language you are using for the post-processing. If your language has datetime datatypes then stick with those. If not, use the unix_timestamp values and integer math (mostly). I have a hard time understanding if you are having problems with the data, the database, or your program design???? BTW - There is at least one case you didn't mention. What happens if an activity is still active at the time you post-process? Do you break it at the last midnight or at the point in time that you are processing? Really, this sounds more like a program design and logic flow issue and less of a MySQL issue. If you need help getting at data or forming your queries we will be more than happy to help but the sort of logic you are asking about probably belongs in your application code, not your SQL statements. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
