Handling time zone in web application

Question

In our web application we need to show and enter date time information for different countries in different time zone. Right now, we are maintaining separate web server and separate database (oracle 11g) for each country.

We are planning to merge all into one portal with single database (oracle 11g). This portal should capture/display date and time in user local time zone.

So far, I have searched about this, I got below suggestion.

1) set web server's and database server's time zone to UTC and while fetching data (data and time) convert into user local time zone.

If you suggest this approach then please clarify the following specific questions.

• most of the time we are capturing date alone, is it require to capture date and time along with time zone always?

• while storing date and time where we need to convert user local time zone to UTC in javascript/java/oracle?

• while fetching date and time where we need to convert UTC to user
  local time zone query itself/java/java script?

• many place we have reports to show based on date column such as
  today/current month/date range.how we can handle this(input - user local time zone - database in UTC)?

• which data type we have to use for date field (date/timestamp/timestamp with time zone/timestamp with local time zone)?

2) capture date and time in both user local time zone and UTC. Stored as separate columns, user local time zone will be used for display purpose and UTC will be used for business logic.

If you suggest this approach then please clarify the following specific questions.

• Is it common practice to store the user local time zone and UTC?

• which column i have to check condition while fetching reports to show based on date column such as today/current month/date range?

• which data type we have to use for date column
  (date/timestamp/timestamp with time zone/timestamp with local time
  zone)?

thanks in advance

Answer 1

Read the Question Daylight saving time and time zone best practices. Yours is basically a duplicate.

Servers in UTC

Yes, generally servers should have their OS set to UTC as the time zone, or if not provided use GMT or the Reykjavík Iceland time zone. Your Java implementation probably picks up this setting as its own current default time zone.

Specify time zone

But do not depend on the time zone being set to UTC. A sysadmin could change it. And any Java code in any thread of any app within your JVM can change the JVM’s current default time zone at runtime by calling TimeZone.setDefault. So instead, make a habit of always specifying the desired/expected time zone by passing the optional argument in your Java code.

I consider it a design flaw that any date-time framework would make the time zone optional. Being optional creates endless amounts of confusion because programmers, like everybody else, unconsciously think in terms of their own personal time zone unless prompted. So all too often in date-time work no attention is paid to the issue. Add on the problem that the JVM default varies. By the way, ditto for Locale, same problems, should always be specified explicitly.

UTC

Your business logic, data storage, and data exchange should almost always be done in UTC. Nearly every database has a feature for adjusting any input into UTC and storing in UTC.

When presenting a date-time to a user, adjust into the expected time zone. When serializing a date-time value, use the ISO 8601 string formats. See the Answer by VickyArora for Oracle specifically (I am a Postgres person). Be sure to read the doc carefully, and practice by experimenting to fully understand your database's behavior. The SQL spec does not spell out very much in this regard, and behavior varies widely.

java.sql

Remember that when using Java and JDBC, you will be using the java.sql.Timestamp and related data types. They are always in UTC, automatically. In the future expect to see JDBC drivers updated to directly use the new data types defined in the java.time framework built into Java 8 and later.

java.time

The old classes are outmoded by java.time. Learn to use java.time while avoiding the old java.util.Date/.Calendar and make your programming life much more pleasant.

Until your JDBC driver is updated, you can use the conversion convenience methods built into java.time. See examples next, where Instant is a moment in UTC and ZonedDateTime is an Instant adjusted into a time zone.

Instant instant = myJavaSqlTimestamp.toInstant();
ZoneId zoneId = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );

To go the other direction.

java.sql.Timestamp myJavaSqlTimestamp = java.sql.Timestamp.from( zdt.toInstant() );

If you need original time zone, store it

If your business requirements consider the original input data’s time zone to be important, to be remembered, then store that explicitly as a separate column in your database table. You can use an offset-from-UTC, but that does not provide full information. A time zone is an offset plus a set of rules for the past, present, and future handling of anomalies such as Daylight Saving Time. So a proper time zone name is most appropriate such as America/Montreal.

Date-only is ambiguous

You said you collect many date-only values, without time-of-day and without time zone. The class for that in java.time is LocalDate. As with LocalTime and LocalDateTime, the “Local…” part means no particular locality, so therefore no time zone, and so not a point on the timeline -- has no real meaning.

Keep in mind that a date-only value is ambiguous by definition. At any given moment, the date varies around the world. For example, just after midnight in Paris France is a new day but in Montréal Québec the date is still “yesterday”.

Usually in business some time zone is implicit, even unconsciously intuited. Unconscious intuition about data points tends not to work well over the long term, especially in software. Better to make explicit what time zone was intended. You could store the intended zone alongside the date such as another column in database table, or your could make a comment in your programming code. I believe it would vastly better and safer to store a date-time value. So how do we transform a date-only into a date-time?

Often a new day is the moment after midnight, the first moment of the day. You might think that means the time-of-day 00:00:00.0 but not always. Daylight Saving Time (DST) and possibly other anomalies may push the first moment to a different wall-clock time. Let java.time determine the correct time-of-day for first moment going through the LocalDate class and its atStartOfDay method.

ZoneId zoneId = ZoneId.of( "America/Montreal" );
LocalDate today = LocalDate.now( zoneId );
ZonedDateTime todayStart = today.atStartOfDay( zoneId );

In some business contexts a new day may be defined (or assumed) to be business hours. For example, say a publisher in New York means 9 AM in their local time when they say “the book draft is due by January 2nd”. Let's get that time-of-day for that date in that time zone.

ZoneId zoneId = ZoneId.of( "America/New_York" );
ZonedDateTime zdt = ZonedDateTime.of( 2016 , 1 , 2 , 9 , 0 , 0 , 0 , zoneId );

What does that mean for the author working in New Zealand? Adjust into her particular time zone for presentation to her by calling withZoneSameInstant.

ZoneId zoneId_Pacific_Auckland = ZoneId.of( "Pacific/Auckland" );
ZonedDateTime zdt_Pacific_Auckland = zdt.withZoneSameInstant( zoneId_Pacific_Auckland );

Database

For database storage we transform into an Instant (a moment on the timeline in UTC) and pass as a java.sql.Timestamp as seen earlier above.

java.sql.Timestamp ts = java.sql.Timestamp.from( zdt.toInstant() );

When retrieved from the database, transform back to a New York date-time. Convert from java.sql.Timestamp to an Instant, then apply a time zone ZoneId to get a ZonedDateTime.

Instant instant = ts.toInstant();
ZoneId zoneId = ZoneId.of( "America/New_York" );
ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );

If your database driver complies with JDBC 4.2 or later, you may be able to pass/fetch the java.time types directly rather than convert to/from java.sql types. Try the PreparedStatement::setObject and ResultSet::getObject methods.

Answer 2

Here I have clarified the following specific questions.

Q. Most of the time we are capturing date alone, is it require to capture date and time along with time zone always?

A. Yes

Q. while storing date and time where we need to convert user local time zone to UTC in javascript/java/oracle?

A. Not convert during data save, save as it with source date+time+zone

Q. While fetching date and time where we need to convert UTC to user local time zone query itself/java/java script?

A. Always convert to display in local time zone OR UTC format where application opened.

Q. Many place we have reports to show based on date column such as today/current month/date range.how we can handle this(input - user local time zone - database in UTC)?

A. The system should provide setting option to user for date time display in what format, either local where application opened or UTC. All is done on front end only.

Q. Which data type we have to use for date field (date/timestamp/timestamp with time zone/timestamp with local time zone)?

A. Timestamp

So in short, save datetime in source time zone and convert based upon user preferences either in local where page opened or UTC format. Means, conversion will be done through script for display only. The region where product is being popular can also be find.

Answer 3

Use TIMESTAMP WITH LOCAL TIME ZONE
if you want the database to automatically convert a time between the database and session time zones.

Stores a date and time with up to 9 decimal places of precision. This datatype is sensitive to time zone differences. Values of this type are automatically converted between the database time zone and the local (session) time zone. When values are stored in the database, they are converted to the database time zone, but the local (session) time zone is not stored. When a value is retrieved from the database, that value is converted from the database time zone to the local (session) time zone.