Does the SQL type TIME WITH TIMEZONE make sense?

Viewed 205

While doing the mapping of some database columns into Java classes I stumbled onto this obscure SQL-92 Standard type (implemented by PostgreSQL, H2, and HyperSQL afaik). I haven't ever used it, but I wanted to understand how clearly map it to a Java type if I ever find it.

Here are the variants I can see:

  • Case A: The TIME type, such as 15:20:01. It's a "local time". The time zone is evident to the application so the database doesn't record it.

  • Case B: The TIME with offset, as in 15:20:01+04:00. It represents a "world time". This time can be converted trivially to UTC, or to any other world clock.

  • Case C: A TIME with a time zone, such as 15:20:01 EDT. Since the rules to interpret a time strongly depend on the specific date I can't really make any sense of it without the date; but then, if I add the date, it becomes a TIMESTAMP, and that's something totally different.

So, did the SQL Standard get it wrong? Or maybe "TIME with time zone" should be always interpreted as "time with offset" (case B)?

0 Answers
Related