UTC time in database whose type is 'timestamp without timezone' to show in UTC in response

Question

I have a field in postgres whose type is "timestamp without timezone". It is UTC time in database which I want to show in API response.

I used the following code to show it in same UTC timezone but it is adding my local time offset in API response-

// date from db in UTC is 2018-06-06 09:59:04.103
Date createdDateTime = description.getCreatedDateTime();
SimpleDateFormat isoFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
isoFormat.setTimeZone(TimeZone.getTimeZone("UTC"));
try {
        Date date = isoFormat.parse(createdDateTime.toString());
        description.setCreatedDateTime(date);
        } catch (ParseException e) {
            LOGGER.error("An exception has occured in parsing .", e);
        }

My creation time in IST- 2018-06-06 15:16:04.103 IST I am still getting the output with offset in response: Jun 6, 2018 8:46:52 PM

I am using hibernate to fetch the data from table.

Answer 1

java.util.Date::toString applies zone

You are likely being confused by the well-intentioned but unfortunate design decision of the java.util.Date::toString method to dynamically applying the JVM’s current default time zone while generating a String to represent the value of the Date which is actually always in UTC by definition. One of many reasons to avoid this terribly troublesome old class.

Wrong data type in database

I have a field in postgres whose type is "timestamp without timezone". It is UTC time in database which I want to show in API response.

This is a contradiction in terms. A SQL-standard TIMESTAMP WITHOUT TIME ZONE column does not hold a UTC value. That data type lacks any concept of time zone or offset-from-UTC. (Such UTC values should have instead been stored in a column of type TIMESTAMP WITH TIME ZONE.)

The values you inserted may have been intended for UTC, but you lost that fact once stored to the database.

java.time

Avoid java.sql.Timestamp

There is no need to be using java.sql.Timestamp. That legacy class should be avoided. As of JDBC 4.2 and later, we can directly exchange java.time types with the database. Hibernate supports java.time.

LocalDateTime

Lacking any zone/offset, you should retrieve those values using LocalDateTime class. That class also lacks any concept of time zone or offset-from-UTC.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

OffsetDateTime

If you want to assume that retrieved value represents a moment in UTC, apply a ZoneOffset to get a OffsetDateTime object. For UTC specifically, we can use the predefined constant ZoneOffset.UTC.

OffsetDateTime odt = ldt.atOffset( ZoneOffset.UTC ) ;

Not ZonedDateTime

While the ZonedDateTime class will technically work here in place of the OffsetDateTime class, its use is inappropriate.

A time zone is much more than an offset. A zone is a history of the past, present, and future changes to the offset used by the people of a specific region. In contrast, an offset-from-UTC is merely a number of hours and minutes, nothing more, nothing less.

So using a ZonedDateTime in this context of UTC is misleading and potentially confusing.

Instant

Now, in your OffsetDateTime object, you have a moment in UTC.

If you know you want to work only in UTC from there, extract a Instant. An Instant is always in UTC by definition. When representing moments in UTC, generally use Instant. If you need more flexibility such as in generating strings in various formats, switch to using OffsetDateTime.

Instant instant = odt.toInstant() ;

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 2

This column type should return a Timestamp type in Java (or a LocalDateTime with JDBC 4.2+). That timestamp will contain the original date, but in your default timezone. So you need to apply a UTC timezone to it.

java.sql.Timestamp t = ...;
ZonedDateTime zdt = t.toLocalDateTime() //remove time zone information
                     .atZone(ZoneOffset.UTC); //this is actually a UTC time

At this point, you can keep using java.time objects (no reason not to if you can) or you can convert the ZonedDateTime to a java.util.Date with:

java.util.Date date = Date.from(zdt.toInstant());