A PostgresSQL function to merge TeslaMate drives in the database

README.md

A PostgresSQL function to merge TeslaMate drives and charges in the database

Please note that the functions are only capable to merge a continuous range of drives/charges.

Make sure that all drives that shall be merged are terminated (how to terminate drives manually is described in the documentation.

Create the function by executing the file with e.g.

docker-compose exec database psql teslamate teslamate < teslamate_merge_drives.sql
docker-compose exec database psql teslamate teslamate < teslamate_merge_charges.sql

Then, call it in the psql CLI with

CALL merge_drives(start_drive_id, end_drive_id);
CALL merge_charges(start_charge_id, end_charge_id);

merge_charges is derived from a post by Dulanic, but taking cost into account.

teslamate_merge_charges.sql

CREATE OR REPLACE PROCEDURE merge_charges(
    start_charge_id integer,
    end_charge_id integer
)
LANGUAGE SQL
AS $$

UPDATE
    charging_processes
SET
    start_date = updated.start_date,
    end_date = updated.end_date,
    start_ideal_range_km = updated.start_ideal_range_km,
    end_ideal_range_km = updated.end_ideal_range_km,
    start_rated_range_km = updated.start_rated_range_km,
    end_rated_range_km = updated.end_rated_range_km,
    start_battery_level = updated.start_battery_level,
    end_battery_level = updated.end_battery_level,
    outside_temp_avg = updated.outside_temp_avg,
    duration_min = updated.duration_min
FROM (
    SELECT
        MAX(charging_process_id) AS charging_process_id,
        MIN(date) AS start_date,
        MAX(date) AS end_date,
        MIN(ideal_battery_range_km) AS start_ideal_range_km,
        MAX(ideal_battery_range_km) AS end_ideal_range_km,
        MIN(rated_battery_range_km) AS start_rated_range_km,
        MAX(rated_battery_range_km) AS end_rated_range_km,
        MIN(battery_level) AS start_battery_level,
        MAX(battery_level) AS end_battery_level,
        AVG(outside_temp) AS outside_temp_avg,
        EXTRACT(EPOCH FROM (MAX(date) - MIN(date)))::integer/60 AS duration_min
    FROM
        charges
    WHERE
      charging_process_id BETWEEN start_charge_id AND end_charge_id
    ) AS updated
WHERE
    charging_processes.id = updated.charging_process_id;

--update all records to show the max id now.
UPDATE
    charges
SET
    charging_process_id = end_charge_id
WHERE
    charging_process_id BETWEEN start_charge_id AND end_charge_id;

--summarize cost and energy usage
UPDATE
    charging_processes
SET
    charge_energy_added = updated.charge_energy_added,
    charge_energy_used = updated.charge_energy_used,
    cost = updated.cost
FROM (
    SELECT
        SUM(charge_energy_added) AS charge_energy_added,
        SUM(charge_energy_used) AS charge_energy_used,
        SUM(cost) AS cost
    FROM charging_processes
    WHERE id BETWEEN start_charge_id AND end_charge_id
    ) AS updated
WHERE
    id = end_charge_id;

--delete all but the max id AS they are now combined.
DELETE FROM
    charging_processes
WHERE
    id BETWEEN start_charge_id AND end_charge_id - 1;

$$;

teslamate_merge_drives.sql

CREATE OR REPLACE PROCEDURE merge_drives(
    start_drive_id integer,
    end_drive_id integer
)
LANGUAGE SQL
AS $$

UPDATE positions set drive_id = start_drive_id where drive_id >= start_drive_id and drive_id <= end_drive_id;

WITH positions_of_merged_drive as (
    SELECT * from positions WHERE drive_id = start_drive_id
)
UPDATE drives SET
    distance = (
        SELECT MAX(odometer) - MIN(odometer) FROM positions_of_merged_drive
    ),
    duration_min = (
        SELECT EXTRACT(EPOCH FROM (MAX(date) - MIN(date)))::integer/60 FROM positions_of_merged_drive
    ),
    outside_temp_avg = (
        SELECT AVG(outside_temp) FROM positions_of_merged_drive
    ),
    inside_temp_avg = (
        SELECT AVG(inside_temp) FROM positions_of_merged_drive
    ),
    speed_max = (
        SELECT MAX(speed) FROM positions_of_merged_drive
    ),
    power_max = (
        SELECT MAX(power) FROM positions_of_merged_drive
    ),
    power_min = (
        SELECT MIN(power) FROM positions_of_merged_drive
    )
WHERE id = start_drive_id;

UPDATE drives
    SET (
        end_date,
        end_ideal_range_km,
        end_km,
        end_address_id,
        end_rated_range_km,
        end_position_id,
        end_geofence_id
    ) = (SELECT
        end_date,
        end_ideal_range_km,
        end_km,
        end_address_id,
        end_rated_range_km,
        end_position_id,
        end_geofence_id
    FROM drives where id = end_drive_id)
WHERE id = start_drive_id;

DELETE FROM drives WHERE id > start_drive_id and id <= end_drive_id;

$$;