CODEWITHSUNDEEP
sqlliquibase
Jeff
Jeff

Reputation: 639

Liquibase: How to set the default value of a date column to be "now" in UTC format?

How do you set the default value of a date column to be "now" in UTC format? I think the answer involves the defaultValueComputed attribute on the column element.

The documentation states:

defaultValueComputed A value that is returned from a function or procedure call. This attribute will contain the function to call.

What langauge is the function referred to supposed to be written in? Java? Is the function supposed to be the database vendor -specific date function I want to use? Is there any more documentation I can read on this topic?

Upvotes: 58

Views: 94246

Answers (8)

Pavel S.
Pavel S.

Reputation: 1224

In MySQL, to use a DATETIME column with fractions of second like DATETIME(6) (microseconds precision), use default value of NOW(6) (caution: CURRENT_TIMESTAMP(6) for some reason produces an error with me using liquibase 3.5.3):

<column name="created_at" type="DATETIME(6)" defaultValueComputed="NOW(6)" >
   <constraints nullable="false" />
</column>

Note that the value will be stored internally in UTC, but read using the server's timezone settings (@@global.time_zone, @@session.time_zone).

Upvotes: 7

Dominika
Dominika

Reputation: 458

This should be:

<property name="now" value="now()" dbms="mysql,h2"/>
<property name="now" value="current_timestamp" dbms="postgresql"/>
<property name="now" value="sysdate" dbms="oracle"/>
<property name="now" value="getdate()" dbms="mssql"/>

<changeSet author="me" id="sample_usage_demo">
    <addColumn schemaName= "dbo" tableName="demo_table" >
        <column name="demo_column" type="datetime" defaultValueDate="${now}">
            <constraints nullable="false" />
        </column>
    </addColumn>
</changeSet>

Upvotes: 22

Jens
Jens

Reputation: 6383

Maybe this topic in the liquibase forum will help?

I think defaultValueComputed will take a database specific function to express "now". In mySQL it would be CURRENT_TIMESTAMP so it could look like this:

<createTable tableName="D_UserSession">
    <column name="ts" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP"/>
</createTable>

(Copied from the forum post.)

Upvotes: 57

MichaelCkr
MichaelCkr

Reputation: 690

Due to the fact, that the requested timezone UTC is not mentioned in all of the answers here, I'd like to state another set of solutions for different database vendors.

Especially, the current answers here does not state the correct solution for Oracle.

<changeSet logicalFilePath="my_changeset.xml"
   id="1"
   author="me"
   dbms="mariadb,h2">
   <addColumn tableName="MY_TABLE">
      <column name="MY_ZONED_DATE_TIME_COLUMN"
         type="timestamp(6)"
         defaultValueComputed="now()">
         <constraints nullable="false"/>
      </column>
   </addColumn>
</changeSet>

<changeSet logicalFilePath="my_changeset.xml"
   id="1"
   author="me"
   dbms="postgresql">
   <addColumn tableName="MY_TABLE">
      <column name="MY_ZONED_DATE_TIME_COLUMN"
         type="timestamp(6)"
         defaultValueComputed="timezone('UTC', now())">
         <constraints nullable="false"/>
      </column>
   </addColumn>
</changeSet>

<changeSet logicalFilePath="my_changeset.xml"
   id="1"
   author="me"
   dbms="oracle">
   <addColumn tableName="MY_TABLE">
      <column name="MY_ZONED_DATE_TIME_COLUMN"
         type="timestamp(6)"
         defaultValueComputed="sys_extract_utc(systimestamp)">
         <constraints nullable="false"/>
      </column>
   </addColumn>
</changeSet>

(One may use properties, like showed in Dominika's answer, but we had some really bad experiences with properties in liquibase.)

Summary:

  • now() is fine for MariaDB, MySql and H2v
  • now() is not completely fine for H2, I just got a correct result starting the h2 database with UTC like jdbc:h2:mem:./my_database;TIME ZONE=UTC (h2database in version 2.x needed). Then now() is working for sure.
  • I'm not sure about current_timestamp on PostgreSQL, but timezone('UTC', now()) works. :)
  • In Oracle, sysdate (mentioned in a few other answers here) is not enough, see as well "How to get UTC value for SYSDATE on Oracle", but sys_extract_utc(systimestamp) does the trick.

Upvotes: 1

joe
joe

Reputation: 373

You should probably use timestamp with time zone as that will keep the timestamps in UTC as opposed to local server time, which might be problem if you have a multi-region setup.

You can readmore about the timezone part on this Stack Overflow post.

  databaseChangeLog:
  - changeSet:
      id: 007
      author: joe
      changes:
        - addColumn:
            tableName: my_table
            columns:
              - column:
                  name: created_at
                  type: timestamp with time zone
        -  addDefaultValue:
             columnName:  created_at
             defaultValueComputed:  now()
             tableName:  my_table

Upvotes: 0

Pedro Madrid
Pedro Madrid

Reputation: 1977

This worked for me:

<property name="now" value="UNIX_TIMESTAMP()" dbms="mysql"/>
<column name="ts" type="timestamp" valueDate="${now}"/>

I found it thanks to this answer: https://stackoverflow.com/a/9100388/3107952

Upvotes: 2

Sagar Jani
Sagar Jani

Reputation: 297

As liquibase is common changelog for any database, to make it generic you should not depend on any specific database like oracle, postegres, mysql instead it should be generic enough to work for any/every database.

Below is how it should be implemented :

<column name="time" type="${type.datetime}" defaultValueComputed="${column.datetime.defaultValue}"/>

This should work for all databases, for oracle, it inserts SYSTIMESTAMP as DATA_DEFAULT.

Upvotes: -1

Neha
Neha

Reputation: 133

This works with SQlite:

<column name="last_updated_at" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
</column>

Adding '$now' didn't work for me. I am using SQlite as the DB.

Upvotes: 4

Related Questions