checking java date with postgresql timestamp without timezone - Stack Overflow

admin管理员组

文章数量:1315368

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

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

• java
• postgresql
• datetime

Share Improve this question Follow edited Jan 30 at 10:26 CarlettoDeiSoffocotti asked Jan 30 at 9:45 CarlettoDeiSoffocottiCarlettoDeiSoffocotti 3711 silver badge44 bronze badges 6

• 2 Why do you actually use java.util.Date when you get the tempoInizio as String? It's much better to use plain java.time if you don't have to create compatibility with legacy code. Use a DateTimeFormatter to parse tempoInizio, what's the expected format? Your use of ZoneId.systemDefault() is a possible source for trouble when the code is expected to be deployed on different systems. – deHaar Commented Jan 30 at 9:57
• 2 So… how does your datetime String look? Like "2025-01-30T10:48:49"? Please provide an example. – deHaar Commented Jan 30 at 9:59
• 4 tl;dr there's LocalDateTime.parse(String, DateTimeFormatter), which means you can skip parsing to a Date and parse directly to a LocalDateTime or any other (possibly more) suitable class from java.time. If there are more suitable classes cannot be said without having seen an example tempoInizio value. – deHaar Commented Jan 30 at 10:07
• 4 What the others are saying. You are starting from earth and taking the detour to hell in order to get to heaven. While the Bible may tell you to do that, don’t here. Throw away SimpleDateFormat and Date and go straight to heaven. – Anonymous Commented Jan 30 at 12:32
• 4 For better handling of Optional instead of the 3 lines where you call u.isEmpty() and u.get() use just one line: Utente utente = u.orElseThrow(UserNotFoundException::new);. – Anonymous Commented Jan 30 at 12:34

 |  Show 1 more comment

2 Answers 2

Sorted by: Reset to default 3

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 from time.
• ISO 8601 uses a T to separate date from 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.

Get rid of java.util.Date, you don't need it in this case.

Instead of

Date data = df.parse(tempoInizio);
LocalDateTime ldt = data.toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDateTime();

create a constant (or somehow else reusable) DateTimeFormatter

private static final DateTimeFormatter DTF = DateTimeFormatter.ofPattern(
                                                 "uuuu-MM-dd HH:mm:ss.SSSSSS",
                                                 Locale.ENGLISH
                                             );

and use it to parse the tempoInizio directly to a LocalDateTime:

LocalDateTime ldt = LocalDateTime.parse(tempoInizio, DTF);

本文标签： checking java date with postgresql timestamp without timezoneStack Overflow