Java MYSQL Timestamp with millieconds issue

Question

I am using MySQL and have a column type TimeStamp. I am not able to store or retrieve milliseconds.

Any help please ? Thanks!

public static Date getBeginAndEndTime(String time) {        
    DateTimeFormatter format = DateTimeFormatter.ofPattern("HHmmssSSS");    
    LocalTime lt = LocalTime.parse(time, format);       
return Timestamp.valueOf(lt.atDate(LocalDate.now()));
}

Ex: Time value coming as is "140833222" and when I stored in database it looks like "2020-08-26 14:08:33" missing last milliseconds "222".

I need to store and retrieve including milliseconds.

Are you able to store data in MySQL? In java side time data is ok I think. You need to change in MySQL side. — Eklavya, Aug 26 '20 at 19:34

score 0 · Answer 1 · answered Aug 26 '20 at 19:19

0

You should use the datatype BIGINT as it allows you to store 8 bytes. The DATE field is generally used for just that, Date and DateTime implementations like LocalDateTime. Since you're using a LocalDate and subsequently a LocalTime you're better off just using a DATE type. Your method effectively becomes;

public static Date getBeginAndEndTime(String time) {     
    // cache this, it's a thread-safe object
    DateTimeFormatter format = DateTimeFormatter.ofPattern("HHmmssSSS"); 
     
    return LocalTime.parse(time, format).atDate(LocalDate.now()));
}

answered Aug 26 '20 at 19:19

Jason

5,154
2
12
22

If we store it as BIGINT can we get the Date object back when retrieving that includes the time with milliseconds ? – user3067524 Aug 26 '20 at 19:39
If you store it as a BIGINT, effectively a `long` or `8 bytes`, you're able to get back the exact `long` value that you inserted. So if you can convert to a Date from the time then you can convert from a time (long) to a Date. – Jason Aug 26 '20 at 19:48

Java MYSQL Timestamp with millieconds issue

1 Answers1