harvy
Posts: 90
Joined: Mon Nov 26, 2012 1:49 pm
Location: London - UK

some SQL help

Fri Dec 29, 2017 4:49 pm

Having a bit of a dense moment - I have a log table for heating, everytime the heating comes on, i get an entry for what the current temp is, the target temp, time, and on or off.

example entrys are:

Code: Select all

log_id			sensor_id	time Ascending		current		target		on_off(1 signifies on, 0 is off)
2137			28-00000595cc2a	2017-12-29 15:32:03		19.812		21.000		1
2133			28-00000595cc2a	2017-12-29 10:06:03		20.062		19.000		0
2127			28-00000595cc2a	2017-12-29 06:01:04		18.312		21.000		1
I want to get the time and temp difference between the on and the off for each on/off cycle - (between the 1 and the next 0 for the same sensor)

so for the data above, i want to get time between 06:01:04 and 10:06:03, and difference between 18.312 and 20.062

i want this programmatic for the whole table (which has approx a year's data) and for a number of different sensors, aim to then put this into a new table initially. Overall aim is to get a moving average of time it takes to increase the temp by one degree, so that i can adjust a heating schedule to come on sooner/later to get a target temp in the room when required, rather then set times and the room not being at the right temp when wanted or the room being at temp before wanted time.

Can someone point me in the right direction of the sql to get the differences?

I'm using MYSQL if it makes much of a difference.


So far i have:

Code: Select all

select (select log_id from heating_log where sensor_id = '28-00000595cc2a' and on_off = 0 and log_id =2099) AS turn_off,
(select min(log_id) from heating_log where sensor_id = '28-00000595cc2a' and on_off = 1 and log_id > 2099) AS turn_on
from heating_log;
this gives me the correct two logs, but the same two values are repeated for every row in the table

harvy
Posts: 90
Joined: Mon Nov 26, 2012 1:49 pm
Location: London - UK

Re: some SQL help

Fri Dec 29, 2017 7:31 pm

bit of headscratching later:

Code: Select all

set @sensor='28-00000595cc2a';
set @log=2100;

select (hour(TIMEDIFF(b.time,a.time))*60)+minute(TIMEDIFF(b.time,a.time)) as Seconds_on, b.current-a.current as temp_increase from heating_log AS a, heating_log AS b where a.log_id = @log and b.log_id = (select min(log_id) from heating_log where sensor_id = @sensor and on_off = 0 and log_id > @log);ere sensor_id = @sensor and on_off = 0 and log_id > @log);

User avatar
DougieLawson
Posts: 33378
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: some SQL help

Fri Dec 29, 2017 9:51 pm

Can you post the output from
mysqldump -d -u pi -p dbnamegoeshere

"-d" means data definition language (DDL) only don't dump the data
"-u" defines the user who owns the table or has SELECT access to it
"-p" means ask for a password (assuming -u xxx needs one)

One way to solve the problem is to always select the current record (for a sensor) before you attempt to insert a new record. You can then insert a TIMEDIFF(now(), old_status_change_timestamp) as a duration column based on the old status change time (from your select) and the time now (whether you do that on your INSERT or UPDATE the existing row is a matter of style).

You may even be able to do that as a sub-SELECT or step away from SQL and do it in your python program. It's a thing to measure for performance, adding an index on your timestamp column and getting the MAX(timestampcolumn) to run should be very quick if you ever want to find the latest timestamp you MUST have an index like that (or you'll trawl through the whole table and may need to invoke a sort). [We could talk about "stage0" [as in not running SQL if you can avoid it], stage1 and stage2 predicates at this point.]

If you get the DDL I can have a go at writing some SQL for you.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

harvy
Posts: 90
Joined: Mon Nov 26, 2012 1:49 pm
Location: London - UK

Re: some SQL help

Thu Jan 04, 2018 7:55 pm

I cant do mysqldmp as its not installed (im running the db on a synology nas box - its installed via phpmyadmin rather then directly).

I've done a create like to get the sql:

-- Table structure for table `temp_log`
--

CREATE TABLE IF NOT EXISTS `temp_log` (
`measurement_id` int(11) NOT NULL AUTO_INCREMENT,
`sensor_id` varchar(15) NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`value` decimal(5,3) DEFAULT NULL,
PRIMARY KEY (`measurement_id`),
UNIQUE KEY `measurement_id` (`measurement_id`),
KEY `indx_sensor` (`sensor_id`),
KEY `sensor_id` (`sensor_id`),
KEY `sensor_id_2` (`sensor_id`),
KEY `idx_temp_log` (`sensor_id`,`date`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii AUTO_INCREMENT=4102113 ;

--
-- Constraints for table `temp_log`
--
ALTER TABLE `temp_log`
ADD CONSTRAINT `fk_sensor_log` FOREIGN KEY (`sensor_id`) REFERENCES `sensor_master` (`sensors`);

User avatar
DougieLawson
Posts: 33378
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: some SQL help

Thu Jan 04, 2018 9:28 pm

I'm not sure what point there is in having an auto-increment column, it's not useful for any SQL you're likely to use.

Also I'd always combine a date column and a time column into a single monotonic datetime column.

My table looks like

Code: Select all

CREATE TABLE `temp_log` (
  `measurement_id` int(11) NOT NULL AUTO_INCREMENT,
  `sensor_id` varchar(15) NOT NULL,
  `datetime` datetime NOT NULL,
  `value` decimal(5,3) DEFAULT NULL,
  `duration` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`measurement_id`),
  UNIQUE KEY `measurement_id` (`measurement_id`),
  KEY `sensor_id` (`sensor_id`),
  KEY `idx_temp_log` (`sensor_id`,`datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=4102117 DEFAULT CHARSET=ascii;
And the SQL to insert a new row is

Code: Select all

UPDATE temp_log SET duration = timediff(now(), datetime) WHERE datetime < now() AND duration is NULL AND sensor_id = 'abcd';
INSERT INTO temp_log (sensor_id, datetime, value) VALUES ('abcd', now(), 17.3);
So the insert puts a row in with NULL duration, we then select and update that row before doing the next insert. That gives us a duration column since the previous insert. sensor_id is unique (indexed and constrained). sensor_id & datetime are indexed to make the update run fast.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

Return to “General programming discussion”

Who is online

Users browsing this forum: No registered users and 4 guests