N2O is more stable than CO2. Mixing N2O with water or cream won't create diffetent molecules. If the liquid you add N2O is not very thick (as water) the gas and liquid will separate in two. If it is thick, as with cream, the gas will get trapped in it. You can see the proccess with more detail in this question.
CO2 reacts with water (H2O) making H2CO3 (carbonic acid). It makes soda water, which has a slight acid taste, and get bubbles of released CO2 if the pressure of the soda water falls. CO2 can be used for whipping cream, but it will give acid taste to it. You can get some more information from the Wikipedia entry.
None of them cools liquids. The chargers get cooled down when opened, as the gas inside them decreases pressure. But that gas goes to the cream whipper or the soda siphon, which theoretically should increase its temperature, but having a much larger volume than the charger makes that increase unnoticeable.
When using pressure gas to make espresso, you arent pouring that gas to a closed recipient: the water escapes thru the coffee puck, so it won't cool the water. MyPressi V1 put the gas in the middle of the water chamber, so it could only be used with N2O. V2 of the device puts the gas in the top part of the chamber, so it is not mixed with the water, making it suitable to be used with CO2.