Add TIME and TIMESTAMP to EXTRACT to obtain time in the zone of a WITH TIME ZONE value as a WITHOUT TIME ZONE value [CORE6492]

[firebird-automations]

Submitted by: @mrotteveel

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 time in the zone of a WITH TIME ZONE type as a WITHOUT TIME ZONE type.

This would have the following semantics:

<part> from <datetime> => <resulttype>
TIME from timestamp_with_tz => time in zone (@ specified date) as TIME WITHOUT TIME ZONE
TIMESTAMP from timestamp_with_tz => time+date in zone as TIMESTAMP WITHOUT TIME ZONE
TIME from time_with_tz => time in zone as TIME WITHOUT TIME ZONE
TIMESTAMP from time_with_tz => time in zone @ 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)

Primary rationale for this feature is that CAST(tz_value as TIME[STAMP] WITHOUT TIME ZONE) converts to the time in the session time zone before conversion to WITHOUT TIME ZONE. Currently, getting the time in the zone as a WITHOUT TIME ZONE requires conversion to string, use of substring and then casting to TIME/TIMESTAMP.

[firebird-automations]

Commented by: @asfernandes

While the internal representation of with-tz types stores the utc value, people generally see that values formated to the session time zone.

It would be extremally non obvious to have that extracted values different than what they see that parts formatted.

[firebird-automations]

Commented by: @mrotteveel

Rewritten to use (date)time in zone as the value to be extracted.

[firebird-automations]

Modified by: @mrotteveel

description: 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 ...)

=>

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 time in the zone of a WITH TIME ZONE type as a WITHOUT TIME ZONE type.

This would have the following semantics:

<part> from <datetime> => <resulttype>
TIME from timestamp_with_tz => time in zone (@ specified date) as TIME WITHOUT TIME ZONE
TIMESTAMP from timetamp_with_tz => time+date in zone as TIMESTAMP WITHOUT TIME ZONE
TIME from time_with_tz => time in zone as TIME WITHOUT TIME ZONE
TIMESTAMP from timestamp_with_tz => time in zone @ 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)

Primary rationale for this feature is that CAST(tz_value as TIME[STAMP] WITHOUT TIME ZONE) converts to the time in the session time zone before conversion to WITHOUT TIME ZONE. Currently, getting the time in the zone as a WITHOUT TIME ZONE requires conversion to string, use of substring and then casting to TIME/TIMESTAMP.

summary: Add TIME and TIMESTAMP to EXTRACT to be able to obtain UTC value of WITH TIME ZONE types. => Add TIME and TIMESTAMP to EXTRACT to obtain time in the zone of a WITH TIME ZONE value as a WITHOUT TIME ZONE value

[firebird-automations]

Commented by: @aafemt

In fourth example didn't you mean time_with_tz...?
Though extracting of timestamp from time makes no sense for me.

[firebird-automations]

Modified by: @mrotteveel

description: 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 time in the zone of a WITH TIME ZONE type as a WITHOUT TIME ZONE type.

This would have the following semantics:

<part> from <datetime> => <resulttype>
TIME from timestamp_with_tz => time in zone (@ specified date) as TIME WITHOUT TIME ZONE
TIMESTAMP from timetamp_with_tz => time+date in zone as TIMESTAMP WITHOUT TIME ZONE
TIME from time_with_tz => time in zone as TIME WITHOUT TIME ZONE
TIMESTAMP from timestamp_with_tz => time in zone @ 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)

Primary rationale for this feature is that CAST(tz_value as TIME[STAMP] WITHOUT TIME ZONE) converts to the time in the session time zone before conversion to WITHOUT TIME ZONE. Currently, getting the time in the zone as a WITHOUT TIME ZONE requires conversion to string, use of substring and then casting to TIME/TIMESTAMP.

=>

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 time in the zone of a WITH TIME ZONE type as a WITHOUT TIME ZONE type.

This would have the following semantics:

<part> from <datetime> => <resulttype>
TIME from timestamp_with_tz => time in zone (@ specified date) as TIME WITHOUT TIME ZONE
TIMESTAMP from timestamp_with_tz => time+date in zone as TIMESTAMP WITHOUT TIME ZONE
TIME from time_with_tz => time in zone as TIME WITHOUT TIME ZONE
TIMESTAMP from time_with_tz => time in zone @ 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)

Primary rationale for this feature is that CAST(tz_value as TIME[STAMP] WITHOUT TIME ZONE) converts to the time in the session time zone before conversion to WITHOUT TIME ZONE. Currently, getting the time in the zone as a WITHOUT TIME ZONE requires conversion to string, use of substring and then casting to TIME/TIMESTAMP.

[firebird-automations]

Commented by: @mrotteveel

@dimitry You're right, fixed it.

Extracting a timestamp from time might not make much sense in a strict interpretation of the word EXTRACT, but defining it like this will make a complete definition that allows equivalent "from <type1> to <type2>" conversions as allowed by cast.

[firebird-automations]

Commented by: @aafemt

So we can end up with non-standard EXTRACT with standard behavior and standard CAST with non-standard behavior.
Wouldn't it make a bigger sense to swap them?

[firebird-automations]

Commented by: @mrotteveel

@dimitry that would have the knock-on effect of SET BIND no longer translating values to the session time zone, making that feature useless.

[firebird-automations]

Commented by: @aafemt

Ah, right, I forgot about it, sorry.

[firebird-automations]

Commented by: @asfernandes

I'm very against these:

TIMESTAMP from time_with_tz => time in zone @ current_date as TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP from time_without_tz => cast to TIMESTAMP WITHOUT TIME ZONE (value @ current_date)
TIMESTAMP from date_val => cast to TIMESTAMP WITHOUT TIME ZONE (current_date + 00:00:00 as time)

This has nothing to do with EXTRACT.

Also, if this is implemented, DATE from TIMESTAMP(s)/DATE should also be implemented.

[firebird-automations]

Commented by: @mrotteveel

@adriano My suggestion for those three options is to have an equivalent for the behaviour that cast has as well.

The only alternatives I can think of is
1. An expression similar to `AT { TIME ZONE <zone> | LOCAL }`, so `<tz_value> WITHOUT TIME ZONE` which would return the WITHOUT TIME ZONE equivalent of the datatype
2. A function, e.g. `TO_LOCAL(<tz_value>)`, but naming-wise, that is bit confusing and ambiguous given `<value> AT LOCAL`