StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Quick 'n dirty suggestions.</p> <p>[Assuming you can't change the underlying tables, that those tables already record the time/date rows were added and that you do have permission to create objects in the DB].</p> <ol> <li>Create a VIEW (or a couple of VIEWS) which has a logical field on it, which generates a unique 'slot-number' by chopping up the date in the tables. Something like:</li> </ol> <p>CREATE VIEW view AS SELECT a,b,c, SUBSTR(date_field,x,y) slot_number FROM TABLE;</p> <p>The example above is simplified, you probably want to add in more elements from date+time.</p> <p>[eg, say date is '2010-01-01 10:20:23,111', you could perhaps generate the key as '2010-01-01 10:00': so your resolution is one-hour].</p> <ol start="2"> <li><p>Optionally: use the VIEW to generate a real table, like:</p> <p>CREATE TABLE frozen_data AS SELECT * FROM VIEW WHERE slot_number='xxx;</p></li> </ol> <p>Why bother with step 1? You don't actually have to: just using a VIEW might make things a bit easier (from a SQL point of view).</p> <p>Why bother with step 2? Just a way of a (possibly) reducing load on the already busy tables: if you can dynamically generate DDL then you could produce separate tables with copies of the 'slots' of data: which you can then work with.</p> <p>OR you could set up a group of tables : one per hour of the day. Create a trigger to populate the secondary tables : the logic of the trigger could segregrate which table is written to.</p> <p>On a daily basis you would have to reset these tables: unless you can generate tables in your trigger on your DB. [unlikely I think].</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload