Question 218429 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – LocalDateTime format ok in JSON, not ok in database

I want to format date-times in a SpringBoot REST API I’m building in the format "dd/MM/yyyy HH:mm:ss.SSS". So I’ve put it in a global constant:

public static final String GLOBAL_DATE_TIME_PATTERN = "dd/MM/yyyy HH:mm:ss.SSS";

and I’ve annotated my DTO’s LocalDateTime field:

@DateTimeFormat(pattern = GLOBAL_DATE_TIME_PATTERN)
@JsonFormat(pattern = GLOBAL_DATE_TIME_PATTERN)
private LocalDateTime createdDateTime;

as well as my entity’s field:

@Column(name = "created_date_time", updatable = false)
@DateTimeFormat(pattern = GLOBAL_DATE_TIME_PATTERN)
@JsonFormat(pattern = GLOBAL_DATE_TIME_PATTERN)
@CreatedDate
protected LocalDateTime createdDateTime;

I’ve POST-ed a few payloads (they’re supposed to represent tasks in the form of cards), and I’m getting the createdDateTime field formatted exactly as I want it in response or after a GET by ID:

{
    "id": 2,
    "name": "JC2",
    .
    .
    .
    "createdDateTime": "29/07/2023 01:07:49.096",
    .
    .
    .
}

Unfortunately, in my database (MySQL) the date is formatted differently:

mysql> select id, created_date_time from card;
+----+----------------------------+
| id | created_date_time          |
+----+----------------------------+
|  1 | 2023-07-29 00:57:51.083969 |
|  2 | 2023-07-29 01:07:49.096333 |
+----+----------------------------+
2 rows in set (0.00 sec)

I would like to have the database date/times formatted as GLOBAL_DATE_TIME_PATTERN dictates. I’m wondering whether the @CreatedDate annotation is in any way interfering. Grateful for any help.

Tags:

2

Answers


  1. 0

    Mysql column and data need to be in string, not date type if you want to store in d/m/Y format. BUT keep in mind that you won’t be able to perform date/time-related queries efficiently. If you need to perform queries based on the date/time values, it’s better to keep the standard datetime format in the database and format the values as needed when fetching them for display in the frontend or API response.

    OR

    You can use this query,

    SELECT *, DATE_FORMAT(created_date_time,'%d/%m/%Y %H:%i:%s.%f') AS created_date_time_formatted
from card;
    Login or Signup to reply.
  2. 0

    Please understand that @CreatedDate,@DateTimeFormat or @JsonFormat is not responsible for formatting dates in the database.

    @JsonFormat used for formatting dates during JSON serialization/deserialization, so it’s responsible for formatting dates in the API responses, not in the DB.

    @CreatedDate used for automatically populating the createdDateTime field with the current date & time when a new entity is persisted & again has nothing to do with format in the DB.

    When a LocalDateTime obj is persisted to the DB, it gets converted to the corresponding date & time data type supported by the DB (like DATETIME or TIMESTAMP) & they are stored in their own internal representation. MySql by default retrieves & displays DATETIME columns in ‘YYYY-MM-DD HH:MM:SS’ format.

    As other answer had pointed out, you can simply change the format in which you retrieve the DATETIME column (like by using DATE_FORMAT() func) or you could store it as VARCHAR in your dd/MM/yyyy HH:mm:ss.SSS format. But storing as a VARCHAR is not recommended.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search