Christian F. Posted February 21, 2013 Share Posted February 21, 2013 (edited) A little background on the project I'm working on: I have a reasonably large number of values, over 500k rows worth. The values are generated per hour, per parent. I want to group these values by one of four time intervals: Month, week, day or hour, and get the average for this period. The challenge comes by the fact that I need to split the values into two different blocks, based upon whether the value was generated during the "day" or "night/weekend". So far I've come up with two different approaches to this: Have two fields in the table for saving the values, one for the "daytime" values and the other for "night time/weekend" values.This would allow me to run separate functions on the fields, without involving the date functions in MySQL. At the cost of having two fields for what is essentially the same type of data, and having to do the splitting in the pre-insert phase. Save all values in the same field, and then use MySQL's datetime functions with a CASE-WHEN to figure out in which block they belong.This approach seems to be the cleanest one in terms of database-design, but I fear it will put a lot of strain on the database. Especially since the SELECT statements will run more often than the insertion script. So the question is if I should go for the first approach, the second, or if there is some other solution to this that I've failed to grasp? I could really do with some expert help on this one. Example values: timestamp parent_1 parent_2 parent_3 2013.02.04 04:00:00 6,300000 1,400000 4,000000 2013.02.04 05:00:00 6,300000 1,400000 4,000000 2013.02.04 06:00:00 6,300000 1,400000 4,000000 2013.02.04 07:00:00 6,300000 1,400000 4,000000 2013.02.04 08:00:00 6,300000 1,400000 4,000000 2013.02.04 09:00:00 6,300000 1,400000 4,000000 2013.02.04 10:00:00 13,600000 9,900000 10,800000 2013.02.04 11:00:00 13,600000 9,900000 10,800000 Desired output: Date: Day avg. Night avg. -- Parent_1 2013.02.04 99,5 6,3 ..... -- Parent_2 2013.02.04 5,65 1,4 ..... -- Parent_3 2013.02.04 4,9 4,0 ..... Edited February 21, 2013 by Christian F. Quote Link to comment Share on other sites More sharing options...
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.