Implement SQL standard FORMAT clause for CAST between string types and datetime types [CORE6507] #2388

firebird-automations · 2021-03-08T18:16:10Z

Implement SQL standard FORMAT clause for CAST between string types and datetime types, to allow custom formatting of datetime values and conversion from string values with a specific format to datetime values.

"""
<cast specification> ::=
CAST <left paren>
<cast operand> AS <cast target>
[ FORMAT <cast template> ]
<right paren>

<cast template> ::=
<character string literal>
"""

Where <cast template> follows the rules of Subclause 9.42, "Converting a datetime to a formatted character string" or Subclause 9.43, "Converting a formatted character string to a datetime". Specific syntax rules defined in Subclause 9.44, "Datetime templates":

"""
<datetime template> ::=
{ <datetime template part> }...

<datetime template year> ::=
YYYY | YYY | YY | Y

<datetime template rounded year> ::=
RRRR | RR

<datetime template month> ::=
MM

<datetime template day of month> ::=
DD

<datetime template day of year> ::=
DDD

<datetime template 12-hour> ::=
HH | HH12

<datetime template 24-hour> ::=
HH24

<datetime template minute> ::=
MI

<datetime template second of minute> ::=
SS

<datetime template second of day> ::=
SSSSS

<datetime template fraction> ::=
FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9

<datetime template am/pm> ::=
A.M. | P.M.

<datetime template time zone hour> ::=
TZH

<datetime template time zone minute> ::=
TZM
"""

firebird-automations · 2021-03-08T20:18:27Z

Commented by: @dyemanov

Replacement for CORE1314?

firebird-automations · 2021-03-08T21:07:23Z

Commented by: @mrotteveel

@dmitry, personally I read the original request of CORE1314 more like a request for a message formatter like the following:

```
STRING_FORMAT('Value 1: {0}, value 2: {1}', 'a', 'b')
```

results in

```
'Value 1: a, value 2: b'
```

The dateformatting was tacked on CORE1314 when CORE1341 was closed, while it should have remained a separate ticket IMHO. These are two different concerns that should be handled separately (string interpolation vs dateformatting).

omachtandras · 2021-05-20T07:09:32Z

It would be nice to see this feature in 5.0.
(Is there a voting system on GitHUB similar to the previous one?)

mrotteveel · 2021-05-20T08:22:26Z

@omachtandras You can use a thumbs-up response on the initial comment (through the smiley icon button).

TreeHunter9 · 2023-06-16T07:26:45Z

I made a PR #7629 with this feature, and it would be nice if someone would look into it.

…2388 (#7629) * Add FORMAT clause to convert datetime types to string and vice versa * Add tests for FORMAT clause * Fixes after review * Change TZD to TZR * Change inline variables back to static * Add README documentation * Add ability to use " in raw string and ... Use session timezone if timezone is not specified. Add ability to use + sign in timezone offset. Add truncating string exception. * Move util methods from BOOST_AUTO_TEST_SUITE * Switch back to inline variables * Consider charset in the format string * Add ability to write patterns without separators * Use printf to add extra zeros Also add extra zeros to the year patterns. * Replace template exception with a plain function * Clean code after review * Fix bug with TZH:TZM when TZH is 0 * Add TZR to STRING to DATE --------- Co-authored-by: Artyom Ivanov <artyom.ivanov@red-soft.ru>

pavel-zotov · 2023-11-02T10:36:56Z

Is it possible to use format pattern (specified here as "<cast template>") from variable instead of literal one ?

Suppose that i have following table and put one row in it:

set list on;
recreate table test(
    id int generated always as identity constraint pk_test primary key,
    dts timestamp with time zone
);
commit;
 
insert into test(dts) values('23.09.2016 12:34:56.789 Indian/Cocos');
commit;

Following query runs OK:

select cast(dts as varchar(50) format 'dd.mm.year hh24:mi:ss.ff3') as dsql_result from test;
DSQL_RESULT                     23.09.2016 12:34:56.789

But if i try to do similar using PSQL block then i get error:

set term ^;
execute block returns( psql_result varchar(50) ) as
   declare dts timestamp with time zone = '23.09.2016 12:34:56.789 Indian/Cocos';
   declare fmt varchar(50);
begin
   fmt = 'dd.mm.year hh24:mi:ss.ff3';
   psql_result = cast(dts as varchar(50) format fmt);
   suspend;
end
^
set term ;^

Output:

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 6, column 50
-fmt

mrotteveel · 2023-11-02T11:32:29Z

Is it possible to use format pattern (specified here as "") from variable instead of literal one ?

No, the SQL standard doesn't support that. It only specifies support for a character string literal (see the quoted syntax rule <cast template>). Part of the problem is that you're thinking of cast as a function, and it is not a function, it is basically a fixed transformation whose entire syntax must be validated when the statement is compiled. That syntax includes the value(!) of the format string. If you'd be able to parametrize that, you can no longer check the syntax at statement compilation.

To be honest, I'm not really happy how the implementation turned out. It has far too many non-standard extension as it is IMHO.

dyemanov · 2023-11-02T11:56:29Z

IMHO it's better to have a standard function with non-standard extensions than implement a second non-standard function with these extensions. We have standard EXTRACT with non-standard QUARTER / MILLISECOND / WEEK / WEEKDAY / YEARDAY and nobody complained so far ;-)

mrotteveel · 2023-11-02T11:59:57Z

IMHO it's better to have a standard function with non-standard extensions than implement a second non-standard function with these extensions. We have standard EXTRACT with non-standard QUARTER / MILLISECOND / WEEK / WEEKDAY / YEARDAY and nobody complained so far ;-)

Sure, but I think some of the extensions that have now been added to the cast-format make it more complicated than necessary, and should only have been implemented when there is a clear demand, not as the initial implementation.

pavel-zotov · 2023-11-03T09:20:58Z

One more Q.

select cast('12:34:57 +01:00' as time with time zone) from rdb$database;
CAST                            12:34:57.0000 +01:00

select cast('12:34:57 +01:00' as time with time zone format 'HH24:MI:SS TZH:TZM' ) from rdb$database;
CAST                            13:34:57.0000 +01:00

Why presence of FORMAT <cast template> causes somewhat like 'additional shifting' of source timestamp ?

mrotteveel · 2023-11-03T09:53:21Z

That looks like a bug to me.

TreeHunter9 · 2023-11-03T13:58:53Z

That looks like a bug to me.

It seems so. I'm looking at the CVT_string_to_datetime implementation (this is the first query), and it converts local time to UTC, so there's no "shift" happens.
I'll fix it.

asfernandes · 2023-11-03T14:07:47Z

There are things in this implementation that without access to the standard I cannot say they are correct or not, for example, missing parts:

select cast('12 -03:00' as timestamp with time zone format 'HH24 TZH:TZM' ) from rdb$database;

Also note that TIME WITH TIMEZONE and region-based timezones is trick in general.

mrotteveel · 2023-11-03T14:27:39Z

This is covered by 9.51 Converting a formatted character string to a datetime (SQL:2023), to paraphrase the rule: missing date parts take the value of CURRENT_DATE, for missing time parts values take value zero (0) (note if SSSS (second of day) is specified, all other time parts are irrelevant), for missing time zone parts, also: use zero (0).

mrotteveel · 2023-11-03T14:30:21Z

Correction: the default values for the date parts are taken from CURRENT_TIMESTAMP (not that this should make much of a difference).

@asfernandes As an aside: I highly recommend buying a copy of ISO/IEC 9075-2:2023 (or maybe ask the Firebird Foundation to buy a copy for you).

asfernandes · 2023-11-03T14:33:16Z

or maybe ask the Firebird Foundation to buy a copy for you

I asked and the subject didn't moved.

pavel-zotov · 2023-11-22T10:14:04Z

QA issue: see #2388 (comment)
Currently test can not be implemented until this bug(?) will be fixed.

…#2388 (#7835) * Convert time with time zone to UTC Also return an exception that was "lost" in previous commits * Fix incorrect timezone conversion Also change behavior of "YY" and "YYY" the way it is done in the standard conversion. * Convert tm year to real year for calculations in "YY" pattern --------- Co-authored-by: Artyom Ivanov <artyom.ivanov@red-soft.ru>

pavel-zotov · 2024-01-12T16:19:59Z

now we get the expected results.

Am i right in guess that this patch soon will be applied and i could resume testing ?

TreeHunter9 · 2024-01-12T22:57:29Z

I re-read this issue, and @mrotteveel said that HH and HH12 implemented differently from the standard (A.M. and P.M. should be in pattern, not in formatting string, if I understand it correctly), so it need at least one more commit. But if fix of HH and HH12 doesn't need right now, I can make a separate PR with that fix, and the current one can be merged.

pavel-zotov · 2024-01-13T18:58:45Z

HH and HH12 implemented differently from the standard

Maybe, but last two examples are about HH24, not HH12.
And problem raises only when there also time zone is specified in format ('TZR' or 'TZH:TZM'). Without it no problem occurs.

A.M. and P.M. should be in pattern, not in formatting string

How should source data look when we have to specify it together with time zone? Can you provide an example ?

TreeHunter9 · 2024-01-14T09:16:35Z

How should source data look when we have to specify it together with time zone? Can you provide an example ?

Something like this, I guess: cast('4:24:20 +01:00' as time with time zone format 'HH A.M.:MI:SS TZH:TZM')

pavel-zotov · 2024-01-14T10:22:53Z

Well, i will wait for commit to make overall test.
Currently label of this ticket ("Fix-version: 6.x") seems to be set too early (premature).

mrotteveel · 2024-01-14T11:02:58Z

Something like this, I guess: cast('4:24:20 +01:00' as time with time zone format 'HH A.M.:MI:SS TZH:TZM')

That template should trigger an error when parsing the string '4:24:20 +01:00' as it doesn't contain A.M. or P.M.. In a datetime template string, both the A.M. and P.M. template parts specify that you expect the words A.M. or P.M. to occur in that position in the input string, and that would mean the string you're parsing should be '4 A.M.:24:20 +01:00' or '4 P.M.:24:20 +01:00' (which to be clear is a something I would not expect to see in real life input, because most would write that as '4:24:20 A.M. +01:00' or '4:24:20 P.M. +01:00' and thus require template 'HH:MI:SS A.M. TZH:TZM' or 'HH:MI:SS P.M. TZH:TZM'.

And to reiterate, the template part A.M. will match both A.M. and P.M. in the input string, as does the template part P.M..

Note that the SQL specification requires that both <datetime template 12-hour> and <datetime template am/pm> must be present, or both must not occur in the template string. That means you can only parse '4:24:20 +01:00' with HH24, not with HH or HH12:

If CT contains <datetime template 24-hour>, then CT shall not contain <datetime template 12-hour> or <datetime template am/pm>.

If CT contains <datetime template 12-hour>, then CT shall contain <datetime template am/pm> and shall not contain <datetime template 24-hour>.

If CT contains <datetime template am/pm>, then CT shall contain <datetime template 12-hour> and shall not contain <datetime template 24-hour>.

(from: ISO/IEC 9075-2:2023(E) 9.52 Datetime templates)

I have to repeat that I find it worrisome to see this much guessing about how this should be implemented: read the standard and implement it accordingly, don't just guess at things.

…ake it more similar to the SQL standard #2388 (#7881) * Use current TimeStamp for data in stringToDate conversion if it's not specify Also fix RM pattern and change (A/P)M to (A/P).M. * Add more tests * Add TimeStamp validation Also move duplicated code to functions. * Add more unit tests for "YY" and "YYY" patterns * Use Callback for getting current date It's better because we can mock Callback for unit tests. * Fix exception and README description * Add ability to print blr_cast_format * Put a comment about new BLR in the right place * Add information about behavior of string to datetime conversion * Rework old patterns and add new ones Add A.M, P.M., RR and RRRR patterns. Rework YY, YYY, HH and HH12 patterns due to new patterns. Add restriction from SQL standard to format. Fix incorrect error message for mismatched pattern. Fix bug with 0 hours in HH12. * Add more unit tests * Update doc for cast format * Allow specification of log_level for BOOST_TESTS in make * Change enum class to enum in namespace * Switch from plain enum to constexpr values --------- Co-authored-by: Artyom Ivanov <artyom.ivanov@red-soft.ru>

pavel-zotov · 2024-03-31T08:45:23Z

21 results found in 6.0.0.301 that can not be explained.
All of them occurs when try to convert time with timezone to string and back to time with timezone (with presense of 'TZR' in format).
MOST of them use format 'HH A.M.:MI:SS TZR' (or 'HH12 A.M.:MI:SS TZR'), but there are also several (seven) examples with format 'HH24:MI:SS TZR'.
For all examples final timestamp differs from original for exactly one hour ahead.

Example-1:

select cast( cast('03:40:10.0000 Asia/Nicosia' as time with time zone) as varchar(50) format 'HH A.M.:MI:SS TZR') from rdb$database;
03 A.M.:40:10 Asia/Nicosia

select cast('03 A.M.:40:10 Asia/Nicosia' as time with time zone format 'HH A.M.:MI:SS TZR') from rdb$database;
04:40:10.0000 Asia/Nicosia

Example-2:

select cast( cast('02:47:50.0000 Europe/Andorra' as time with time zone) as varchar(50) format 'HH24:MI:SS TZR') from rdb$database;
02:47:50 Europe/Andorra

select cast('02:47:50 Europe/Andorra' as time with time zone format 'HH24:MI:SS TZR') from rdb$database;
03:47:50.0000 Europe/Andorra

Please see attachment with .sql and its log.

gh-2388-wrong-results-when-convert-time_tz-to-str-and-back.zip

pavel-zotov · 2024-03-31T08:56:24Z

PS.
In #a429459b i see this:

* Add more tests
* Add more unit tests for "YY" and "YYY" patterns
...
* Add information about behavior of string to datetime conversion

Where can these tests be found (if, of course, they aren't private) ?
What info about "behavior of string to datetime conversion" can be obtained in current FB-6.x version ?

TreeHunter9 · 2024-03-31T18:46:54Z

Where can these tests be found (if, of course, they aren't private) ?

https://github.com/FirebirdSQL/firebird/blob/master/src/common/tests/CvtTest.cpp
This is unit tests.

What info about "behavior of string to datetime conversion" can be obtained in current FB-6.x version ?

https://github.com/FirebirdSQL/firebird/blob/master/doc/README.cast.format.md#2-string-to-datetime

mrotteveel · 2024-04-01T10:07:29Z

@pavel-zotov Out of curiosity, why are you putting the A.M./P.M. format marker in such an illogical place? I'm not aware of anyone (or any locale) who in practice would want to generate a value like 03 A.M.:40:10, they'd generally want something like 03:40:10 A.M.

pavel-zotov · 2024-04-01T10:54:10Z

why are you putting the A.M./P.M. format marker in such an illogical place?

It's not me. The script that generates [almost] all possible combinations has "found" that :-)

pavel-zotov · 2024-04-01T12:47:33Z

PS.
No errors if we use practically used ('proper') format: 'HH:MM:SS A.M.'.
So, maybe it makes sense to prohibit such exotic permutations as was shown above (for which you have pointed) ?

mrotteveel · 2024-04-01T12:53:09Z

The problem with that is that the SQL template for datetime doesn't prohibit it as far as I can tell from the rules in the standard.

TreeHunter9 · 2024-04-02T08:40:20Z

21 results found in 6.0.0.301 that can not be explained. All of them occurs when try to convert time with timezone to string and back to time with timezone (with presense of 'TZR' in format). MOST of them use format 'HH A.M.:MI:SS TZR' (or 'HH12 A.M.:MI:SS TZR'), but there are also several (seven) examples with format 'HH24:MI:SS TZR'. For all examples final timestamp differs from original for exactly one hour ahead.

Example-1:
select cast( cast('03:40:10.0000 Asia/Nicosia' as time with time zone) as varchar(50) format 'HH A.M.:MI:SS TZR') from rdb$database;
03 A.M.:40:10 Asia/Nicosia

select cast('03 A.M.:40:10 Asia/Nicosia' as time with time zone format 'HH A.M.:MI:SS TZR') from rdb$database;
04:40:10.0000 Asia/Nicosia
Example-2:
select cast( cast('02:47:50.0000 Europe/Andorra' as time with time zone) as varchar(50) format 'HH24:MI:SS TZR') from rdb$database;
02:47:50 Europe/Andorra

select cast('02:47:50 Europe/Andorra' as time with time zone format 'HH24:MI:SS TZR') from rdb$database;
03:47:50.0000 Europe/Andorra
Please see attachment with .sql and its log.

gh-2388-wrong-results-when-convert-time_tz-to-str-and-back.zip

I cannot reproduce this behavior in 6.0.0.305.

pavel-zotov · 2024-04-02T09:22:28Z

Hm-m... it looks weird but i'm also can not reproduce that, on same 6.0.0.301 which i checked 31-mar-2024 :-/
I tried to change my system date from today (02-apr) back to 31-mar, and checked again 6.0.0.301 - but also can't reproduce!

pavel-zotov · 2024-04-04T20:10:58Z

I have one more Q.
This:

set heading off;
SELECT CAST('2021.01.02' AS date FORMAT 'YYYY.YYY.YY') FROM RDB$DATABASE;
SELECT CAST('2024.02.03' AS date FORMAT 'YYYY.YYY.YY') FROM RDB$DATABASE;
SELECT CAST('2025.11.17' AS date FORMAT 'YYYY.YY.YY') FROM RDB$DATABASE;
SELECT CAST('2027.12.19' AS date FORMAT 'YYYY.YY.YYY') FROM RDB$DATABASE;

-- issues:

AFAIU, final part of every line ("-04-04" ) is from current year (2024).
But what about first tokens (2002, 2003, 2017, 2019) ?
And (more interesting): why format allows 'duplicating' of some date/timestamp part ("YYYY", "YYY", "YY") ?

TreeHunter9 · 2024-04-05T06:59:09Z

AFAIU, final part of every line ("-04-04" ) is from current year (2024).

To be more precise, it's from current date, first 04 - current month and second 04 - current day.

And (more interesting): why format allows 'duplicating' of some date/timestamp part ("YYYY", "YYY", "YY") ?

I didn't add such validation because I thought it looked more like "shooting yourself in a foot" problem. Maybe I'm wrong about that.

But what about first tokens (2002, 2003, 2017, 2019) ?

It's using last pattern for YEAR part (YY), so we get these values.

pavel-zotov · 2024-04-14T07:56:28Z

QA-run for snapshot 'WI-T6.0.0.313 Firebird 6.0 aaf5faf' shows that some results are still wrong.
For example:

set bail on;
set heading off;

select cast('02:02:45.9632 A.M. Europe/Kiev' as time with time zone format 'HH12:MI:SS.FF4 A.M. TZR') from rdb$database;
select cast('02:02:45.9632 A.M. Africa/El_Aaiun' as time with time zone format 'HH12:MI:SS.FF4 A.M. TZR') from rdb$database;
----------------------------------------------------------------------------------------------------------------------------
select cast('02:33:55.7139 Europe/Kiev' as time with time zone format 'HH24:MI:SS.FF4 TZR') from rdb$database;
select cast('02:33:55.7139 Africa/Casablanca' as time with time zone format 'HH24:MI:SS.FF4 TZR') from rdb$database;

Output:

02:02:45.9632 A.M. Europe/Kiev                                                                       
02:02:45.9632 Europe/Kiev                      -- OK, expected

02:02:45.9632 A.M. Africa/El_Aaiun                                                                   
03:02:45.9632 Africa/El_Aaiun                  -- ??? (one hour ahead)

02:33:55.7139 Europe/Kiev                                                                            
02:33:55.7139 Europe/Kiev                      -- OK, expected

02:33:55.7139 Africa/Casablanca                                                                      
03:33:55.7139 Africa/Casablanca              -- ??? (one hour ahead)

It seems that problem can raise not only for some time zones but also for concrete values of timestamps (test selects randomly both parameters).

mrotteveel · 2024-04-14T08:16:46Z

And (more interesting): why format allows 'duplicating' of some date/timestamp part ("YYYY", "YYY", "YY") ?

I didn't add such validation because I thought it looked more like "shooting yourself in a foot" problem. Maybe I'm wrong about that.

According to the SQL standard (9.52 Datetime templates of SQL:2023-2) you do need to validate that:

Syntax Rules

Let DT be the DATETIME TYPE and let CT be the TEMPLATE in an application of the Syntax Rules of this Subclause.

CT shall conform to the lexical grammar of a <datetime template>.

CT shall not contain two consecutive <datetime template delimiter>s.

CT shall contain at most one of each of the following: <datetime template year>, <datetime template rounded year>, <datetime template month>, <datetime template day of month>, <datetime template day of year>, <datetime template 12-hour>, <datetime template 24-hour>, <datetime template minute>, <datetime template second of minute>, <datetime template second of day>, <datetime template fraction>, <datetime template am/pm>, <datetime template time zone hour>, and <datetime template time zone minute>.

CT shall not contain both <datetime template year> and <datetime template rounded year>,

If CT contains <datetime template day of year>, then CT shall not contain <datetime template month> or <datetime template day of month>.

If CT contains <datetime template 24-hour>, then CT shall not contain <datetime template 12-hour> or <datetime template am/pm>.

If CT contains <datetime template 12-hour>, then CT shall contain <datetime template am/pm> and shall not contain <datetime template 24-hour>.

If CT contains <datetime template am/pm>, then CT shall contain <datetime template 12-hour> and shall not contain <datetime template 24-hour>.

If CT contains <datetime template second of day>, then CT shall not contain any of the following: <datetime template 12-hour>, <datetime template 24-hour>, <datetime template minute>, <datetime template second of minute>, or <datetime template am/pm>.

If CT contains <datetime template time zone minute>, then CT shall contain <datetime template time zone hour>.

Case:
   a) If DT is DATE, then CT shall not contain <datetime template 24-hour>, <datetime template 12-hour>, <datetime template minute>, <datetime template second of minute>, <datetime template second of day>, <datetime template fraction>, <datetime template am/pm>, <datetime template time zone hour>, or <datetime template time zone minute>.
   b) If DT is TIME WITHOUT TIME ZONE, then CT shall not contain <datetime template year>, <datetime template rounded year>, <datetime template month>, <datetime template day of month>, <datetime template day of year>, <datetime template time zone hour>, or <datetime template time zone minute>.
   c) If DT is TIME WITH TIME ZONE, then CT shall not contain <datetime template year>, <datetime template rounded year>, <datetime template month>, <datetime template day of month>, or <datetime template day of year>
   d) If DT is TIMESTAMP WITHOUT TIME ZONE, then CT shall not contain <datetime template time zone hour> or <datetime template time zone minute>.
  NOTE 512 — All datetime template fields are permitted with TIMESTAMP WITH TIME ZONE.

A <datetime template part> DTP contained in CT is left-delimited if and only if at least one of the following is true:
   a) DTP is the first <datetime template part> contained in CT.
   b) DTP is a <datetime template am/pm> or a <datetime template time zone hour>.
   c) DTP is immediately preceded in CT by a <datetime template delimiter> or <datetime template am/pm>.

A <datetime template part> DTP contained in CT is right-delimited if and only if at least one of the following is true:
   a) DTP is the last <datetime template field> contained in CT.
   b) DTP is a <datetime template am/pm>.
   c) DTP is immediately followed in CT by a <datetime template delimiter>, <datetime template am/pm>, or <datetime template time zone hour>.

A <datetime template part> DTP contained in CT is delimited if it is both left-delimited and right-delimited.

The maximum field length of a <datetime template part> DTP is defined as follows.
   Case:
   a) The maximum field length of a <datetime template 12-hour>, <datetime template 24-hour>, or <datetime template time zone minute> is 2.
   b) The maximum field length of a <datetime template fraction> FF1, FF2, FF3, FF4, FF5, FF6,
FF7, FF8, or FF9 is 1 (one), 2, 3, 4, 5, 6, 7, 8, 9, respectively.
   c) Otherwise, the maximum field length of DTP is the number of characters in DTP.
    NOTE 513 — The maximum field length of <datetime template time zone hour> is 3 (one character for a sign, plus two digits).

The maximum template length of CT is the arithmetic sum of the maximum field lengths of the <datetime template part>s contained in CT.

The minimum field length of a <datetime template part> DTP of CT is
   Case:
   a) If DTP is not delimited, then the maximum field length of DTP.
   b) If DTP is a <datetime template time zone hour>, then 2.
    NOTE 514 — A <datetime template time zone hour> is matched by a sign and one or two digits.
   c) Otherwise, 1 (one).

The minimum template length of CT is the arithmetic sum of the minimum field lengths of all <datetime template part>s contained in CT.

The fractional precision of CT is
   Case:
   a) If CT contains <datetime template fraction> FF1, FF2, FF3, FF4, FF5, FF6, FF7, FF8, or FF9, then the fractional precision is 1 (one), 2, 3, 4, 5, 6, 7, 8, or 9, respectively.
   b) Otherwise the fractional precision is 0 (zero).

Evaluation of the Syntax Rules is terminated and control is returned to the invoking Subclause.

pavel-zotov · 2024-04-14T08:43:51Z

Comparison for timezones 'Africa/Casablanca' vs 'Africa/Algiers' shows that first of them definitely has a problem.
Despite the fact that both timezones have UTC+1 and relate to neighboring countries:
https://upload.wikimedia.org/wikipedia/commons/8/88/World_Time_Zones_Map.png

Please consider two scripts below (yes, some formats look very strange - but they do not affect on result):

Timezone = 'Africa/Casablanca':

set heading off;
set echo on;

select cast('02,02,45.96 A.M. Africa/Casablanca' as time with time zone format 'HH12,MI,SS.FF2 A.M. TZR') from rdb$database;

select cast('02,05,29.7265 A.M. Africa/Casablanca' as time with time zone format 'HH12,MI,SS.FF4 A.M. TZR') from rdb$database;

select cast('02;37;54.1 A.M. Africa/Casablanca' as time with time zone format 'HH;MI;SS.FF1 A.M. TZR') from rdb$database;

select cast('02.51.45.4952 A.M. Africa/Casablanca' as time with time zone format 'HH12.MI.SS.FF4 P.M. TZR') from rdb$database;

select cast('02/29/27.437 A.M. Africa/Casablanca' as time with time zone format 'HH/MI/SS.FF3 P.M. TZR') from rdb$database;

select cast('02/10/04.874 Africa/Casablanca' as time with time zone format 'HH24/MI/SS.FF3 TZR') from rdb$database;

select cast('02,46,05.6 A.M. Africa/Casablanca' as time with time zone format 'HH,MI,SS.FF1 P.M. TZR') from rdb$database;

select cast('02,48,35.92 A.M. Africa/Casablanca' as time with time zone format 'HH12,MI,SS.FF2 P.M. TZR') from rdb$database;

select cast('02,53,13.6081 Africa/Casablanca' as time with time zone format 'HH24,MI,SS.FF4 TZR') from rdb$database;

select cast('02;19;43.9 A.M. Africa/Casablanca' as time with time zone format 'HH12;MI;SS.FF1 P.M. TZR') from rdb$database;

select cast('02:33:55.7139 Africa/Casablanca' as time with time zone format 'HH24:MI:SS.FF4 TZR') from rdb$database;

Timezone = 'Africa/Algiers':

set heading off;
set echo on;

select cast('02,02,45.96 A.M. Africa/Algiers' as time with time zone format 'HH12,MI,SS.FF2 A.M. TZR') from rdb$database;

select cast('02,05,29.7265 A.M. Africa/Algiers' as time with time zone format 'HH12,MI,SS.FF4 A.M. TZR') from rdb$database;

select cast('02;37;54.1 A.M. Africa/Algiers' as time with time zone format 'HH;MI;SS.FF1 A.M. TZR') from rdb$database;

select cast('02.51.45.4952 A.M. Africa/Algiers' as time with time zone format 'HH12.MI.SS.FF4 P.M. TZR') from rdb$database;

select cast('02/29/27.437 A.M. Africa/Algiers' as time with time zone format 'HH/MI/SS.FF3 P.M. TZR') from rdb$database;

select cast('02/10/04.874 Africa/Algiers' as time with time zone format 'HH24/MI/SS.FF3 TZR') from rdb$database;

select cast('02,46,05.6 A.M. Africa/Algiers' as time with time zone format 'HH,MI,SS.FF1 P.M. TZR') from rdb$database;

select cast('02,48,35.92 A.M. Africa/Algiers' as time with time zone format 'HH12,MI,SS.FF2 P.M. TZR') from rdb$database;

select cast('02,53,13.6081 Africa/Algiers' as time with time zone format 'HH24,MI,SS.FF4 TZR') from rdb$database;

select cast('02;19;43.9 A.M. Africa/Algiers' as time with time zone format 'HH12;MI;SS.FF1 P.M. TZR') from rdb$database;

select cast('02:33:55.7139 Africa/Algiers' as time with time zone format 'HH24:MI:SS.FF4 TZR') from rdb$database;

Time values in both scripts are identical (for appropriate statements), scripts differ only in TZ suffix ('Casablanca' vs 'Algiers').
But first script (for Casablanca) will show wrong results (+1 hour ahead) for all statements.

pavel-zotov · 2024-04-14T18:11:47Z

I've done somewhat like 'brute force attack' in order to get full list of time values + time zones which have a problem with converting from string to time with timezone using FORMAT clause in the CAST().
Script performed check for all time zones and all values of MINUTES from scope from '00:00' to '23:59' (i.e. all time values were only accurate to the minute, w/o seconds).

There are only TWO problematic time zones:

Africa/Casablanca
Africa/El_Aaiun

And both of them have only ONE SCOPE of time values which have a problem with CAST(): they start with 02:00 and finish with 02:59. All results for that scope are wrong (one hour ahead).

No other time zones and also no other time scopes in above mentioned TZ (Casablanca and El_Aaiun) caused any problem.

SQL script with all statements that have wrong result see in attached .zip
failed-cast-textual-time-to-time-with-timezone-using-format-clause.sql.zip

asfernandes · 2024-04-14T18:34:07Z

I did not verified if @pavel-zotov tests are correct, I hope @TreeHunter9 does, but look at this:

SQL> select * from RDB$TIME_ZONE_UTIL.TRANSITIONS('Africa/Casablanca', timestamp '2024-01-01', timestamp '2025-12-31');

                                      RDB$START_TIMESTAMP                                         RDB$END_TIMESTAMP RDB$ZONE_OFFSET RDB$DST_OFFSET RDB$EFFECTIVE_OFFSET 
========================================================= ========================================================= =============== ============== ==================== 
2023-04-23 02:00:00.0000 GMT                              2024-03-10 01:59:59.9999 GMT                                            0             60                   60 
2024-03-10 02:00:00.0000 GMT                              2024-04-14 01:59:59.9999 GMT                                            0              0                    0 
2024-04-14 02:00:00.0000 GMT                              2025-02-23 01:59:59.9999 GMT                                            0             60                   60 
2025-02-23 02:00:00.0000 GMT                              2025-04-06 01:59:59.9999 GMT                                            0              0                    0 
2025-04-06 02:00:00.0000 GMT                              2026-02-15 01:59:59.9999 GMT                                            0             60                   60 

SQL> select * from RDB$TIME_ZONE_UTIL.TRANSITIONS('Africa/El_Aaiun', timestamp '2024-01-01', timestamp '2025-12-31');

                                      RDB$START_TIMESTAMP                                         RDB$END_TIMESTAMP RDB$ZONE_OFFSET RDB$DST_OFFSET RDB$EFFECTIVE_OFFSET 
========================================================= ========================================================= =============== ============== ==================== 
2023-04-23 02:00:00.0000 GMT                              2024-03-10 01:59:59.9999 GMT                                            0             60                   60 
2024-03-10 02:00:00.0000 GMT                              2024-04-14 01:59:59.9999 GMT                                            0              0                    0 
2024-04-14 02:00:00.0000 GMT                              2025-02-23 01:59:59.9999 GMT                                            0             60                   60 
2025-02-23 02:00:00.0000 GMT                              2025-04-06 01:59:59.9999 GMT                                            0              0                    0 
2025-04-06 02:00:00.0000 GMT                              2026-02-15 01:59:59.9999 GMT                                            0             60                   60

You have found some timezones that were switching offsets today!

pavel-zotov · 2024-04-14T19:34:33Z

You have found some timezones that were switching offsets today!

Hmm... it seems that you're right:
https://www.zeitverschiebung.net/en/timezone/africa--casablanca
https://www.zeitverschiebung.net/en/timezone/africa--el_aaiun

Latest clock change for both is: TODAY, 14-APR-2024, from 02:00 AM forward to 03:00 AM

pavel-zotov · 2024-04-14T19:57:09Z

PS.
AFAIU, next time we can wait for 'surprise' in 26-APR-2024 (for Egypt).
Source: https://www.worlddata.info/timezones/daylightsavingtimes.php
Result of copy-paste to Excel, applying formulas to get date from string and day number sinve 01-jan + sorting:

dates-for-time-change-in-countries.xlsx

pavel-zotov · 2024-04-14T20:38:21Z

I have one more Q.
In %FB_HOME%/include/firebird/TimeZones.h we can see list of time zones, and among them:

#define fb_tzid_nz                                   64995 /* NZ */
#define fb_tzid_nz_chat                              64994 /* NZ-CHAT */

IMO, this corresponds to

New Zealand Standard Time (NZST)
Chatham Island Standard Time (CHAST)
(see https://www.worlddata.info/oceania/new-zealand/timezones.php)
According to https://www.worlddata.info/timezones/daylightsavingtimes.php , next time when clock must be changed in the New Zaeland is 29-SEP-2024 at 02:00 AM.

And - note - clock must be changed (increased by 1 hour) for BOTH timezones ("countrywide"), but:

NZST (Auckland) has local time = UTC+12
CHAST (Chatham) has local time = UTC +12.75

Now let's look on result of following statements:

select cast('29.09.2024 01:59:59.9999 NZ' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;
2024-09-29 01:59:59.9999 NZ // expected because this time is earlier than 02:00 AM when clock must be changed

select cast('29.09.2024 02:00:00.0000 NZ' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;
2024-09-29 03:00:00.0000 NZ  // expected: this value is exactly equal to the timestamp when the clock should be changed

select cast('29.09.2024 02:00:00.0000 NZ-CHAT' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;
2024-09-29 02:00:00.0000 NZ-CHAT // WHY ? 


select cast('29.09.2024 23:59:59.9999 NZ-CHAT' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-09-29 23:59:59.9999 NZ-CHAT // WHY ??

pavel-zotov · 2024-04-14T21:13:07Z

It seems like there will be many more questions :-)
Here: https://www.worlddata.info/america/mexico/timezones.php - we can see that in Mexico clock must be changed (because of DST) only in: Matamoros, Ojinaga, ..., and Tijuana. All of these cities (regions ?) must change clock as 02:00 AM.

From this: https://www.worlddata.info/america/mexico/timezones.php - we can see that:

In Matamoros Daylight Saving Time has been established on 04/03/1988 -- i.e. it is ACTUAL for nowadays
In Ojinaga Daylight Saving Time has been established on 05/02/1931 -- i.e. it is ACTUAL for nowadays
In Tijuana Daylight Saving Time has been established on 04/01/1931 -- i.e. it is ACTUAL for nowadays

Also, let's note there is Monterrey for which:

In Monterrey Daylight Saving Time has been observed since 1988 until 10/30/2022 -- i.e. it was DISCONTINUED in 2022.

Now - let's check following:

select cast('03.11.2024 01:59:59.9999 America/Matamoros' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 01:59:59.9999 America/Matamoros                


select cast('03.11.2024 02:00:00.0000 America/Matamoros' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 02:00:00.0000 America/Matamoros                


select cast('03.11.2024 23:59:59.0000 America/Matamoros' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 23:59:59.0000 America/Matamoros                


---------------------------------------------------------------------------------------------------

select cast('03.11.2020 01:59:59.9999 America/Monterrey' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2020-11-03 01:59:59.9999 America/Monterrey                


select cast('03.11.2020 02:00:00.0000 America/Monterrey' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2020-11-03 02:00:00.0000 America/Monterrey                


select cast('03.11.2020 23:59:59.0000 America/Monterrey' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2020-11-03 23:59:59.0000 America/Monterrey                



select cast('03.11.2024 01:59:59.9999 America/Monterrey' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 01:59:59.9999 America/Monterrey                


select cast('03.11.2024 02:00:00.0000 America/Monterrey' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 02:00:00.0000 America/Monterrey                


select cast('03.11.2024 23:59:59.0000 America/Monterrey' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 23:59:59.0000 America/Monterrey                


---------------------------------------------------------------------------------------------------

select cast('03.11.2024 01:59:59.9999 America/Ojinaga' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 01:59:59.9999 America/Ojinaga                  


select cast('03.11.2024 02:00:00.0000 America/Ojinaga' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 02:00:00.0000 America/Ojinaga                  


select cast('03.11.2024 23:59:59.0000 America/Ojinaga' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 23:59:59.0000 America/Ojinaga                  


---------------------------------------------------------------------------------------------------

select cast('03.11.2024 01:59:59.9999 America/Tijuana' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 01:59:59.9999 America/Tijuana                  


select cast('03.11.2024 02:00:00.0000 America/Tijuana' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 02:00:00.0000 America/Tijuana                  


select cast('03.11.2024 23:59:59.0000 America/Tijuana' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 23:59:59.0000 America/Tijuana

So, we can see that there are NO changed clock, at all.
But why ?

pavel-zotov · 2024-04-14T21:27:31Z

Same question about Canada (Blanc-Sablon vs Toronto), clock must be changed at 02:00 AM 03-NOV-2024:

-- In Blanc-Sablon Daylight Saving Time has been observed since 1918 until 09/30/1945.
-- In Toronto Daylight Saving Time has been established on 04/14/1918.

select cast('03.11.2024 01:59:59.9999 America/Blanc-Sablon' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 01:59:59.9999 America/Blanc-Sablon // expected


select cast('03.11.2024 02:00:00.0000 America/Blanc-Sablon' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 02:00:00.0000 America/Blanc-Sablon  // expected (because DST actions discontinued since 1945)

---------------------------------------------------------------------------------------------------

select cast('03.11.2024 01:59:59.9999 America/Toronto' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 01:59:59.9999 America/Toronto // expected


select cast('03.11.2024 02:00:00.0000 America/Toronto' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 02:00:00.0000 America/Toronto // Why ?


select cast('03.11.2024 23:59:59.0000 America/Toronto' as timestamp with time zone format 'DD.MM.YYYY HH24:MI:SS.FF4 TZR') from rdb$database;

2024-11-03 23:59:59.0000 America/Toronto // Why ??

firebird-automations added priority: major component: engine type: new feature labels Apr 25, 2021

dyemanov assigned dyemanov and unassigned dyemanov Apr 11, 2023

dyemanov mentioned this issue Jun 16, 2023

Add FORMAT clause to convert datetime types to string and vice versa #2388 #7629

Merged

asfernandes linked a pull request Jun 28, 2023 that will close this issue

Add FORMAT clause to convert datetime types to string and vice versa #2388 #7629

Merged

asfernandes closed this as completed in #7629 Oct 24, 2023

asfernandes assigned TreeHunter9 Oct 24, 2023

asfernandes added the fix-version: 6.0 Alpha 1 label Oct 24, 2023

pavel-zotov added the qa: deferred label Nov 22, 2023

pavel-zotov added qa: done successfully and removed qa: deferred labels Apr 5, 2024

Implement SQL standard FORMAT clause for CAST between string types and datetime types [CORE6507] #2388

Implement SQL standard FORMAT clause for CAST between string types and datetime types [CORE6507] #2388

Comments

firebird-automations commented Mar 8, 2021

firebird-automations commented Mar 8, 2021

firebird-automations commented Mar 8, 2021

omachtandras commented May 20, 2021

mrotteveel commented May 20, 2021

TreeHunter9 commented Jun 16, 2023

pavel-zotov commented Nov 2, 2023 • edited Loading

mrotteveel commented Nov 2, 2023 • edited Loading

dyemanov commented Nov 2, 2023

mrotteveel commented Nov 2, 2023

pavel-zotov commented Nov 3, 2023

mrotteveel commented Nov 3, 2023

TreeHunter9 commented Nov 3, 2023 • edited Loading

asfernandes commented Nov 3, 2023

mrotteveel commented Nov 3, 2023 • edited Loading

mrotteveel commented Nov 3, 2023

asfernandes commented Nov 3, 2023

pavel-zotov commented Nov 22, 2023

pavel-zotov commented Jan 12, 2024

TreeHunter9 commented Jan 12, 2024

pavel-zotov commented Jan 13, 2024

TreeHunter9 commented Jan 14, 2024

pavel-zotov commented Jan 14, 2024

mrotteveel commented Jan 14, 2024 • edited Loading

pavel-zotov commented Mar 31, 2024

pavel-zotov commented Mar 31, 2024 • edited Loading

TreeHunter9 commented Mar 31, 2024

mrotteveel commented Apr 1, 2024

pavel-zotov commented Apr 1, 2024

pavel-zotov commented Apr 1, 2024

mrotteveel commented Apr 1, 2024

TreeHunter9 commented Apr 2, 2024

pavel-zotov commented Apr 2, 2024

pavel-zotov commented Apr 4, 2024

TreeHunter9 commented Apr 5, 2024

pavel-zotov commented Apr 14, 2024

mrotteveel commented Apr 14, 2024 • edited Loading

pavel-zotov commented Apr 14, 2024 • edited Loading

pavel-zotov commented Apr 14, 2024 • edited Loading

asfernandes commented Apr 14, 2024

pavel-zotov commented Apr 14, 2024

pavel-zotov commented Apr 14, 2024

pavel-zotov commented Apr 14, 2024 • edited Loading

pavel-zotov commented Apr 14, 2024

pavel-zotov commented Apr 14, 2024

pavel-zotov commented Nov 2, 2023 •

edited

Loading

mrotteveel commented Nov 2, 2023 •

edited

Loading

TreeHunter9 commented Nov 3, 2023 •

edited

Loading

mrotteveel commented Nov 3, 2023 •

edited

Loading

mrotteveel commented Jan 14, 2024 •

edited

Loading

pavel-zotov commented Mar 31, 2024 •

edited

Loading

mrotteveel commented Apr 14, 2024 •

edited

Loading

pavel-zotov commented Apr 14, 2024 •

edited

Loading

pavel-zotov commented Apr 14, 2024 •

edited

Loading

pavel-zotov commented Apr 14, 2024 •

edited

Loading