42

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?

Jeff
  • 469
  • 1
  • 6
  • 8

6 Answers6

41

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

Jens
  • 5,615
  • 1
  • 45
  • 71
  • 2
    `defaultValueComputed="to_timestamp('2015-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')"` also works with Oracle – Vadzim Jan 20 '16 at 11:42
  • 1
    Note: this will only set the default value as the CURRENT_TIMESTAMP, not when on update change the value(like last_updated_at) as CURRENT_TIMESTAMP, checked with mysql and mariadb both same behaviour – Ntwobike Mar 13 '18 at 10:59
7

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

Pavel S.
  • 1,058
  • 1
  • 11
  • 25
3

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.

Neha
  • 123
  • 6
2

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

axiopisty
  • 4,555
  • 6
  • 37
  • 65
Pedro Madrid
  • 1,682
  • 16
  • 32
  • 4
    Hey Pedro, perhaps you could accept people's suggested edits. You have people saying that ${now} didn't work, but that's because you left out the fact that people need to define 'now' for each database they support: – NealeU May 03 '17 at 09:09
1

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.

Sagar Jani
  • 191
  • 7
0

I used function the database vendor. For Oracle it`s a sysdate:

<column name="create_date" type="DATETIME" valueDate="sysdate" defaultValueComputed="sysdate" />
0x5a4d
  • 720
  • 1
  • 6
  • 20