0

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?

jahra
  • 1,173
  • 1
  • 16
  • 41
  • 2
    If you just want to record time, not date and time, then use the `time` data type in MySQL, not a timestamp. – Shadow Oct 27 '16 at 08:19
  • Sounds like a good idea! I tested and it works. Can you post it as full answer with some short helpful comments on how to store time and map it to java? Thanks in advance! @Shadow – jahra Oct 27 '16 at 08:34
  • could you try this sdf.setTimeZone(TimeZone.getTimeZone("UTC")); – Daniyar Oct 27 '16 at 08:34

2 Answers2

1

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.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • And what is the corresponding type for mapping in java? `java.util.Date`? When I persist it, my console logs contains WARN message `Incorrect time value: '1970-01-01 00:10:00' for column 'start_time' at row 1` – jahra Oct 27 '16 at 08:43
  • I believe you need to use the `@Temporal(TemporalType.TIME)` annotation to mark that the column is of time data type, or use `java.sql.Time` as the java data type. But I'm no real expert on java and hibernate. – Shadow Oct 27 '16 at 08:48
-1

Your column type is TIMESTAMP

11.3.1 The DATE, DATETIME, and TIMESTAMP Types

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Solution you can change start_time and end_time columns type to DATETIME directly in DB or drop table and change you entity like this

@Column(name = "start_time", columnDefinition="DATETIME")
@Temporal(TemporalType.TIMESTAMP)
private Date startTime;

hope it will help

Community
  • 1
  • 1
Daniyar
  • 815
  • 1
  • 9
  • 22
  • But '1970-01-01 02:55:00' is after '1970-01-01 00:00:01'. So what is the problem? – jahra Oct 27 '16 at 08:25
  • I guess it depends on your timezone, e.g. if +4 it is before than '1970-01-01 00:00:01' UTC – Daniyar Oct 27 '16 at 08:27
  • Hi Daniyar. You saved me. This was the reason why `preparedStatement.setTimestamp(paramIndex, new java.sql.Timestamp(0))` wasn't working. Though I can't get what's happening `update.setTimestamp(paramIndex, new java.sql.Timestamp(3601000))` works for me (below doesn't). It is GMT+1h+1sec. Hmmm?!? – JackLeEmmerdeur Apr 19 '17 at 16:51