I believe you need 5v to run the regular $4 pi pico board. The chip only requires 3.2v though so maybe it’s not a hard requirement? There are probably other lighter weight RP2040 boards but I don’t think months or years-long low power usage was an intended goal— it’s $4 (still!) with 40 GPIO pins and PIO and it runs micropython for people scared of/chafed by C— it’s a prototyping/hobby tool.

It has sleep current of 200microamps, so no longer than 40 days…

The STM32L011 in no way requires the arduino IDE; your code would likely compile with GCC just fine. The pico would probably work if you redesigned your project, but your hardware very likely doesn't need to change, just the software you're using.