0

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.

2 Answers2

1

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.

  • ``` CREATE TABLE "MASTERDATA"."JOB_STATUS" ( "ID" NUMBER(*,0) NOT NULL ENABLE, "STARTDATE" TIMESTAMP (6) NOT NULL ENABLE, "ENDDATE" TIMESTAMP (6), "STATUS" VARCHAR2(20 CHAR) NOT NULL ENABLE, "REMARKS" VARCHAR2(255 CHAR), "JOBID" NUMBER(*,0) NOT NULL ENABLE ) ``` This is table script. – Gagan Noor Singh Oct 19 '22 at 05:05
  • 1
    Your configuration seems ok! TIMESTAMP(6) is the default. You can try to check the Oracle timezone config, even these datatype doesn't work with it. But I really recommend you try to use 'java.time.LocalDateTime' instead of 'java.sql.Timestamp'. 'java.sql.Timestamp' is a "thin wrapper around 'java.util.Date' that allows the JDBC API to identify this as an SQL TIMESTAMP value" If you don't wanna handle timezone, use LocalDateTime datatype. – Fabricio Entringer Moreira Oct 19 '22 at 06:06
  • 1
    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 list = repository.findJobNameBylastTime(timestamp); – Gagan Noor Singh Oct 19 '22 at 06:27
  • 1
    Good to know @GaganNoorSingh. `java.sql.Timestamp` reflects UTC its methods to apply conversions. This causes no end of confusion. From the SQL perspective, it makes no sense to convert a SQL TIMESTAMP value to some other time zone as a TIMESTAMP has no time zone to convert from. `java.time.LocalDateTime` is also a bare date time. It does not have a time zone, exactly like SQL TIMESTAMP. None of its methods apply any kind of time zone conversion which makes its behavior much easier to predict and understand. – Fabricio Entringer Moreira Oct 19 '22 at 06:36
  • Thanks for your timely response :) Appreciate it !! – Gagan Noor Singh Oct 19 '22 at 06:37
0

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);