alexandreyc opened a new pull request, #4546:
URL: https://github.com/apache/arrow-rs/pull/4546
# Which issue does this PR close?
Closes #4457.
# Rationale for this change
Arithmetic on timezoned timestamps was wrong.
# Are there any user-facing changes?
Yes.
Methods `add_year_months`, `add_day_time`, `add_month_day_nano`,
`subtract_year_months`, `subtract_day_time` and `subtract_month_day_nano` on
`TimestampSecondType`, `TimestampMillisecondType`, `TimestampMicrosecondType`
and `TimestampNanosecondType` now take an additonal parameter `tz: Tz` as these
operations are inherently timezone-dependent. Maybe there is a way to do things
differently and not break the API. Feel free to suggest any idea.
# Tests
I tested the results against PostgreSQL 14. Feel free to propose new test
cases that you find interesting.
Here is the script to reproduce the results:
```
postgres=# create temporary table tests as (
select
(timestamp '1970-01-28 23:00:00' at time zone 'Europe/Paris') as
datetime,
(interval '0 year 1 month') as interval_year_month,
(interval '0 day 0 millisecond') as interval_day_time,
(interval '1 month 0 day 0 microsecond') as interval_month_day_nano
union all
select
(timestamp '1970-01-01 00:00:00' at time zone 'Europe/Paris') as
datetime,
(interval '5 year 34 month') as interval_year_month,
(interval '5 day 454000 millisecond') as interval_day_time,
(interval '344 month 34 day -43000000 microsecond') as
interval_month_day_nano
union all
select
(timestamp '2010-04-01 04:00:20' at time zone 'Europe/Paris') as
datetime,
(interval '-2 year 4 month') as interval_year_month,
(interval '-34 day 0 millisecond') as interval_day_time,
(interval '-593 month -33 day 13000000 microsecond') as
interval_month_day_nano
union all
select
(timestamp '1960-01-30 04:23:20' at time zone 'Europe/Paris') as
datetime,
(interval '7 year -4 month') as interval_year_month,
(interval '7 day -4000 millisecond') as interval_day_time,
(interval '5 month 2 day 493000000 microsecond') as
interval_month_day_nano
union all
select
(timestamp '2023-03-25 14:00:00' at time zone 'Europe/Paris') as
datetime,
(interval '0 year 1 month') as interval_year_month,
(interval '1 day 0 millisecond') as interval_day_time,
(interval '1 month 0 day 0 microsecond') as interval_month_day_nano
);
SELECT 5
postgres=# select * from tests
postgres=# select * from tests;
datetime | interval_year_month | interval_day_time |
interval_month_day_nano
------------------------+---------------------+-------------------+--------------------------------------
1970-01-28 23:00:00+01 | 1 mon | 00:00:00 | 1 mon
1970-01-01 00:00:00+01 | 7 years 10 mons | 5 days 00:07:34 | 28 years
8 mons 34 days -00:00:43
2010-04-01 04:00:20+02 | -1 years -8 mons | -34 days | -49
years -5 mons -33 days +00:00:13
1960-01-30 04:23:20+01 | 6 years 8 mons | 7 days -00:00:04 | 5 mons 2
days 00:08:13
2023-03-25 14:00:00+01 | 1 mon | 1 day | 1 mon
(5 rows)
postgres=# select datetime + interval_year_month from tests;
?column?
------------------------
1970-02-28 23:00:00+01
1977-11-01 00:00:00+01
2008-08-01 04:00:20+02
1966-09-30 04:23:20+01
2023-04-25 14:00:00+02
(5 rows)
postgres=# select (datetime + interval_year_month) - interval_year_month
from tests;
?column?
------------------------
1970-01-28 23:00:00+01
1970-01-01 00:00:00+01
2010-04-01 04:00:20+02
1960-01-30 04:23:20+01
2023-03-25 14:00:00+01
(5 rows)
postgres=# select datetime + interval_day_time from tests;
?column?
------------------------
1970-01-28 23:00:00+01
1970-01-06 00:07:34+01
2010-02-26 04:00:20+01
1960-02-06 04:23:16+01
2023-03-26 14:00:00+02
(5 rows)
postgres=# select (datetime + interval_day_time) - interval_day_time from
tests;
?column?
------------------------
1970-01-28 23:00:00+01
1970-01-01 00:00:00+01
2010-04-01 04:00:20+02
1960-01-30 04:23:20+01
2023-03-25 14:00:00+01
(5 rows)
postgres=# select datetime + interval_month_day_nano from tests;
?column?
------------------------
1970-02-28 23:00:00+01
1998-10-04 23:59:17+02
1960-09-29 04:00:33+01
1960-07-02 04:31:33+01
2023-04-25 14:00:00+02
(5 rows)
postgres=# select (datetime + interval_month_day_nano) -
interval_month_day_nano from tests;
?column?
------------------------
1970-01-28 23:00:00+01
1970-01-02 00:00:00+01
2010-04-02 04:00:20+02
1960-01-31 04:23:20+01
2023-03-25 14:00:00+01
(5 rows)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
