Re: Join based on closest timestamp

[Holger A.]

Hi Peter,

what exactly do you mean with "populate the tables"? The create statements or an extract of the data itself?

I reduced the statements to the required minimum and added some comments into them.



#Database sbfspot-man
#"master" for available timestamps
CREATE TABLE `data` (
`timestamp` datetime NOT NULL, #created by the application in format yyyy-mm-dd hh:mm:ss
`timestamp-unix` bigint(20) NOT NULL, #transformation with sql to unix timestamp
`watt-pactot` decimal(15,4) DEFAULT NULL
)
;

#Database volkszaehler
#in this table are more timestamps available than required
CREATE TABLE `data` (
`timestamp` bigint(20) NOT NULL, #created by the application - unix timestamp
`value` double NOT NULL
)
;


#current work in progress
#open/to do: creation of join based on closest timestamp to add the field value from 2nd table.
SELECT
`SBF`.`timestamp`,
(SELECT
`VZ`.`timestamp`
FROM
`volkszaehler`.`data` AS VZ
ORDER BY ABS(`SBF`.`timestamp-unix` - `VZ`.`timestamp`)
LIMIT 1) AS newtime,
`SBF`.`timestamp-unix`,
`SBF`.`watt-pactot`
FROM
`sbfspot-man`.`data` AS SBF
;


Thx and KR
Holger