checking java date with postgresql timestamp without timezone

Question

I need to retrieve some data from the db and I need to check if the given date matches the one in the table, this is what I come up with:

@Transactional(readOnly = true)
public UtenteDTO getUtenteFromAsta(
    String nomeGiocatore, String tempoInizio
) throws PlayerNotFoundException, UserNotFoundException, ParseException {
    Giocatore giocatore = giocatoreService.getGiocatoreByNome(nomeGiocatore);
    Date data = df.parse(tempoInizio);
    LocalDateTime ldt = data.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
    Optional<Utente> u = astaRepository.getUtenteFromAstaByGiocatore(giocatore, ldt);
    if (u.isEmpty())
        throw new UserNotFoundException();
    Utente utente = u.get();
    UtenteDTO uDTO = new UtenteDTO();
    uDTO.setEmail(utente.getEmail());
    uDTO.setNome(utente.getNome());
    uDTO.setCognome(utente.getCognome());
    uDTO.setRuolo(false);
    return uDTO;
}

So I am converting the String I get from the front end app, transform it in a java.util.Date and then in a LocalDateTime.

This is the query:

@Query("SELECT ua.utente FROM Asta a, UtenteAsta ua WHERE a.id = ua.id.astaId AND a.giocatore_nome = ?1 AND a.tempo_inizio = ?2")
Optional<Utente> getUtenteFromAstaByGiocatore(Giocatore giocatore, LocalDateTime tempoInizio);

Is this approach correct, or am i missing something?

Edit:

Format of the string from the front end and also stored in db:

2025-01-29 15:09:57.475000

Answer 1

ISO 8601

To directly answer your Question, the Answer by deHaar is correct.

Alternatively, you could skip the formatter. Just replace the SPACE in the middle with a T. The resulting string complies with the ISO 8601 standard formats used by default in java.time.

• SQL uses a SPACE to separate date & time.
• ISO 8601 uses a T to separate date & time.

Example code:

String text = input.replace ( " " , "T" ) ;
LocalDateTime ldt = LocalDateTime.parse ( text ) ;

But you have a much bigger problem.

Wrong data type

You seem to be trying to capture a moment when something started. If so, you are using the wrong data types.

A moment

To represent a moment, a point on the timeline, in Postgres use a column of the type TIMESTAMP WITH TIME ZONE.

Stored in UTC

Any time zone or offset in an input is used by Postgres to adjust to UTC (an offset of zero hours-minutes -seconds from the temporal meridian of UTC). Values in a TIMESTAMP WITH TIME ZONE column are always stored in UTC (offset of zero) despite the misleading name of the type.

Beware: some tools may dynamically apply some time zone to retrieved values. But that is a distortion as Postgres always stores this type in UTC.

java.time.OffsetDateTime

In Java, represent a moment using Instant, OffsotDateTime, or ZonedDateTime . For JDBC specifically, always use OffsetDateTime for database values of type TIMESTAMP WITH TIME ZONE.

OffsetDateTime now = OffsetDateTime.now( ZoneOffset.UTC ) ;
myPreparedStatement.setObject ( … , now ) ;
…
OffsetDateTime odt = myResultSet.getObject ( … , OffsetDateTime.class ) ; 

In such code, the OffsetDateTime instantiated by the JDBC driver will have an offset of zero. See the constant ZoneOffset.UTC.

Not a moment

The Java class LocalDateTime represents only a date with time-of-day while lacking the context of a time zone or offset. Such values are inherently ambiguous. Never try to use this class to represent a moment, a point on the timeline. Never call LocalDateTime.now.

In Postgres, the matching type is TIMESTAMP WITHOUT TIME ZONE.