Overriding current date/time function

General Discussion
1

Hi,

we use Liquibase on Oracle database. Our problem is that Liquibase uses SYSDATE function as standard function for getting the timestamp of executed change set. The smallest precision of SYSDATE function is second and we have problems when multiple changes are done within one second and we issue tag database command. Then multiple changes are marked with the tag. It even happend that user applyed more changes with more than one tag database command and the later tag overwritten the previous tag because all was done in the same second. The result was that one tag wasn’t present in DATABASECHANGELOG table at all! The user could not rollback to that tag.

Therefore I wanted to find out how to use CURRENT_TIMESTAMP function instead of SYSDATE function. CURRENT_TIMESTAMP function gives time with fractions of seconds. I found the parameter -currentDateTimeFunction which should override the current date/time function, but it doesn’t. The method getCurrentDateTimeFunction() in liquibase-1.9.5-src/src/java/liquibase/database/OracleDatabase.java class simply returns “SYSDATE” and doesn’t take the parameter -currentDateTimeFunction into account.

So I suggest the following implementation of getCurrentDateTimeFunction() method (useful for all supported databases):

    public String getCurrentDateTimeFunction() {
        if (currentDateTimeFunction == null) {
            return “SYSDATE”;
        } else {
            return currentDateTimeFunction;
        }
    }

This modification allows really to override the current date/time function by means of parameter -currentDateTimeFunction.

And when the issue http://liquibase.jira.com/browse/CORE-588 is resolved, overriding will work in command line mode either :slight_smile:

Jirka

2

The upcoming 2.0 release also helps this problem by adding a separate execution_order column that can be used as a sub-order for the date_executed to grab the last executed change.

Would there be any reason to not change the default getCurrentDateTimeFunction to CURRENT_TIMESTAMP for oracle?

One thing you could do for now is use the databaseClass parameter and pass your own subclassed version of OracleDatabase that changes the return value of getCurrentDateTimeFunction() or just re-build a modified version of liquibase. 

Nathan

3
Would there be any reason to not change the default getCurrentDateTimeFunction to CURRENT_TIMESTAMP for oracle?

I think the default getCurrentDateTimeFunction can be changed to CURRENT_TIMESTAMP for Oracle. The column DATABASECHANGELOG.DATEEXECUTED has the type TIMESTAMP for Oracle anyway so CURRENT_TIMESTAMP function should be supported in versions of Oracle that support TIMESTAMP data type.

After some invetigation I see that SYSTIMESTAMP function would be better. CURRENT_TIMESTAMP gives session timestamp/date. SYSTIMESTAMP gives database date. The two obviously may be in different timezones.

Jirka

4

Thanks for looking into it.  I changed it to use systimestamp.

Nathan