API: timestamp resolution inference: default to microseconds when possible
Draft PR for https://github.com/pandas-dev/pandas/issues/58989/

This should already make sure that we consistently use 'us' when converting non-numeric data in `pd.to_datetime` and `pd.Timestamp`, but if we want to do this, this PR still requires updating lots of tests and docs (and whatsnew) and cleaning up.

Currently the changes here will ensure that we use microseconds more consistently when inferring the resolution while creating datetime64 data. Exceptions: if the data don't fit in the range of us (either because out of bounds (use ms or s) or because it has nanoseconds or below (use ns)), or if the input data already has a resolution defined (for Timestamp objects, or numpy datetime64 data).

@jbrockmendel would you have time to give this a review? Yes, but its in line behind a few other reviews i owe.