Saving and retrieving time from client to database

Question

In one of my web application I have to schedule an event. My server, client and database are in different timezone. The client will schedule an event and the server has to execute the event.

How do I do this?

Currently from client, I am sending the time of event in milliseconds since the epoch: (with getTime() method of Date in javascript)

In server I convert it to a date object with Java, and store this as a Date type value in database.

In database, the value stored is one day behind the day I gave from client. Why is it like that? How can I store it in servers time?

Answer 1

Yours is a duplicate of many other Questions. So I'll be brief.

There are two ways to represent three kinds of date-time values:

• Store and exchange date-time values in UTC if actual moment has passed ( payment made, invoice received, court papers served, shipment arrived, etc.), or if in the future you know you want a specific moment on the timeline regardless if politicians redefine the time zone such as changing Daylight Saving Time (DST). For Java, use Instant class. In SQL use TIMESTAMP WITH TIME ZONE.
• Store and exchange date-time values without zone/offset for scheduling future events far enough out in the future that politicians may change the time zone rules (dental appointment next year, interview in two months, party announcement, etc.). Politicians around the world have shown a penchant for twiddling with our clocks, often with little warning, sometimes only several weeks advance notice. For Java, use LocalDateTime class. In SQL, use TIMESTAMP WITHOUT TIME ZONE. To present a schedule, generate transient data in Java using ZonedDateTime class, via Instant::atZone method.
• Store and exchange date-time values without zone/offset for non-specific events, such as the start of Christmas or saying "all our factories in Delhi, Düsseldorf, & Detroit, will close for a half-day at noon on October 30th this year" where we mean noon at each of their respective local times rather than at a simultaneous moment. For Java, use LocalDateTime class. In SQL, use TIMESTAMP WITHOUT TIME ZONE.

As a programmer and sysadmins, learn to think in UTC and in 24-hour time. Forget about your own parochial time zone while on the job. Keep a second clock set to UTC in the office.

How do you know the user’s expected/desired time zone? Ultimately the only sure way is to ask her/him.

When serializing to text, use standard ISO 8601 formats.

Avoid the terrible old date-time classes such as java.util.Date and java.sql.Date, etc. These are now legacy, supplanted by the excellent industry-leading java.time classes.

An Instant represents a moment on the timeline in UTC.

Instant instant = Instant.ofEpochMilli( millis ) ;

For presentation to user, adjust the UTC into a time zone they expect/desire. Think of this like internationalization, where you store references to a key, then for presentation use the retrieved key to look up a localized text value.

Never use 3-4 character pseudo-time zones. True time zones have a continent/region name such as Asia/Kolkata and Pacific/Auckland.

ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

Note that databases vary widely in their handling of date-time. The SQL standard barely touches on the subject. Study the docs and conduct experiments to be sure you understand it's behavior. Ditto for your JDBC driver. Tip: Postgres has some of the best support for date-time types and functions.

With JDBC 4.2 and later, exchange data with database via java.time objects, by calling:

• PreparedStatement::setObject
• ResultSet::getObject

Example code

myPStmt.setObject.( … , myInstant ) ;

…and…

Instant instant = myResultSet.getObject( … , Instant.class ) ;

Answer 2

When you're storing time, your best bet is to store it everywhere in epoch time (or unix time if you prefer), and deserialize it on the front end. This means that you can have confidence that you've persisted the correct time, and then you can convert it on the front end. Without knowing the particulars, i suspect that the date object you're creating in Java is causing the offshift, and then you're storing the wrong date value in the database.