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

@Mark To be fair, his response is a different answer than the one he's thanking. — Ryan Shillington, Jul 05 '12 at 18:29

score 68 · Answer 1 · edited Apr 09 '16 at 10:28

68

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.

edited Apr 09 '16 at 10:28

Neek

7,181
3
37
47

answered Feb 01 '12 at 07:11

Nathan Voxland

15,453
2
48
64

1

I find that defaultValueFunction isn't allowed in my changelog. According to the popup that Netbeans offers me, I'm only allowed the following attributes that start with 'default': defaultBoolean defaultDate defaultComputed defaultNumeric. I'm running liquibase 2.0.3, the most recent version, and have tried using the exact changelog header as shown in the 'changelog parameters' page you linked to. Are you sure defaultValueFunction is available in the latest release, or is this a feature on trunk? – Neek Feb 07 '12 at 07:26
It may be new in trunk, or something that should be added since there is a valueFunction. defaultValueNumeric should work as well. That also should tell liquibase to not bother quoting the value. – Nathan Voxland Feb 07 '12 at 07:52
Hi Nathan Voxland, I suffer the same issue since i cannot find the valueFuntion. There is not such thing on liquibase website about that value either. Can you give me some suggestion to solve this issue ? – Thai Tran Feb 24 '12 at 04:32
@NathanVoxland, Do you know if the property is accessible from a csv file, as we are using a csv file to input all of our data. – Ben Green Apr 17 '15 at 14:58
Attribute 'defaultValueFunction' is not allowed to appear in element 'column'. - in Oracle. – Witold Kaczurba Aug 14 '18 at 15:03
In liquibase 3.9.0 defaultValueFunction is not allowed here. I'm on sqlserver database and tried using GETDATE() and CURRENT_TIMESTAMP in the value of the property "now". Then trying to use defaultValueNumeric or defaultValueDate I'm getting null value for the date and using valueDate liquibase is parsing and failing. – Plàcid Masvidal Dec 03 '20 at 10:55
@NathanVoxland Hi, I appreciate it has been some time since this was posted. However, I have implemented your solution in my project, and all worked fine at first. However, this was within my local environment, when my manager merged my branch into master, the build failed on our UAT version. We believe this is due to a difference in version between my SQL and the SQL used by our UAT. I tried simply adding a second property for the ``dbms="mysql"`` but it did not work. Do you have a potential solution for this? Thanks – LondonMassive Aug 31 '21 at 09:29

score 18 · Answer 2 · edited Jul 25 '17 at 05:16

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

score 5 · Answer 3 · answered Feb 23 '12 at 10:06

5

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.

answered Feb 23 '12 at 10:06

Adam D

51
1

This seems to work for MySQL too, except use "now()" instead of "CURRENT TIMESTAMP". – Ryan Shillington Jul 05 '12 at 18:28

score 2 · Answer 4 · answered Feb 20 '14 at 05:59

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>

score 2 · Answer 5 · answered Aug 14 '18 at 15:09

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>

score 0 · Answer 6 · edited Feb 19 '14 at 10:40

0

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.

edited Feb 19 '14 at 10:40

BenMorel

34,448
50
182
322

answered Jan 17 '14 at 22:04

raafaar

108
7

score 0 · Answer 7 · answered Feb 06 '14 at 14:46

0

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>

answered Feb 06 '14 at 14:46

Nic

13
3

score 0 · Answer 8 · answered Sep 22 '21 at 07:40

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>

score 0 · Answer 9 · edited Oct 19 '22 at 08:07

0

Below solution worked for me with mysql DB.

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

edited Oct 19 '22 at 08:07

Adriaan

17,741
7
42
75

answered Oct 19 '22 at 07:59

Pranith Code

1
2

score 0 · Answer 10 · answered Dec 04 '22 at 23:42

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>

liquibase : Insert current date

10 Answers10

Linked