On 2020-05-16 05:22, Adriano dos Santos Fernandes wrote:
On 15/05/2020 12:46, Mark Rotteveel wrote:
The decision to use 2020-01-01 as date for some of the time with time
zone conversion leads to, in my opinion, confusing behaviour:
select
time'13:25:32.1235 Europe/Amsterdam' at time zone 'utc' as t1,
cast(time'13:25:32.1235 Europe/Amsterdam' as timestamp with time zone)
at time zone 'utc' as t2,
cast(cast(time'13:25:32.1235 Europe/Amsterdam' as timestamp with time
zone) as time with time zone) at time zone 'utc' as t3,
cast(cast(time'13:25:32.1235 Europe/Amsterdam' as timestamp with time
zone) at time zone 'utc' as time with time zone) as t4
from rdb$database;
yields:
T1 12:25:32.1235 UTC
T2 2020-05-15 11:25:32.1235 UTC
T3 12:25:32.1235 UTC
T4 11:25:32.1235 UTC
The previous behaviour where the current date was used (in line with the
SQL-standard requirements for using the current date when casting from
TIME WITH TIME ZONE to TIME WITH TIMESTAMP) would make all these
consistent (at 11:25:32.1235 UTC).
First, the previous behavior, as you know, is not viable: it broke
indexes, foreign keys, unique constraints, stored data.
The current behavior is not non-standard, as standard has only
offset-based time zones, and with offsets the current behavior is
identical to the previous one.
Do you have an alternative (that do not broke things), better than use
fixed date (as Oracle does with 0001-01-01 and it even more broke
behavior) or a recent date?
Telling true I have bug desire to answer that error 'Invalid operation'
should be raised because operation is really invalid, it has undefined
result (something near to zerodivide). But I'm afraid users do not like
such suggestion...
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
