Using liquibase changelog to create a table with a datetime field, how come that field become a text type

Question

I created a table using liquibase databaseChangeLog:

      <changeSet id="1" author="person1">
        <createTable tableName="schedule">
         <column name="id" type="varchar(20)">
                <constraints primaryKey="true" nullable="false"/>
         </column>
         <column name="worktime" type="datetime">
         </column>
  </createTable>
  </changeSet>

However when verifying my sqlite database, the table schema becomed below:

sqlite> .schema schedule
CREATE TABLE schedule (
id TEXT NOT NULL, 
worktime TEXT, 
CONSTRAINT PK_SCHEDULE 
PRIMARY KEY (id));

You see both varchar and datatime were changed to text type. What I should add into changeSet table definition to make it work right. By the way, I am using liquibase-1.9.3.jar. Now I have to correct them directly from sqlite DB. Thanks.

score 2 · Answer 1 · answered Jul 05 '12 at 16:31

2

SQLite does not have a datetime datatype. It only subports numbers, text and blob:

Quote from http://www.sqlite.org/datatype3.html

SQLite does not have a storage class set aside for storing dates and/or times

answered Jul 05 '12 at 16:31

But when I open my sqlite database, and create a table with datetime, varchar data type, it is created without any problem. – user1342336 Jul 05 '12 at 17:57
@user1342336: what is a "datetime, varchar" type? – Jul 05 '12 at 21:18
See below a table's schema in sqlite: sqlite> .schema test CREATE TABLE test(id integer not null, type varchar(10), shiptime datetime, Primary key(id)); sqlite> I don't quite understand why there are only 3 data types? Please give more detail. Thanks. – user1342336 Jul 05 '12 at 22:17
@user1342336: well it's what the developers of SQLite chose to implement. It's more or less explained in the link I posted – Jul 05 '12 at 22:34

Using liquibase changelog to create a table with a datetime field, how come that field become a text type

1 Answers1