Oracle datetimetz incorrectly parsed due to disagreement between OraclePlatform->getDateTimeTzFormatString() and Oracle driver's InitializeSession

[wayne530]

Bug Report

Q	A
Version	all versions >= 3.7.0 (I did not check prior versions)

Summary

This issue was documented in #2332 however simply because a workaround exists, I would not consider the issue "fixed" as everyone using datetimetz will encounter this issue when using Oracle and as a result, will have many corrupted datetimetz values that need to be corrected even after the workaround is discovered and applied. tl;dr This is a major issue with a relatively trivial fix so rather than rely on a workaround (which requires digging through your issues to find), how about just fixing it in all actively maintained version branches?

Current behaviour

Timestamps that have a negative offset from GMT, for example PST (UTC-8) or PDT (UTC-7) are incorrectly parsed as positive offsets from GMT. For example, datetimetz values with a -08:00 offset are stored in Oracle as +08:00.

How to reproduce

Reproducing this issue is quite straightforward. The Oracle InitializeSession middleware sets NLS_TIMESTAMP_TZ_FORMAT to YYYY-MM-DD HH24:MI:SS TZH:TZM:

                $connection->exec(
                    'ALTER SESSION SET'
                        . " NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"
                        . " NLS_TIME_FORMAT = 'HH24:MI:SS'"
                        . " NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"
                        . " NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM'"
                        . " NLS_NUMERIC_CHARACTERS = '.,'",
                );

while OraclePlatform->getDateTimeTzFormatString() uses:

    public function getDateTimeTzFormatString(): string
    {
        return 'Y-m-d H:i:sP';
    }

Thus, if I wish set set a datetimetz from the current datetime in my location at the time of this writing, which is 2024-07-15T11:16:00-07:00, I would take the DateTime instance and format it to a string using the format provided by OraclePlatform, or 2024-07-15 11:16:00-07:00.

Connect to an Oracle database using credentials that allow you to create tables and insert. Initialize the session as the Oracle middleware would:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIME_FORMAT = 'HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM' NLS_NUMERIC_CHARACTERS = '.,';

Create a table with a single column that is a TIMESTAMP WITH TIME ZONE type:

CREATE TABLE test (created_at TIMESTAMP WITH TIME ZONE);

Insert a row into the table and provide the string formatted datetimetz as OraclePlatform would provide:

INSERT INTO test (created_at) VALUES ('2024-07-15 11:16:00-07:00');

Now view the stored row:

SELECT * FROM test;

Expected output:

2024-07-15 11:16:00 -07:00

Actual output:

2024-07-15 11:16:00 +07:00

Expected behaviour

datetimetz values should be stored with the correct UTC offset. Hopefully this is not controversial.

The fix

The fix is quite straightforward. Make OraclePlatform->getDateTimeTzFormatString() and Oracle middleware's InitializeSession agree on a datetimetz format, whether that is with OR without a space. For example, leave OraclePlatform as is and simply update InitializeSession as follows:

                $connection->exec(
                    'ALTER SESSION SET'
                        . " NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"
                        . " NLS_TIME_FORMAT = 'HH24:MI:SS'"
                        . " NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"
                        . " NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SSTZH:TZM'"
                        . " NLS_NUMERIC_CHARACTERS = '.,'",
                );