I am facing issue while fetching record, The timestamp value is changed after fetching from DB.

In my Oracle DB the column value is "19-OCT-22 02.15.00.000000000 AM" but after fetching it comes as "2022.10.19 00:15:00"

@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy.MM.dd HH:mm:ss")
    private Timestamp startDate; 

I am using JPA repository with springboot to fetch the records.

CodePudding user response：

It looks like a timezone issue. Please share your JPA mapping and the "create table" script. The Java Timezone datatype doesn't accept timezone, probably in your database, the value is stored with timezone.

Some examples of datatype in Oracle BD:

1. TIMESTAMP: Does not store any timezone information. If you enter a timestamp with a time zone then the time zone information is simply truncated and lost.

2. TIMESTAMP WITH TIME ZONE: Stores the timestamp with the time zone information (i.e. either as named region or as UTC-Offset) as you insert the timestamp into the database.

3. TIMESTAMP WITH LOCAL TIME ZONE: Timestamp is stored as DBTIMEZONE (recommended and usually UTC). The timestamp is always and only displayed in the current user session SESSIONTIMEZONE. Thus it does not show any time zone information, because by definition this is always your local time zone.

with more information about your mapping and table, we can help you better.

CodePudding user response：

LocalDateTime worked now.

@Column(name = "STARTDATE") 
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy.MM.dd HH:mm:ss") 
private LocalDateTime startDate; 

DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); 
LocalDateTime timestamp = LocalDateTime.parse(fromDate, format); 
List<JobName> list = repository.findJobNameBylastTime(timestamp);