98 lines
4.0 KiB
SQL
98 lines
4.0 KiB
SQL
-- :name insert-price! :! :n
|
|
-- :doc Insert/update a day-ahead price record
|
|
INSERT INTO day_ahead_prices (time_utc, time_dk, price_area, price_dkk, price_eur)
|
|
VALUES (:time-utc, :time-dk, :price-area, :price-dkk, :price-eur)
|
|
ON CONFLICT (time_utc, price_area) DO UPDATE SET
|
|
time_dk = EXCLUDED.time_dk,
|
|
price_dkk = EXCLUDED.price_dkk,
|
|
price_eur = EXCLUDED.price_eur;
|
|
|
|
-- :name get-prices-for-date :? :*
|
|
-- :doc Get all prices for a given date (time_dk local date) and price area
|
|
SELECT time_utc, time_dk, price_area, price_dkk, price_eur
|
|
FROM day_ahead_prices
|
|
WHERE time_dk::date = :date
|
|
AND price_area = :price-area
|
|
ORDER BY time_dk;
|
|
|
|
-- :name upsert-metering-point! :! :n
|
|
-- :doc Upsert a metering point, refreshing data on re-sync
|
|
INSERT INTO metering_points (metering_point_id, type_of_mp, balance_supplier, street_name,
|
|
building_number, postcode, city_name, has_relation, settlement_method,
|
|
reading_occurrence, consumer_start_date, first_consumer_name)
|
|
VALUES (:metering-point-id, :type-of-mp, :balance-supplier, :street-name,
|
|
:building-number, :postcode, :city-name, :has-relation, :settlement-method,
|
|
:reading-occurrence, :consumer-start-date, :first-consumer-name)
|
|
ON CONFLICT (metering_point_id) DO UPDATE SET
|
|
type_of_mp = EXCLUDED.type_of_mp,
|
|
balance_supplier = EXCLUDED.balance_supplier,
|
|
street_name = EXCLUDED.street_name,
|
|
building_number = EXCLUDED.building_number,
|
|
postcode = EXCLUDED.postcode,
|
|
city_name = EXCLUDED.city_name,
|
|
has_relation = EXCLUDED.has_relation,
|
|
settlement_method = EXCLUDED.settlement_method,
|
|
reading_occurrence = EXCLUDED.reading_occurrence,
|
|
consumer_start_date = EXCLUDED.consumer_start_date,
|
|
first_consumer_name = EXCLUDED.first_consumer_name,
|
|
fetched_at = now();
|
|
|
|
-- :name insert-meter-reading! :! :n
|
|
-- :doc Insert a meter reading, skip if already exists
|
|
INSERT INTO meter_readings (metering_point_id, time_dk, quantity_kwh, quality)
|
|
VALUES (:metering-point-id, :time-dk, :quantity-kwh, :quality)
|
|
ON CONFLICT (metering_point_id, time_dk) DO NOTHING;
|
|
|
|
-- :name insert-charge! :! :n
|
|
-- :doc Insert a charge record, skip if already exists
|
|
INSERT INTO charges (metering_point_id, charge_type, name, description, owner,
|
|
valid_from_date, valid_to_date, period_type, price, quantity, position)
|
|
VALUES (:metering-point-id, :charge-type, :name, :description, :owner,
|
|
:valid-from-date, :valid-to-date, :period-type, :price, :quantity, :position)
|
|
ON CONFLICT (metering_point_id, charge_type, name, owner, valid_from_date, position) DO NOTHING;
|
|
|
|
-- :name count-consumption-for-date :? :1
|
|
-- :doc Count consumption records for a given UTC time_start
|
|
SELECT count(*) AS cnt
|
|
FROM consumption
|
|
WHERE time_start = :time-start;
|
|
|
|
-- :name count-production-for-date :? :1
|
|
-- :doc Count production records for a given UTC time_start
|
|
SELECT count(*) AS cnt
|
|
FROM production
|
|
WHERE time_start = :time-start;
|
|
|
|
-- :name count-meter-readings-for-date :? :1
|
|
-- :doc Count meter readings for a given UTC time_start (converted to DK date range)
|
|
SELECT count(*) AS cnt
|
|
FROM meter_readings
|
|
WHERE time_dk >= :from-dk AND time_dk < :to-dk;
|
|
|
|
-- :name insert-production! :! :n
|
|
-- :doc Insert a production record, skip if already exists
|
|
INSERT INTO production (time_start, hour, kwh)
|
|
VALUES (:time-start, :hour, :kwh)
|
|
ON CONFLICT (time_start, hour) DO NOTHING;
|
|
|
|
-- :name insert-consumption! :! :n
|
|
-- :doc Insert a consumption record, skip if already exists
|
|
INSERT INTO consumption (time_start, hour, kwh)
|
|
VALUES (:time-start, :hour, :kwh)
|
|
ON CONFLICT (time_start, hour) DO NOTHING;
|
|
|
|
-- :name insert-elprice! :! :n
|
|
-- :doc Upsert an hourly el-price row; refresh tariff/total on conflict
|
|
INSERT INTO elprice (time_dk, price_area, price_dkk, tariff_dkk, total_dkk)
|
|
VALUES (:time-dk, :price-area, :price-dkk, :tariff-dkk, :total-dkk)
|
|
ON CONFLICT (time_dk, price_area) DO UPDATE SET
|
|
tariff_dkk = EXCLUDED.tariff_dkk,
|
|
total_dkk = EXCLUDED.total_dkk;
|
|
|
|
-- :name get-elprice-for-date :? :*
|
|
-- :doc Hourly prices for a DK local date, both areas
|
|
SELECT time_dk, price_area, price_dkk, tariff_dkk, total_dkk
|
|
FROM elprice
|
|
WHERE time_dk::date = :date
|
|
ORDER BY time_dk, price_area;
|