Most databases have two separate data types to represent a TIME (range is 0 to 24h) and a DURATION where the range is much longer than that.
My questions is why is it necessary for there to be two different types here, and why can't it be handled more like a VARCHAR is done, that is, "time" would be something like DURATION(24) and "duration" might be something like DURATION(24*7*365). What is the main reason that the two types need to be different as opposed to having something like a check/restraint on the domain or range of values it may have?
I believe mysql does not have a duration (just time), postgres has both, and sqlserver also does not have a duration type (again just a time and various other date/time's).
My questions is why is it necessary for there to be two different types here
Because they are two different things. A time is an absolute "moment during the day", e.g. "8 in the evening" or "3 in the morning".
An interval (=duration) is something like "2 months 3 days 10 hours 15 minutes and 42 seconds" - a value that cannot be represented as a time.
The interval type has another reason to exist: it can also correctly express the difference between two timestamps.
In theory anything you can express with a time value can be expressed with an interval as well. Actually a timestamp is also not necessary the, as every timestamp can also be expressed as an interval based on a start date you define.
But if you remove all types that "overlap" with other types, you would need to remove other types as well. float or double can always be expressed in a numeric value. In fact a numeric value can also be expressed as a really large integer value, so why keep the numeric.
The main reason to have different data types for different things is primarily efficiency and validation. But it also helps understanding your data model and improves expressiveness - if it's an interval you know by looking at the column's type that it represents a "duration", if it's a time column you know that it represents a "moment". And you see that without having to first consult the check constraints that are defined for the column.