Calculations via SQL

[As5a5sIn5]

I have a table which has the fields

id id_outlet time_exe time_toggle power

In which go an ID to make each field unique, an ID to reference to a specific outlet, the time the command took place, the time the outlet should change, and whether the power should turn on or off.

My goal is to create a sql script which calculates all the time off and all the time on. To do this, one must do time_toggle [OFF] - time_toggle [ON] (this would be all the time spent on. Also, note that the time is a unix timestamp. I not only am not an excellent sql writer, but I also am having trouble with ideas.

Thank you in advance for any suggestions
Anthony

[Fumigator]

You really should store your timestamps as a data type of datetime or timestamp (datetime is more appropriate for MySQL).

Once you make that change, this query should be pretty straight forward. And you are using straight MySQL query, no server side language?

[Old Pedant]

Agree w/ Fumigator that DATETIME is more appropriate with MySQL, but you should be able to use the data, as is. All the standard MySQL functions accept either data type and/or you could CAST it to DATETIME if need be. Just be aware that your code won't work after 8 Jan 2038. If you care. <grin/>

Anyway, what bothers me a bit here is the notion of needing to "pair" times.

Example data:

id | id_outlet | time_exe | time_toggle | power
337 | 4011 | 2009-6-1 13:15:22 | 2009-6-1 18:00:00 | on
351 | 4011 | 2009-6-1 15:13:12 | 2009-6-1 19:00:00 | off
402 | 4011 | 2009-6-1 16:22:11 | 2009-6-1 18:30:00 | on
535 | 4011 | 2009-6-1 17:43:13 | 2009-6-1 22:00:00 | off

Can you say "oops"?? If you put those records in "time_toggle" order, you can see that we have TWO "on" commands without an intervening "off". (And two "off" without an "on".)

But even if we change record "337" to turn "on" at (say) 21:00:00, we still have the problem of pairing up the on/off records.

We need to make sure that each "off" is paired with the "on" that happens closest to but before the "off".

Doing this all in SQL doesn't strike me as necessarily the best idea. I think it would be a lot easier to do--and you'd be much better able to catch errors such as in the data as presented--if you just made a simple SQL query but then processed the data in a server-side language, such as PHP/ASP/JSP.

[As5a5sIn5]

Well the purpose of time_exec is to keep the order. Also it can be used as a check to see when the switch was pushed (obviously). But this is a temporary dummy project. It's not perfect. I was just hoping for a little direction with the calculations via SQL or PHP. Also, I am using PHP as my server side which I feel comfortable with. Any code suggestions? Thank you in advance.

Anthony

[Old Pedant]

??? But what happens if somebody needs to come along and change a power on/off???

Then if time_exec reflects the time the record was added or changed, it will be out of sequence.

I would think it would be much better to just use something like this:

SELECT id_outlet, time_toggle, power
FROM yourtable
ORDER BY id_outlet, time_toggle

(that assumes you want to group all the records for a single outlet together...would be tough to do this another way).

Now your PHP code simply gets the records one at a time and subtracts the power=ON time_toggle from the power=OFF time_toggle.

In other words, with the example data I showed, you'd ignore the ID and ignore the time_exec and you'd get these records:

id_outlet | time_toggle | power
4011 | 2009-6-1 18:30:00 | on
4011 | 2009-6-1 19:00:00 | off
4011 | 2009-6-1 21:00:00 | on
4011 | 2009-6-1 22:00:00 | off

As you ran through those records in your PHP code, you would ensure that the power field alternated between "on" and "off" (and raise an error if it didn't). And you'd simply save the ON time while you got the next record, with the OFF time and the subtract the minutes of one from the minutes of the other. Presto.

I am *NOT* a PHP programmer, so I don't know what the function(s) you need are to get the minutes from a datetime value.

I would note that you *could* convert the times to minutes in your SQL query, so that then you'd just be subtracting integers in your PHP code, if that makes more sense to you.

If you want that, you could do:

SELECT id_outlet, TIMESTAMPDIFF(MINUTE,'2001-01-01',time_toggle) AS toggle_minutes, power
FROM yourtable
ORDER BY id_outlet, time_toggle

The base date there (2001-01-01) isn't important so long as it's before any time_toggle date in your table. That will return the number of minutes from the base date to the time_toggle value, as an integer, which makes the PHP code then especially easy.