We studied the physical conditions in the local ISM and at high redshift by fitting observed column densities of CO and H2 rotational levels, and C i fine-structure levels using photo-dissociation models calculated with the Meudon PDR code. We analyzed CO absorption systems in 28 sight lines in the local ISM and 7 damped Lyman-α absorption systems at high redshift, covering N(H2) = 1019 to 1021.5 cm−2 and N(CO) = 1013 to 1018 cm−2. We constructed a method to accurately calculate CO excitation, incorporating the effects of photon trapping. Our findings indicate that in the local ISM, CO excitation is primarily driven by collisions and excitation due to the CMB radiation. We demonstrated that an increase in CO excitation, observed near N(CO) ≃ 1015 cm−2, is attributed to an increase in gas densities from ≃ 100 cm−3 to ≃ 300 − 1000 cm−3. CO absorption systems in the local ISM are characterized by a gas number density of about 10-1000 cm−3, a kinetic temperature of 10-100 K, and an intensity of external UV field ranging from 0.1 to 10 units of Mathis field. Compared to the average gas probed by C i absorption, the gas detected in CO is denser and colder, while the external UV field remains nearly constant. We observed a negative correlation between the kinetic temperature and both N(CO) and N(H2), with power-law slopes of −0.21 ± 0.02 and −0.65 ± 0.05, respectively. At the same time, the gas number density exhibits a positive correlation with N(CO) and N(H2) with slopes of 0.38 ± 0.02 and 1.15 ± 0.05, respectively.