How do I handle java.sql.Dates across multiple time zones?

Question

I have an Oracle DB that is in US Eastern time zone. I am in US Central time zone. My EJBs use java.sql.Date for date fields. The fields in the DB are Date:

CREATE TABLE "MYSCHEMA"."TBLBURSTASSGN" 
   (    "BURSTASSGN_ID" NUMBER(*,0) NOT NULL ENABLE, 
"BURSTASSGN_TYPEVID" NUMBER(*,0), 
"BURSTASSGN_CONTACTID" NUMBER(*,0), 
"BURSTASSGN_ASSIGNMENTREF" VARCHAR2(50), 
"BURSTASSGN_STARTDATE" DATE, 
"BURSTASSGN_DUEDATE" DATE, 
"BURSTASSGN_INSTRUCTION" VARCHAR2(2048), 
"BURSTASSGN_STATUSVID" NUMBER(10,0), 
"BURSTASSGN_SUMMARYONLYYN" CHAR(1) DEFAULT 'N' NOT NULL ENABLE);

When I enter a date into the EJB in Central, and save it to the Eastern DB, it is saved correctly eg. I enter January 31, 2018 and it is stored 2018-01-31 00:00:00. But when I reload my app and it pulls from the DB, it displays January 30, 2018.

Application

BurstAssignment burstAssignment = SessionHelper.getSession().getBurstAssignment(assignmentId);
System.out.println("StartDate " + burstAssignment.getStartDate());

SessionBean

@Override
public BurstAssignment getBurstAssignment(Integer id) {
    if (id == null) { return null; }
    return em.find(BurstAssignment.class, id);
}

EntityBean

import com.kable.newsstand.kdsejb.audit.Auditable;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.EqualsBuilder;
import org.apache.commons.lang3.builder.HashCodeBuilder;
import java.sql.Date;
import javax.validation.constraints.NotNull;
import org.hibernate.annotations.Type;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.SequenceGenerator;

@Entity
@Table(name="TBLBURSTASSGN")
public class BurstAssignment extends Auditable implements Serializable {

    private static final long serialVersionUID = 1L;

    @Column(name="BURSTASSGN_DUEDATE")
    protected Date dueDate = null;
    @Column(name="BURSTASSGN_CONTACTID")
    protected Integer contactId = null;
    @Column(name="BURSTASSGN_INSTRUCTION")
    protected String instruction = null;
    @Column(name="BURSTASSGN_STARTDATE")
    protected Date startDate = null;
    @Column(name="BURSTASSGN_ASSIGNMENTREF")
    protected String assignmentRef = null;
    @Column(name="BURSTASSGN_TYPEVID")
    protected Integer typeVid = null;
    @Id
    @SequenceGenerator(name="BURSTASSIGNMENT_SEQ", sequenceName="TBLBURSTASSGN_ID_SEQ", initialValue = 1, allocationSize = 1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="BURSTASSIGNMENT_SEQ")
    @Column(name="BURSTASSGN_ID", updatable=false, nullable=false)
    protected Integer id = null;
    @Column(name="BURSTASSGN_STATUSVID")
    protected Integer statusVid = null;
    @Type(type="yes_no")
    @NotNull
    @Column(name="BURSTASSGN_SUMMARYONLYYN")
    protected Boolean summaryOnlyYN = false;

    public BurstAssignment() { super(); }

    public BurstAssignment(BurstAssignment burstAssignment) {
        setDueDate(burstAssignment.getDueDate());
        setContactId(burstAssignment.getContactId());
        setInstruction(burstAssignment.getInstruction());
        setStartDate(burstAssignment.getStartDate());
        setAssignmentRef(burstAssignment.getAssignmentRef());
        setTypeVid(burstAssignment.getTypeVid());
        setId(burstAssignment.getId());
        setStatusVid(burstAssignment.getStatusVid());
        setSummaryOnlyYN(burstAssignment.getSummaryOnlyYN());
    }

    public Date getDueDate() {
        if (dueDate == null) { return null; }
        return new Date(dueDate.getTime());
    }

    public void setDueDate(Date dueDate) {
        if (dueDate == null) { this.dueDate = null; }
        else { this.dueDate = new Date(dueDate.getTime()); }
    }

    public Integer getContactId() {
        return contactId;
    }

    public void setContactId(Integer contactId) {
        this.contactId = contactId;
    }

    public String getInstruction() {
        return instruction;
    }

    public void setInstruction(String instruction) {
        this.instruction = instruction;
    }

    public Date getStartDate() {
        if (startDate == null) { return null; }
        return new Date(startDate.getTime());
    }

    public void setStartDate(Date startDate) {
        if (startDate == null) { this.startDate = null; }
        else { this.startDate = new Date(startDate.getTime()); }
    }

    public String getAssignmentRef() {
        return assignmentRef;
    }

    public void setAssignmentRef(String assignmentRef) {
        this.assignmentRef = assignmentRef;
    }

    public Integer getTypeVid() {
        return typeVid;
    }

    public void setTypeVid(Integer typeVid) {
        this.typeVid = typeVid;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getStatusVid() {
        return statusVid;
    }

    public void setStatusVid(Integer statusVid) {
        this.statusVid = statusVid;
    }

    public Boolean getSummaryOnlyYN() {
        return summaryOnlyYN;
    }

    public void setSummaryOnlyYN(Boolean summaryOnlyYN) {
        this.summaryOnlyYN = summaryOnlyYN;
    }

    @Transient
    @Override
    public String getPrimaryKeyDisplay() {
        StringBuilder sb = new StringBuilder();
        if (id == null) {
            sb.append(" id: null");
        } else {
            sb.append(" id: " + id.toString());
        }
        return sb.toString();
    }
    @Transient
    @Override
    public String toString() {
        return new ToStringBuilder(this).append("dueDate", dueDate).append("contactId", contactId).append("instruction", instruction).append("startDate", startDate).append("assignmentRef", assignmentRef).append("typeVid", typeVid).append("id", id).append("statusVid", statusVid).append("summaryOnlyYN", summaryOnlyYN).toString();
    }

    @Transient
    @Override
    public int hashCode() {
        return new HashCodeBuilder().append(dueDate).append(contactId).append(instruction).append(startDate).append(assignmentRef).append(typeVid).append(id).append(statusVid).append(summaryOnlyYN).toHashCode();
    }

    @Transient
    @Override
    public boolean equals(Object obj) {
        if (obj == null) { return false; }
        if (obj == this) { return true; }
        if (obj.getClass() != getClass()) { return false; }
        BurstAssignment that = (BurstAssignment) obj;
        return new EqualsBuilder().append(dueDate, that.dueDate).append(contactId, that.contactId).append(instruction, that.instruction).append(startDate, that.startDate).append(assignmentRef, that.assignmentRef).append(typeVid, that.typeVid).append(id, that.id).append(statusVid, that.statusVid).append(summaryOnlyYN, that.summaryOnlyYN).isEquals();
    }

}

Option 1: Sanity. Store everything in UTC (if necessary store the local offset to UTC as well). Option 2: Store everything in local time (and store the offset to UTC or you're stuck). — Elliott Frisch, May 01 '18 at 22:44
All other issues aside, always store dates at UTC, retrieve as UTC, and display as appropriate for the user. — KevinO, May 01 '18 at 22:44
Perhaps you should **read the documentation**, e.g. chapter "[Datetime Data Types and **Time Zone Support**](https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG004)" of the "[Oracle Database Globalization Support Guide](https://docs.oracle.com/cd/E11882_01/server.112/e10729/toc.htm)". Correct time zone handling is a **broad** topic. — Andreas, May 01 '18 at 22:56

bcr666 · Answer 1 · 2018-05-03T17:23:59.107

0

I fixed it, for me, by changing the Time Zone of the local VM to be Eastern to match the DBTIMEZONE.

-Duser.timezone=-5:00

Update: My application is deployed as a Web Start application, which I can't seem to get to accept the above line in the jnlp, or on the commandline. So I inserted the following into my app's main class.

{
    TimeZone.setDefault(TimeZone.getTimeZone("-5:00"));
}

edited May 03 '18 at 17:23

answered May 02 '18 at 20:39

bcr666

2,157
1
12
23

score -1 · Answer 2 · answered May 01 '18 at 23:09

-1

Your problem is that your getters and setters are calling getTime(), which converts the Date to a number of milliseconds, then the constructor for Date removes the time component for the date. If you've got different time zones, this can cause a discrepancy of one hour to be converted to a discrepancy of one day.

Don't be too fancy with your getters and setters. They should just look like this.

public Date getDueDate() {
    return dueDate;
}

public void setDueDate(Date dueDate) {
    this.dueDate = dueDate;
}

answered May 01 '18 at 23:09

Dawood ibn Kareem

77,785
15
98
110

I have implemented this, and same result. – bcr666 May 02 '18 at 14:59
I have also tried answer 2 here https://stackoverflow.com/questions/36641664/storing-dates-in-hibernate-as-utc-time-dates with no result. – bcr666 May 02 '18 at 15:00

How do I handle java.sql.Dates across multiple time zones?

2 Answers2