Get the value for a past year

Filter on the year window, applies_from_year and applies_to_year, and leave is_latest_activity_year out: that flag marks the current row, the opposite of what you want. For a 2019 inventory, Spain's residual electricity mix from MITECO reads 0.310 kg CO2e per kWh, against 0.258 on today's default row.

The query#

Each physical value is one row with a window of activity years. This query asks the same factor for three reporting years at once.

SELECT y.report_year,
       f.reference_year,
       f.applies_from_year,
       f.applies_to_year,
       f.is_default_indicator,
       f.is_latest_activity_year,
       round(f.value_co2e_native::numeric, 3) AS kg_co2e_per_kwh
FROM (VALUES (2005), (2019), (2026)) AS y (report_year)
JOIN open_ef.factors_flat f
  ON f.library = 'miterd'
 AND f.slug = 'ef-miterd-electricidad_mix_sin_gdo-es-kwh-generation-c0551d67'
 AND (f.applies_from_year IS NULL OR f.applies_from_year <= y.report_year)
 AND (f.applies_to_year IS NULL OR f.applies_to_year >= y.report_year)
ORDER BY y.report_year;

For one year, replace the VALUES list with the year itself: applies_from_year <= 2019 and applies_to_year >= 2019, each allowing NULL.

How to read the window#

• A NULL applies_from_year sits on the earliest vintage and means "no earlier value exists". That is why 2005 returns the 2007 value. Check reference_year, the year the value describes, before you use a value outside its own year.
• A NULL applies_to_year sits on the newest vintage and means "still current". That is why 2026 returns the 2025 value.
• A monetary value works differently: it is served once per activity year, with applies_from_year = applies_to_year. The same predicate still picks the right row; see Deflate spend factors.

Watch out#

Watch out. Do not filter a past year on is_latest_activity_year: it is true only on the row that covers today, so it returns nothing for 2019. Do not rely on is_default_indicator either. It elects a methodology, not a year, and on MITECO's past-year rows it reads false, as the table shows. When the window returns more than one row (another GWP vintage, another calculation approach), choose between them on those columns, never by adding them up.

Watch out. What reference_year means depends on the library: the data year for most publishers, the edition year for some. MITECO publishes a data year. Read the library's methodology section before you reuse this query on another library: it prints what the year on that library's values means.

Related#

• Reference year and year windows and the glossary's year window
• Choosing a value: the axes
• Market-based and location-based electricity
• MITECO in factor search