sql: make TIMETZ more pg-compatible

[knz]

Following up to #25224.

Currently TIMETZ does not store the time zone.
The code could be adapted to do this and make the data type more compatible with postgres.

[bobvawter]

Is there a similar need for TIMESTAMPTZ, too?

[jordanlewis]

Luckily, no. TIMESTAMPTZ behaves like we thought TIMETZ did - no timezone stored, interpreted according to session timestamp at time of read.

[knz]

@jordanlewis what do you think will be the timeline on this?

Unless you propose to come up with something soon-ish, I suggest we create an intermediate step where the feature is disabled. I already filed cockroachdb/docs#3254 to remove it from docs.

[jordanlewis]

I'll be working on this within the next 2 weeks.

[otan]

Done some digging and will start implementation on this shortly.

In postgres, TimeTZADT is stored as {timeofday, zone_offset}. However, zone_offset is not quite what you expect naturally - it is the negative of the offset given by t.Zone(). For example, ET is -05:00 but the offset is stored as 18000.

For comparisons, it compares timeofday, and then zone_offset. Importantly from this discovery, timetzs, even when they are equivalent in real world clock time are not UNLESS the timezones are the same. For an illustration:

 otan=# select '19:00+03'::timetz < '16:00+00'::timetz;
 ?column?
----------
 t
(1 row)

otan=# select '19:00+03'::timetz = '16:00+00'::timetz;
 ?column?
----------
 f
(1 row)

otan=# select '19:00+03'::timetz < '13:00-03'::timetz;
 ?column?
----------
 t
(1 row)

otan=# select '19:00+03'::timetz < '16:00'::time;
 ?column?
----------
 t
(1 row)

My plan is to bring back #28095 over smaller, reviewable components, but largely adapting the encoding used by #27605 because it would've been correct (minus some test failures which I will look at addressing).

btree proof: https://sourcegraph.com/github.com/postgres/postgres@7a0574b50ee9c2b96ce94c29e031c103285c0b1d/-/blob/contrib/btree_gist/btree_time.c#L181