Many of the greenhouse Youtube channels I watch do a smaller DIY version of this. They have either dark barrels of water in the greenhouse with the windows facing south to the sun, or a thin metal wall filled with clay and pipes acting as a sun-heat-battery. They pump the water through the barrels or clay battery into pipes that are under ground to store the heat. In winter time they extract the heat from the ground…
This reminds me a lot of the Earthship community in the middle of the desert, which always focusses on smart ways of reusing water for different purposes, and by not wasting water as much as possible. The houses usually have a clean/grey/blackwater + a rainwormbox system to process fecals and reuse it for growing plants. Their air conditioning system is basically just a pipe in the ground where the hot air flows thro…
All the standard Earthship designs I've studied do store their water on the roof, but they use batteries instead of water pumping to store unused electricity, because to run a conventional house's electrical systems at night you need to store tens of megajoules, and lifting water three meters only stores 0.029 MJ per cubic meter. A cubic meter of water weighs a tonne. So you'd need hundreds of tonnes of water to store the requisite amount of energy that way.
A 12-volt 24-amp-hour deep-cycle lead-acid battery goes for US$61 at retail, and that's nominally a megajoule; it replaces 33 tonnes of water at 3 meters of head, you can pick it up in one hand, and it doesn't require an electromechanical pump/turbine to convert the energy into a useful form. And if it shorts out, though it might cause a fire in your electrical room, it won't flood your house.
If you're building on a plain, you either need to support your upper water tank with earthworks (say, 2 tonnes per cubic meter of earth) or dig out a hole for a lower water tank for water to flow down into (also 2 tonnes per cubic meter of earth). If we want to store 700 cubic meters of water in a 2-meter-deep water tank whose bottom is 3 meters above grade, we need to pile up 2100 tonnes of dirt covering a water-tank-holding area of 350 m², a tank area with minimally a diameter of 21 meters. And you need a similarly sized tank down at grade level for it to drain into. You can cut this in half by putting the downhill tank in the hole you dug to get all that dirt, but it's still over a thousand tonnes of dirt. Aside from the 1400 square meters of water tank top and bottom surfaces, this would increase the earthmoving effort involved in building an Earthship by over an order of magnitude.
Then you need to pump the 700 tonnes of water out of a well, because that's four years' worth of rainfall on the area covered by your giant water tanks (assuming 250 mm rainfall per year). This is a feasible thing to do, and it's less water use than what cattle ranchers evaporate from their windmill-fed water tanks, but it would probably clash with the sensibilities of many Earthship types.
Alternatively you can put 20 deep-cycle batteries on shelves in a closet-sized concrete room. So that's what they do.
If your Earthship is situated at the foot of a 100-meter-tall mesa, the situation changes, because now you can store a megajoule per tonne of water. So you could use more reasonably sized tanks, like, 20 tonnes. But Earthships are mostly not designed for that situation, because it's rare.