I had to think about what “Next Day Actual Generation” meant. How do you report on power generated on the next day? Answer…. the data represents the actual generation of a full day’s production (or storage) but is published… the next day. That’s it.
The National Energy Market (NEM) “Next Day Actual Generation” dataset lists the power generated (Mwh) and the associated production sites (duid). In this report for non-scheduled generators (i.e. biomass, backup diesel, powerwalls, rooftop solar etc)
Datasets are imported into my PostgeSQL server and sourced from here:
- Current (3 months rolling): https://nemweb.com.au/Reports/Current/Next_Day_Actual_Gen/
- Archive (1 year rolling): https://nemweb.com.au/Reports/ARCHIVE/Next_Day_Actual_Gen/
Below are some queries used to explore the data and derive some insights.
Quick view of data in “actual_generation” tables
- Have a quick look at the data and how it can be displayed
select interval_datetime,duid,mwh_reading
from actual_generation
limit 20
;Output…
interval_datetime | duid | mwh_reading
------------------------+-----------+-------------
2025-05-01 04:05:00+10 | BARCSF1 | 0.0
2025-05-01 04:05:00+10 | BUTLERSG | 8.399999
2025-05-01 04:05:00+10 | BWTR1 | -21.1492
2025-05-01 04:05:00+10 | CAPTL_WF | 13.959197
2025-05-01 04:05:00+10 | CHALLHWF | 14.3
2025-05-01 04:05:00+10 | CLOVER | 0.0
2025-05-01 04:05:00+10 | CLUNY | 2.07599
2025-05-01 04:05:00+10 | CONDONG1 | -0.86
2025-05-01 04:05:00+10 | CULLRGWF | 2.8
2025-05-01 04:05:00+10 | DIAPURWF1 | 6.8
2025-05-01 04:05:00+10 | ERGT01 | 0.0
2025-05-01 04:05:00+10 | GB01 | 10.11902
2025-05-01 04:05:00+10 | GERMCRK | -0.415
2025-05-01 04:05:00+10 | GRIFSF1 | 0.001526
2025-05-01 04:05:00+10 | HUGSF1 | -0.2
2025-05-01 04:05:00+10 | KEPBG1 | 0.0
2025-05-01 04:05:00+10 | LRSF1 | 0.0
2025-05-01 04:05:00+10 | MBAHNTH | 27.1
2025-05-01 04:05:00+10 | MLSP1 | 0.023
2025-05-01 04:05:00+10 | MLWF1 | 2.325
(20 rows)Total NEM generation per 5-minute interval
- Measure ramp rates. i.e. how much does each plant’s generation change every interval.
- Call out fast ramping generators
SELECT
interval_datetime,
SUM(mwh_reading) AS total_mwh
FROM actual_generation
GROUP BY interval_datetime
ORDER BY interval_datetime
;Output…
interval_datetime | total_mwh
------------------------+------------
2024-05-01 04:05:00+10 | 545.497502
2024-05-01 04:10:00+10 | 539.038527
2024-05-01 04:15:00+10 | 531.715357
2024-05-01 04:20:00+10 | 530.386384
2024-05-01 04:25:00+10 | 534.226486
2024-05-01 04:30:00+10 | 526.845372
2024-05-01 04:35:00+10 | 516.330730
2024-05-01 04:40:00+10 | 510.359193
2024-05-01 04:45:00+10 | 509.214776
2024-05-01 04:50:00+10 | 505.824655
2024-05-01 04:55:00+10 | 503.555583
2024-05-01 05:00:00+10 | 500.254706
2024-05-01 05:05:00+10 | 497.356030
2024-05-01 05:10:00+10 | 497.771763
2024-05-01 05:15:00+10 | 496.404566
2024-05-01 05:20:00+10 | 500.029548
2024-05-01 05:25:00+10 | 496.353926
2024-05-01 05:30:00+10 | 502.279562
2024-05-01 05:35:00+10 | 511.691882
2024-05-01 05:40:00+10 | 519.135316
2024-05-01 05:45:00+10 | 544.446013
2024-05-01 05:50:00+10 | 547.439224
2024-05-01 05:55:00+10 | 550.809626
2024-05-01 06:00:00+10 | 549.886148
2024-05-01 06:05:00+10 | 560.390427
2024-05-01 06:10:00+10 | 558.757519
2024-05-01 06:15:00+10 | 571.775836
2024-05-01 06:20:00+10 | 569.472035
2024-05-01 06:25:00+10 | 570.351214
2024-05-01 06:30:00+10 | 558.260983
...
...Average daily generation per generator
- Calculate average daily output
- Potentially display with clustered bar chart
SELECT
date_trunc('day', interval_datetime) AS day,
duid,
AVG(mwh_reading) AS avg_mwh
FROM actual_generation
GROUP BY day, duid
ORDER BY day, avg_mwh DESC
limit 20;Output…
day | duid | avg_mwh
------------------------+----------+---------------------
2024-05-01 00:00:00+10 | PORTWF | 98.6606694979079498
2024-05-01 00:00:00+10 | WAUBRAWF | 79.8161338033472803
2024-05-01 00:00:00+10 | MBAHNTH | 53.5248952594142259
2024-05-01 00:00:00+10 | WOOLNTH1 | 33.7896644476987448
2024-05-01 00:00:00+10 | CAPTL_WF | 32.1958773179916318
2024-05-01 00:00:00+10 | SHOAL1 | 28.7500000000000000
2024-05-01 00:00:00+10 | CONDONG1 | 23.4248536066945607
2024-05-01 00:00:00+10 | PIONEER | 22.2016811008403361
2024-05-01 00:00:00+10 | CHALLHWF | 19.7861923682008368
2024-05-01 00:00:00+10 | GERMCRK | 18.7159451983122363
2024-05-01 00:00:00+10 | PALOONA | 15.8649369037656904
2024-05-01 00:00:00+10 | YAMBUKWF | 14.7263597866108787
2024-05-01 00:00:00+10 | CULLRGWF | 10.9298326485355649
2024-05-01 00:00:00+10 | MLWF1 | 9.4992677991631799
2024-05-01 00:00:00+10 | GRIFSF1 | 7.5182350334728033
2024-05-01 00:00:00+10 | YSWF1 | 6.6966527196652720
2024-05-01 00:00:00+10 | ROYALLA1 | 5.6247993933054393
2024-05-01 00:00:00+10 | REPULSE | 5.0655075146443515
2024-05-01 00:00:00+10 | LRSF1 | 4.7598413472803347
2024-05-01 00:00:00+10 | BWTR1 | 4.3709462761506276
(20 rows)Top 10 generators between dates in 5-minute output
- Ranking of generators by average output between two date intervals
- Interesting in bar chart format
SELECT
duid,
ROUND(AVG(mwh_reading), 2) AS five_min_avg_mwh
FROM actual_generation
WHERE interval_datetime BETWEEN '2025-05-01' AND '2025-05-31'
GROUP BY duid
ORDER BY five_min_avg_mwh DESC
LIMIT 10
;
Output…
duid | five_min_avg_mwh
----------+------------------
WAUBRAWF | 64.86
WOOLNTH1 | 42.64
SHOAL1 | 40.26
PORTWF | 38.98
CAPTL_WF | 33.34
MBAHNTH | 23.46
GERMCRK | 14.22
CHALLHWF | 12.97
CULLRGWF | 11.34
CONDONG1 | 11.08
(10 rows)Identify negative generation events (consumption by storage/pumps)
- Highlight time intervals when energy is consumed
- Identify pumping patterns by duid/site.
SELECT
interval_datetime,
duid,
mwh_reading
FROM actual_generation
WHERE mwh_reading < 0
ORDER BY interval_datetime
;Output…
interval_datetime | duid | mwh_reading
------------------------+----------+-------------
2024-05-01 04:05:00+10 | BUTLERSG | -0.000002
2024-05-01 04:05:00+10 | HUGSF1 | -0.039
2024-05-01 04:05:00+10 | RPCG | -0.3
2024-05-01 04:10:00+10 | BUTLERSG | -0.000002
2024-05-01 04:10:00+10 | CLOVER | -0.01
2024-05-01 04:10:00+10 | HUGSF1 | -0.039
2024-05-01 04:10:00+10 | RPCG | -0.3
2024-05-01 04:15:00+10 | BUTLERSG | -0.000002
2024-05-01 04:15:00+10 | CLOVER | -0.01
2024-05-01 04:15:00+10 | HUGSF1 | -0.039
2024-05-01 04:15:00+10 | RPCG | -0.3
2024-05-01 04:20:00+10 | BUTLERSG | -0.000002
2024-05-01 04:20:00+10 | CLOVER | -0.01
2024-05-01 04:20:00+10 | HUGSF1 | -0.039
2024-05-01 04:20:00+10 | RPCG | -0.3
2024-05-01 04:25:00+10 | BUTLERSG | -0.000002
2024-05-01 04:25:00+10 | CLOVER | -0.01
2024-05-01 04:25:00+10 | HUGSF1 | -0.039
2024-05-01 04:25:00+10 | RPCG | -0.3
2024-05-01 04:30:00+10 | BUTLERSG | -0.000002
...
...Delta of interval power produced per generator
- Measure ramp rates how much each plant output changes per interval
- Highlight fast ramping units
SELECT
interval_datetime,
duid,
mwh_reading,
mwh_reading -
LAG(mwh_reading) OVER (PARTITION BY duid ORDER BY interval_datetime)
AS delta_mwh
FROM actual_generation
;Output…
interval_datetime | duid | mwh_reading | delta_mwh
------------------------+-----------+-------------+-------------
...
...
2024-05-01 08:05:00+10 | BARCSF1 | 6.7 | 0.6
2024-05-01 08:10:00+10 | BARCSF1 | 7.3 | 0.6
2024-05-01 08:15:00+10 | BARCSF1 | 6.7 | -0.6
2024-05-01 08:20:00+10 | BARCSF1 | 7.400001 | 0.700001
2024-05-01 08:25:00+10 | BARCSF1 | 8.3 | 0.899999
2024-05-01 08:30:00+10 | BARCSF1 | 5.9 | -2.4
2024-05-01 08:35:00+10 | BARCSF1 | 7.2 | 1.3
2024-05-01 08:40:00+10 | BARCSF1 | 8.0 | 0.8
2024-05-01 08:45:00+10 | BARCSF1 | 7.400001 | -0.599999
2024-05-01 08:50:00+10 | BARCSF1 | 8.1 | 0.699999
2024-05-01 08:55:00+10 | BARCSF1 | 8.1 | 0.0
2024-05-01 09:00:00+10 | BARCSF1 | 8.1 | 0.0
2024-05-01 09:05:00+10 | BARCSF1 | 8.200001 | 0.100001
2024-05-01 09:10:00+10 | BARCSF1 | 8.200001 | 0.000000
2024-05-01 09:15:00+10 | BARCSF1 | 8.200001 | 0.000000
2024-05-01 09:20:00+10 | BARCSF1 | 8.200001 | 0.000000
2024-05-01 09:25:00+10 | BARCSF1 | 8.200001 | 0.000000
2024-05-01 09:30:00+10 | BARCSF1 | 8.200001 | 0.000000
2024-05-01 09:35:00+10 | BARCSF1 | 8.200001 | 0.000000
...
...Identify missing intervals per generator(duid)
- Check for tine intervals gaps.
- Handy to know what data clearing needs to be performed.
WITH all_intervals AS (
SELECT DISTINCT interval_datetime FROM actual_generation
),
duid_intervals AS (
SELECT
duid,
interval_datetime
FROM actual_generation
)
SELECT
ai.interval_datetime,
di.duid
FROM all_intervals ai
CROSS JOIN (SELECT DISTINCT duid FROM actual_generation) di
LEFT JOIN duid_intervals ON
di.duid = duid_intervals.duid
AND ai.interval_datetime = duid_intervals.interval_datetime
WHERE duid_intervals.duid IS NULL
ORDER BY di.duid, ai.interval_datetime
;Output…
interval_datetime | duid
------------------------+-----------
...
...
2025-03-08 08:15:00+11 | YSWF1
2025-03-08 14:35:00+11 | YSWF1
2025-03-08 16:50:00+11 | YSWF1
2025-03-09 12:10:00+11 | YSWF1
2025-03-20 06:40:00+11 | YSWF1
2025-03-21 05:35:00+11 | YSWF1
2025-03-27 16:25:00+11 | YSWF1
2025-04-01 13:50:00+11 | YSWF1
2025-04-01 13:55:00+11 | YSWF1
2025-04-01 14:00:00+11 | YSWF1
2025-04-01 14:05:00+11 | YSWF1
2025-04-01 14:10:00+11 | YSWF1
2025-04-01 14:15:00+11 | YSWF1
2025-04-01 14:20:00+11 | YSWF1
2025-04-01 14:25:00+11 | YSWF1
2025-04-01 14:30:00+11 | YSWF1
2025-04-01 14:35:00+11 | YSWF1
...
...Daily plant(duid) min / max generation
- Highlight each generator daily minimum and maximum value
- Utilise min-max chart to visualise
SELECT
date_trunc('day', interval_datetime) AS day,
duid,
MIN(mwh_reading) AS min_mwh,
MAX(mwh_reading) AS max_mwh
FROM actual_generation
GROUP BY day, duid
ORDER BY day, duid
;Output…
day | duid | min_mwh | max_mwh
------------------------+-----------+------------+------------
2024-05-01 00:00:00+10 | BARCSF1 | 0.0 | 8.3
2024-05-01 00:00:00+10 | BUTLERSG | -0.000002 | -0.000002
2024-05-01 00:00:00+10 | BWTR1 | 3.77256 | 4.80144
2024-05-01 00:00:00+10 | CAPTL_WF | 0.0 | 68.547928
2024-05-01 00:00:00+10 | CHALLHWF | 6.8 | 35.199997
2024-05-01 00:00:00+10 | CLOVER | -0.03 | 13.66
2024-05-01 00:00:00+10 | CLUNY | 1.891051 | 14.140078
2024-05-01 00:00:00+10 | CONDONG1 | 18.75 | 26.950001
2024-05-01 00:00:00+10 | CULLRGWF | 3.14 | 23.389999
2024-05-01 00:00:00+10 | DIAPURWF1 | 0.0 | 0.0
2024-05-01 00:00:00+10 | ERGT01 | 0.0 | 0.0
2024-05-01 00:00:00+10 | GB01 | 0.0 | 0.0
2024-05-01 00:00:00+10 | GERMCRK | 14.141 | 20.611
2024-05-01 00:00:00+10 | GRIFSF1 | 0.001526 | 22.700848
2024-05-01 00:00:00+10 | HUGSF1 | -0.113 | 8.697001
2024-05-01 00:00:00+10 | KEPBG1 | 0.0 | 1.93
2024-05-01 00:00:00+10 | LRSF1 | 0.01 | 10.081
2024-05-01 00:00:00+10 | MBAHNTH | 50.268753 | 57.768753
...
...Generate distribution histogram
- Bucket output levels to see occurrences of frequency distribution
- Histogram chart would be best to display output
SELECT
CASE
WHEN mwh_reading < 0 THEN '< 0'
WHEN mwh_reading >= 0 AND mwh_reading < 5 THEN '0–5'
WHEN mwh_reading >= 5 AND mwh_reading < 10 THEN '5–10'
WHEN mwh_reading >= 10 AND mwh_reading < 15 THEN '10–15'
WHEN mwh_reading >= 15 AND mwh_reading < 20 THEN '15–20'
WHEN mwh_reading >= 20 AND mwh_reading < 25 THEN '20–25'
WHEN mwh_reading >= 25 AND mwh_reading < 30 THEN '25–30'
WHEN mwh_reading >= 30 AND mwh_reading < 35 THEN '30–35'
WHEN mwh_reading >= 35 AND mwh_reading < 40 THEN '35–40'
WHEN mwh_reading >= 40 AND mwh_reading < 45 THEN '40–45'
WHEN mwh_reading >= 45 AND mwh_reading < 50 THEN '45–50'
WHEN mwh_reading >= 50 AND mwh_reading < 75 THEN '50–75'
WHEN mwh_reading >= 75 AND mwh_reading < 100 THEN '75–100'
ELSE '> 100'
END AS range_mwh,
COUNT(*) AS count
FROM actual_generation
GROUP BY range_mwh
ORDER BY MIN(mwh_reading)
;Output…
range_mwh | count
-----------+---------
< 0 | 525512
0–5 | 1747144
5–10 | 512283
10–15 | 369916
15–20 | 267429
20–25 | 172469
25–30 | 146030
30–35 | 91441
35–40 | 76989
40–45 | 89712
45–50 | 41362
50–75 | 73583
75–100 | 53516
> 100 | 96456
(14 rows)