sql: Add support for convert_tz() and equivalent functions

[tim-o]

FEATURE REQUEST

1. Does an issue already exist addressing this request? If yes, please add a 👍 reaction to the existing issue. If not, move on to step 2.

Dependent on but not identical to #26097 .

2. Please describe the feature you are requesting, as well as your proposed use case for this feature.

Add support for a convert_tz() function as described in https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz , or equivalent.

3. Indicate the importance of this issue to you (blocker, must-have, should-have, nice-to-have). Are you currently using any workarounds to address this issue?

Switchfour to comment below.

[thebitmechanic]

We have data that when queried is specific to a particular timezone. The data includes a UTC timestamp that represents the point in time that the data takes place. For example flight segments.

We need to be able to query all segments that occurred on a specific day considering what the day is the timezone. The following example is from MariaDB.

select * from segments_air where date_format(convert_tz(date_add(from_unixtime(0), interval start_tstamp second), '+00:00', start_timezone), '%e %b %Y')='19 Feb 2018';
Side note, in our application we have indexed values in the where clause, and the function in this example would filter the indexed records.

This is an important issue to us because we would need to query all records, then in our code apply the timezone then exclude the records that don't apply. Obviously we could work around it, but we would prefer that the database handle it.

[knz]

@thebitmechanic why not convert the desired date to UTC instead in the comparison? e.g. select * from segments_air where start_tstamp::date = ('2018-02-19'::interval + start_timezone)::date?

[knz]

(then to make this query fast you'd need to convert the test x::date = y::date to something like x >= ts_date1 and x < ts_date2 where ts_date2 is precomputed to be ts_date1 + 1 day.)

[thebitmechanic]

@knz start_timezone is an IANA timezone not an offset. Are you suggesting converting the following to a unix timestamp outside of the query?

'2018-02-19'::interval + start_timezone

I haven't seen anywhere in the cockroach docs that describes using IANA timezones.

[knz]

All the timezone names map to an offset expressed in seconds. Are you requesting a function that computes that amount of seconds as an INTERVAL value, so you can use it to compute what you need?

[thebitmechanic]

Our database stores all timestamps as unix timestamps. The example above is looking for all air segments that occur on a day, 19 Feb 2018 for example. start_tstamp is essentially a departure date and time in seconds. We need to be able to apply the IANA timezone (start_timezone) to the start_tstamp to determine if the segment occurs on 19 Feb 2018. Without doing this the start_tstamp is a unix timestamp which of course is UTC. We could select a number of records, iterate over them while programmatically applying the IANA timezone looking for segments that occur on 19 Feb 2018, but it would be nice if the database could just handle it.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[knz]

crdb now supports the AT TIME ZONE operator