liquibase <insert> : Insert current date

Question

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

Answer 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.

Answer 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