liquibase : Insert current date

问题:

I am trying to insert data using liquibase insert tag. It works fine when I am inputing a number to value tag . But I am looking for a simple function that will take care of default date (current DateTime of database) even when I don't have it as part of my table definition.

Eg:

<changeSet id="abc_1" author="Me">
<insert tableName="Emp" schemaName="XYZ">
    <column name="name" value="Me"/>
    <column name="create_date" value ="1328055111692"/>
    <column name="profile_last_update" value="currentDateTimeFunction"/>
    <column name="group_name" value="BlahBlah"/>
</insert>
</changeSet>

here <column name="create_date" value ="1328055111692"/> works fine and it gets inserted in to the database. I also tried using <defaultValueDate> and <valueDate> but they also need some date input in specified format.

I am looking for some function like currentDateTimeFunction that would be converted to UNIX_TIMESTAMP() or SYSDATE or now() based on type of database I am using. Please help me.

Thank you, Ramya

回答1:

What you you will have to do is use changelog parameters and define a "now" or "current_timestamp" parameter that is replaced per database type.

At the top of your <databaseChangeLog>, normally just outside your <changeset>, add per-database definitions of the property like:

  <property name="now" value="sysdate" dbms="oracle"/>
  <property name="now" value="now()" dbms="mysql"/>
  <property name="now" value="now()" dbms="postgresql"/>

then in your changesets use

<column name="Join_date" defaultValueFunction="${now}"/>

Notice the use of defaultValueFunction that will let liquibase know not to parse it as a date or quote it.

回答2:

Thank you for your reply. it was helpful. Below is what I did and it worked for me.

<property name="now" value="UNIX_TIMESTAMP()" dbms="mysql"/>
<changeSet id="emp_1" author="Me">
<insert tableName="Emp" schemaName="XYZ">
    <column name="EmpName" value="abc"/>
    <column name="Join_date" valueDate="${now}"/>
    <column name="Profile_last_update" valueDate="${now}"/>
    <column name="group_name" value="BlahBlah"/>
</insert>
</changeSet>

Thanks again, Ramya

回答3:

If you are using DB2 then you could do the following:

<changeSet id="emp_1" author="Me">
<insert tableName="Emp" schemaName="XYZ">
    <column name="EmpName" value="abc"/>
    <column name="Join_date" valueDate="CURRENT TIMESTAMP"/>
    <column name="Profile_last_update" valueDate="CURRENT TIMESTAMP"/>
    <column name="group_name" value="BlahBlah"/>
</insert>
</changeset>

The resulting SQL is not quoted so it just calls the DB2 function.

回答4:

There is a simple way of doing it given as below

you can simply use valueDate="now()" for MySQL. Like in my case I did:

    <changeSet id="emp_1" author="Me">
       <insert tableName="Emp" schemaName="XYZ">
         <column name="last_updated_at" valueDate="now()"></column>
       </insert>
    </changeset>

回答5:

Liquibase + Oracle: use valueComputed="SYSDATE"

Worked with Oracle 11g:

    <insert tableName="SOMETABLE">
        <column name="ID" valueComputed="SOMETABLE_SEQ.NEXTVAL" />
        <column name="USER_ID" value="123" />
        <column name="CREATED_DATE" valueComputed="SYSDATE" />      
    </insert>

回答6:

You can use valueNumeric to execute functions, as it will prevent the wrapping of quotes around the value.

<column name="id" valueNumeric="uuid_generate_v4()"></column>

回答7:

The answers posted by Ramya and Adam D, works for PostgreSQL too.

Using the "property" tag, I had to change the dbms to dbms="postgresql" and to use a PostgreSQL function for "value" (CURRENT_TIMESTAMP in my case).

Without the "property" tag, I used valueDate="CURRENT_TIMESTAMP" in the "column" tag.

I'm using PostgreSQL 9.1.9 and Liquibase 2.0.5.