how to get the value of time left for a transient

The problem here is that if the site is actually using something like Redis, or any other supported “external cache” then nothing is going to actually be set in the options table (which can be checked with wp_using_ext_object_cache)

Storage in the options table is just how WordPress handles transients outside of any external caching (or local object caching).

With that said, to calculate the time remaining, you would do something like this:

$expires = (int) get_option( '_transient_timeout_MY_TRANSIENT_NAME', 0 );
$time_left = $expires - time();

The value for expiration is calculated by adding time() to the value passed when setting the transient, so to get time left, just subtract time() from the value set for expiration