Re: Join based on closest timestamp
Posted by:
Holger A.
Date: April 02, 2018 02:45PM
Hi Peter,
yes for sure.
Table A (Master for available timestamps); database: "sbfspot-man":
CREATE TABLE `data` (
`id` int(20) NOT NULL,
`timestamp` datetime NOT NULL,
`timestamp-rnd` datetime DEFAULT NULL,
`timestamp-unix` bigint(20) NOT NULL,
`timestamp-unix-rnd` bigint(20) NOT NULL,
`device_name` varchar(45) NOT NULL,
`devicetype` varchar(45) NOT NULL,
`serial` decimal(15,0) NOT NULL,
`uploaded` int(11) DEFAULT '0',
`watt-pdc1` decimal(15,4) DEFAULT NULL,
`watt-pdc2` decimal(15,4) DEFAULT NULL,
`amp-idc1` decimal(15,4) DEFAULT NULL,
`amp-idc2` decimal(15,4) DEFAULT NULL,
`volt-udc1` decimal(15,4) DEFAULT NULL,
`volt-udc2` decimal(15,4) DEFAULT NULL,
`watt-pac1` decimal(15,4) DEFAULT NULL,
`watt-pac2` decimal(15,4) DEFAULT NULL,
`watt-pac3` decimal(15,4) DEFAULT NULL,
`amp-iac1` decimal(15,4) DEFAULT NULL,
`amp-iac2` decimal(15,4) DEFAULT NULL,
`amp-iac3` decimal(15,4) DEFAULT NULL,
`volt-uac1` decimal(15,4) DEFAULT NULL,
`volt-uac2` decimal(15,4) DEFAULT NULL,
`volt-uac3` decimal(15,4) DEFAULT NULL,
`watt-pdctot` decimal(15,4) DEFAULT NULL,
`watt-pactot` decimal(15,4) DEFAULT NULL,
`per-efficiency` decimal(15,4) DEFAULT NULL,
`kwh-etoday` decimal(15,4) DEFAULT NULL,
`kwh-etotal` decimal(15,4) DEFAULT NULL,
`hz-frequency` decimal(15,4) DEFAULT NULL,
`hours-operating` decimal(15,4) DEFAULT NULL,
`hours-feedintime` decimal(15,4) DEFAULT NULL,
`per-bt_singal` decimal(15,4) DEFAULT NULL,
`status-condition` varchar(45) DEFAULT NULL,
`status-gridrelay` varchar(45) DEFAULT NULL,
`degc-temperature` decimal(15,4) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `index2` (`timestamp`,`device_name`,`serial`,`devicetype`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Table B (more timestamps available than required, closest one needs to be picked); database: "volkszaehler":
CREATE TABLE `data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`channel_id` int(11) DEFAULT NULL,
`timestamp` bigint(20) NOT NULL,
`value` double NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `data_unique` (`channel_id`,`timestamp`),
KEY `IDX_ADF3F36372F5A1AA` (`channel_id`),
CONSTRAINT `FK_ADF3F36372F5A1AA` FOREIGN KEY (`channel_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=500997 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
My current work in progress status:
SELECT
`SBF`.`id`,
`SBF`.`timestamp`,
`SBF`.`timestamp-rnd`,
(SELECT
`VZ`.`timestamp`
FROM
`volkszaehler`.`data` AS VZ
WHERE
`VZ`.`channel_id` = 10
ORDER BY ABS(`SBF`.`timestamp` - `VZ`.`timestamp`)
LIMIT 1) AS newtime,
`SBF`.`timestamp-unix`,
`SBF`.`timestamp-unix-rnd`,
`SBF`.`device_name`,
`SBF`.`devicetype`,
`SBF`.`serial`,
`SBF`.`uploaded`,
`SBF`.`watt-pdc1`,
`SBF`.`watt-pdc2`,
`SBF`.`amp-idc1`,
`SBF`.`amp-idc2`,
`SBF`.`volt-udc1`,
`SBF`.`volt-udc2`,
`SBF`.`watt-pac1`,
`SBF`.`watt-pac2`,
`SBF`.`watt-pac3`,
`SBF`.`amp-iac1`,
`SBF`.`amp-iac2`,
`SBF`.`amp-iac3`,
`SBF`.`volt-uac1`,
`SBF`.`volt-uac2`,
`SBF`.`volt-uac3`,
`SBF`.`watt-pdctot`,
`SBF`.`watt-pactot`,
`SBF`.`per-efficiency`,
`SBF`.`kwh-etoday`,
`SBF`.`kwh-etotal`,
`SBF`.`hz-frequency`,
`SBF`.`hours-operating`,
`SBF`.`hours-feedintime`,
`SBF`.`per-bt_singal`,
`SBF`.`status-condition`,
`SBF`.`status-gridrelay`,
`SBF`.`degc-temperature`,
`SBF`.`created_at`,
`SBF`.`updated_at`
FROM
`sbfspot-man`.`data` AS SBF
LIMIT 0 , 10
Thx and KR
Holger