Timezone UTC is not considered equal to +00:00

[samuelcolvin]

Describe the bug

Running

select now() - start_timestamp from records limit 5;

Results in an error:

Cannot coerce arithmetic expression Timestamp(Nanosecond, Some("+00:00")) - Timestamp(Microsecond, Some("UTC")) to valid types

As far as I know UTC is alwasy identical to +00:00 so these two should be considered equivilant?

To Reproduce

No response

Expected behavior

Timezones which are practically equivalent — e.g. have the same offset and DST semantics should allow subtraction to get an interval.

Additional context

No response

[marvinlanhenke]

Probably this is the reason why...both str are simply compared. I'm not sure if it makes sense to just 'add' another rule when checking...or if one has to come up with a better fix / design if there are other cases that have to be supported or checked as well?

[samuelcolvin]

Technically now() is wrongly defined, it explicitly states it's in "UTC" in the docs.

[samuelcolvin]

I'd be happy to create a pool to start adding special cases, but that sounds like a rabbit hole.

[alamb]

I think the chrono-tz crate has the special cases / knows how to handle converting "UTC" to "+0:00" so I don't think we would have to make a special datafusion map

The arrow crate has a "chrono-tz" feature which activates it. I wonder if we have to turn that on somewhere 🤔 https://crates.io/crates/arrow or maybe use an appropriate check in datafusion

[alamb]

I am responding to @marvinlanhenke 's comment #10960 (comment)

Since "+00:00" and "UTC" are the 'same' - is this viable? Or do we have to extend the logic here in order to provide both variants (and possibly not introduce a breaking change)?

I think that "UTC" == "+00:00" might be a reasonable special case to include

So perhaps we could update the timezone comparison check to compare those two types the same 🤔

[alamb]

#10960 from @marvinlanhenke is up for review 🙏