FYI At the suggestion of Adriano, I have rewritten this to extract the
time in the zone instead of the UTC time.
Mark
On 21-02-2021 14:00, Mark Rotteveel (JIRA) wrote:
Add TIME and TIMESTAMP to EXTRACT to be able to obtain UTC value of WITH TIME
ZONE types.
-----------------------------------------------------------------------------------------
Key: CORE-6492
URL: http://tracker.firebirdsql.org/browse/CORE-6492
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 4.0 RC 1
Reporter: Mark Rotteveel
As a follow-up to the discussion on firebird-devel titled "Current timestamp without
timezone in given timezone", I suggest that we add TIME and TIMESTAMP to EXTRACT to
be able to extract the UTC value of a WITH TIME ZONE type as an WITHOUT TIME ZONE type.
This would have the following semantics:
<part> from <datetime> => <resulttype>
TIME from timestamp_with_tz => UTC value as TIME WITHOUT TIME ZONE
TIMESTAMP from timetamp_with_tz => UTC value as TIMESTAMP WITHOUT TIME ZONE
TIME from time_with_tz => UTC value as TIME WITHOUT TIME ZONE
TIMESTAMP from timestamp_with_tz => UTC value @ current_date as TIMESTAMP
WITHOUT TIME ZONE
TIME from timestamp_without_tz => cast to TIME WITHOUT TIME ZONE (strip date)
TIMESTAMP from timestamp_without_tz => original value (timestamp_without_tz )
TIME from time_without_tz => original value (time_without_tz)
TIMESTAMP from time_without_tz => cast to TIMESTAMP WITHOUT TIME ZONE (value @
current_date)
TIME from date_val => conversion error
TIMESTAMP from date_val => cast to TIMESTAMP WITHOUT TIME ZONE (current_date +
00:00:00 as time)
This solution would be non-standard, but would provide a nice symmetry with
EXTRACT(TIMEZONE_HOUR FROM ...) and EXTRACT(TIMEZONE_MINUTE FROM ...)
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
