CODEWITHSUNDEEP
liquibase
Ramya
Ramya

Reputation: 591

liquibase <insert> : 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

Upvotes: 40

Views: 69300

Answers (10)

Foxhound
Foxhound

Reputation: 605

Neek's answer works well (although I had to use valueComputed), however, issues can arise with substitution in other parts of the code such as unit testing, so the following approach as per the Liquidbase documentation worked:

<insert tableName="Emp" schemaName="XYZ" dbms="oracle">  
    <column name="profile_last_update" value="sysdate"/>  
</insert>
<insert tableName="Emp" schemaName="XYZ" dbms="postgresql, mysql">  
    <column name="profile_last_update" value="now()"/>  
</insert>

Upvotes: 0

Pranith Code
Pranith Code

Reputation: 1

Below solution worked for me with mysql DB.

<column name="LAST_MODIFIED_DATE" valueComputed="CURRENT_TIMESTAMP"/>

Upvotes: 0

Chamith
Chamith

Reputation: 129

First you have to set dbms in insert tag.

then for date use valueDate field in column tag.

 <changeSet  author="name"  id="id_1">
    <insert tableName="table_name" dbms="oracle">
        <column  name="id" value="71"/>
        <column  name="description" value="test"/>
        <column  name="start_date" valueDate="2020-01-01"/>
        <column  name="end_date" valueDate="2021-01-01"/>
    </insert>
</changeSet>

Upvotes: 0

Witold Kaczurba
Witold Kaczurba

Reputation: 10505

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>

Upvotes: 2

Ramya
Ramya

Reputation: 591

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

Upvotes: 19

Nathan Voxland
Nathan Voxland

Reputation: 15773

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.

Upvotes: 73

Gourav Singla
Gourav Singla

Reputation: 1806

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>

Upvotes: 2

raafaar
raafaar

Reputation: 108

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.

Upvotes: 0

Nic
Nic

Reputation: 13

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>

Upvotes: 0

Adam D
Adam D

Reputation: 61

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.

Upvotes: 6

Related Questions