jahra jahra - 1 month ago 12
MySQL Question

Incorrect datetime value while saving data to MySql

I faced a strange problem about saving java

Date
object to MySql db. Here is the error I get:

Incorrect datetime value: '1970-01-01 02:55:00' for column 'start_time' at row 1


My sql script

CREATE TABLE `schedules` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`start_time` timestamp NOT NULL,
`end_time` timestamp NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Part of my entity:

@Column(name = "start_time")
private Date startTime;


When I try to do something like

String time = "02:55"
SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
scheduleEntity.setStartTime(sdf.parse(time));

repository.save(scheduleEntity);


It falls with error. But when I use
"16:00"
string for parsing it works perfectly. I use Spring Boot, Spring Data with Hibernate. Can someone help me?

Answer

Apparently, you want to store only a time portion in your table, yet you use timestamp data type, which stores both date and time and has other special features as well.

If you want to store time only, then use MySQL's time data type:

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

The linked documentation contains a link that explains how time literals are interpreted.

Comments