Travis Smith Travis Smith - 1 year ago 46
SQL Question

Storing datetime in a mysql server play framework

I have a Play application connected to a mysql server, in the models I have some attributes that I would like to be saved as DateTimes, however using java.sql.Timestamp produces attributes in my evolutions for the creation of a tables

create table delivery (
id bigint auto_increment not null,
deleted tinyint(1) default 0,
description varchar(500),
notes varchar(1000),
account_id bigint,
customer_id bigint,
sender_id bigint,
recipient_id bigint,
delivery_status_id bigint,
delivery_type_id bigint,
package_type_id bigint,
item_type_id bigint,
call_date datetime(6),
pickup_date datetime(6),
delivery_date datetime(6),
no_of_pieces integer,
cust_type integer,
payment_type integer,
way_bill integer,
created_time date,
modified_time date,
createdby_id bigint,
modifiedby_id bigint,
constraint pk_delivery primary key (id))

which in turn leads to the mysql error:

We got the following error: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '(6), modified_time datetime(6), createdby_id
bigint, mo' at line 21 [ERROR:1064, SQLSTATE:42000], while trying to
run this SQL script:

I learned that it was the size parameter (6) causing the problem and was able to avoid it by using java.sql.Date (since it does not produce in the evolutions) has anyone else experienced this and know a way of storing a DateTime under these conditions?

Answer Source

I was able to get around the problem by using the @Column annotation to define the the type in sql.

    @Column(columnDefinition = "datetime")
    public Timestamp createdAt;