Considerations for designing a time field in a mysql table _ MySQL

Considerations for mysql table design time fields:

I haven't worked on the jdbc coding and database table design of mysql for a few years. I just got started and found it was a problem during the test. here I am referring to the design problem of the corresponding time field, generally, we require that the time field be saved to seconds. Therefore, we discard the mysql date field, select the timestamp type field, and select create_time as the field. the default value is CURRENT_TIMESTAMP.

Field type definition: create_time timestamp default CURRENT_TIMESTAMP comment 'creation time, format: date ',

Table structure definition:

/* ===================================================== =======================================* // * Table: task_detail * // * ====================================== =========================*/create table task_detail (id int not null auto_increment comment' primary Key ', taskName varchar (200) comment 'task name', start_time timestamp comment 'start time', end_time timestamp comment 'end time', task_start_time timestamp comment' task start time ', task_end_time timestamp comment 'task end time', cost_timesecond int comment' time consumed, measured in seconds, create_time timestamp default CURRENT_TIMESTAMP comment' creation time, in the format of 'date ', primary key (id); alter table task_detail comment 'task execution detaillist ';


As a result, when performing jdbc to save data, all fields with null values are inserted by default.
This part may not be clearly stated. suppose we need to set end_time to Null, that is, insert through prepareStatement
After the stmt. setTimestamp (3, null) statement, it is not a null value,
create_time          
Take the default value.

In this case, an error occurs with the data I actually want to store, so I have been checking the cause of the problem.
MySQl has multiple data types that indicate date and time. YEAR indicates the YEAR, DATE indicates the DATE, TIME indicates the TIME, DATETIME and TIMESTAMP indicate the DATE and practice. Their comparison is as follows:
TEAR. the number of bytes is 1 and the value range is "bytes 1--2155"
DATE, 4 bytes, value range: "1000-01-01--9999-12-31"
TIME. The value ranges from-838: 59: 59--838: 59: 59"
DATETIME, 8 bytes, value range: "1000-01-01 00:00:00 -- 9999-12-31 23:59:59"
TIMESTAMP. the number of bytes is 4 and the value range is "197001000000001--20108119111407"
 When the inserted value exceeds the valid value range, the system reports an error and inserts the zero value into the database.
When you change the data table structure to the following, it is normal to store the data.
/* ===================================================== =======================================* // * Table: task_detail * // * ====================================== =========================*/create table task_detail (id int not null auto_increment comment' primary Key ', taskName varchar (200) comment 'task name', start_time datetime comment 'start time', end_time datetime comment 'end time', task_start_time datetime comment 'task start time ', task_end_time datetime comment 'task end time', cost_timesecond int comment' time consumed, in seconds ', create_time timestamp default CURRENT_TIMESTAMP comment 'creation time, in the format of date ', primary key (id); alter table task_detail comment 'task execution detaillist ';

This is mainly because only one field in a mysql data table must be
timestamp 
Type, and the default value is set.