1

In my application I have the following 2 columns:

start - datetime
end - datetime

Corresponding java fields:

@JsonProperty
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "start")
private Calendar start;

@JsonProperty
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "end")
private Calendar end;

Whenever the front end calls the back end to persist an entity it is always saved as EST time.

E.g. if the time is sent as 12pm it is saved in the DB as 8am.

How can I ensure that the time saved in the DB is always in UTC?

I tried changing the type of the start column to be timestamp, but this did not solve it.

java12399900
  • 1,485
  • 7
  • 26
  • 56
  • 2
    [Calendar](https://stackoverflow.com/questions/48688736/are-java-util-date-and-java-util-calendar-deprecated)? Really? – Seelenvirtuose Jul 21 '21 at 10:25
  • 1
    Use `java.time.OffsetDateTime` (or maybe `java.time.ZonedDateTime`) instead of `java.util.Calendar`. Oh, and check the time zone of (1) your system and (2) the database. – deHaar Jul 21 '21 at 10:25
  • so should I change both calendar >ZonedDatetime in java, and datetime > timestamp in mysql – java12399900 Jul 21 '21 at 10:37

1 Answers1

1

TIMESTAMP data types are stored in UTC in MySql. (DATETIME data types are not.)

When you store them they're translated from the connection's, or server's, time zone setting to UTC. And, when you retrieve them they're translated back.

To make this work you need to ensure the time zone setting is correct on each connection. Something like this will do the trick, if you do it while opening the connection or before handling any TIMESTAMP data.

SET time_zone = 'America/Halifax';

Global web apps (WordPress for example) have a user preference setting for time zone, so each user can see times in their own local time.

If you want to retrieve UTC times, do this first:

SET time_zone = 'UTC';
O. Jones
  • 103,626
  • 17
  • 118
  • 172