`OracleDatabase` uses `SYSTIMESTAMP` instead of `CURRENT_TIMESTAMP`

Vampire commented 10 months ago

Search first

[X] I searched and no similar issues were found

Description

If you have an Oracle database and use CURRENT_TIMESTAMP() as date value, it is translated by Liquibase to SYSTIMESTAMP. This causes the system timezone to be used, instead of the expected database timezone. If you for example have an Oracle database in the Oracle cloud, the system timezone is UTC, but you might have selected CET as DB timezone. If you now use CURRENT_TIMESTAMP(), the UTC time is used.

Steps To Reproduce

Use CURRENT_TIMESTAMP() on an Oracle database where system timezone and db timezone do not match.

Expected/Desired Behavior

db timezone used

Liquibase Version

4.23.1

Database Vendor & Version

Oracle

Liquibase Integration

Library

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

No response

Additional Context

No response

Are you willing to submit a PR?

[ ] I'm willing to submit a PR (Thank you!)

tati-qalified commented 10 months ago

Hello @Vampire, I wasn't able to replicate this issue. I created a table with a "date" type column and a "timestamp" type column, both with DEFAULT CURRENT_TIMESTAMP()

When running update-sql or generate-changelog, the output is: CREATE TABLE T3 (TS date DEFAULT CURRENT_TIMESTAMP, NAME VARCHAR2(100 BYTE)); or CREATE TABLE T3 (TS TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, NAME VARCHAR2(100 BYTE));

If you're getting a different output, please provide me with a step-by-step of what you're doing, in order for me to test it effectively. Thank you Tatiana

Vampire commented 10 months ago

With this:

<insert tableName="T3">
    <column name="TS" valueDate="CURRENT_TIMESTAMP()"/>
</insert>

Liquibase will use SYSTIMESTAMP when executing the insert, due to this line: https://github.com/liquibase/liquibase/blob/7e709fe1c54906e31fb39ff80d46d2e8b9d184ae/liquibase-standard/src/main/java/liquibase/database/core/OracleDatabase.java#L66

tati-qalified commented 10 months ago

I see it now, thank you.

I've verified that the currentDateTimeFunction definition has used SYSTEMTIMESTAMP since at least v4.0.0, so this wasn't caused by any recent PRs. I'll forward this to the dev team so they can evaluate how to move forward. Thanks again, Tatiana

liquibase / liquibase