0

I have application in which I have set the timezone to UTC as follows:

// set default time zone to UTC
TimeZone.setDefault(TimeZone.getTimeZone(ZoneOffset.UTC));

This is fine for all date fields that we are storing in database as UTC. But one date is already coming in UTC so above timezone will again set it in UTC.

How to avoid setting it again in UTC?

Some code-

public static void main(String[] args) throws DatatypeConfigurationException {
        DatatypeFactory datatypeFactory  = DatatypeFactory.
         newInstance();
        // set default time zone to UTC
        TimeZone.setDefault(TimeZone.getTimeZone(ZoneOffset.UTC));
        // coming from external program in UTC but assigned it here for reference
        long someTimeToBeInUTC = 1528371000000L;
        GregorianCalendar start = new GregorianCalendar();
        start.setTimeInMillis(someTimeToBeInUTC);
        XMLGregorianCalendar startXmlCalendar  = 
        datatypeFactory.newXMLGregorianCalendar(start);
        System.out.println(startXmlCalendar.
        toGregorianCalendar().getTime());

    }

Think like I have a time instant someTimeToBeInUTC irrespective of a timezone and then because of the line TimeZone.setDefault(TimeZone.getTimeZone(ZoneOffset.UTC)) it sets it in UTC.

Then in hibernate I have the property in hbm.xml of the table where I am storing this value-

<property name="startTime" type="timestamp">
            <column name="start_time" length="29" />
        </property>

The type of startTime is timestamp without timezone in postgres. So when we save then it again converts it in UTC.

sjain
  • 23,126
  • 28
  • 107
  • 185
  • 2
    I don't understand. What's the problem? The output seems fine. – Michael Jun 05 '18 at 12:08
  • The problem is `someTimeToBeInUTC` is already in UTC coming from external program. So the line `TimeZone.setDefault(TimeZone.getTimeZone(ZoneOffset.UTC));` sets it again in UTC. – sjain Jun 05 '18 at 12:09
  • 1
    What do you mean "sets it again"? Is your problem that it's "inefficient"? Because it isn't. And anyway, a timestamp is usually an instant in time irrespective of a timezone. – Michael Jun 05 '18 at 12:10
  • @Michael- the instant is irrespective of timezone but then when I save it then it is not saved as it is. It is getting saved with the difference of my local timezone i.e. it is again applying my local timezone difference and then getting saved. Please check my updated question. – sjain Jun 05 '18 at 12:19
  • If I remove the line `TimeZone.setDefault(TimeZone.getTimeZone(ZoneOffset.UTC))` then it will correctly save the time in UTC but then other date fields which are already coming without UTC from external program will get saved with my timezone (not in UTC). – sjain Jun 05 '18 at 12:21
  • 1
    Wild guess about what your problem really is: you normally receive time values in local time and have a conversion to UTC implemented somewhere (obviously not in the code you are showing), but you get UTC time values from some source now and therefore your conversion mishandles these UTC time values?!? – Matthias Jun 05 '18 at 12:25
  • 3
    Please take a step back. Tell us **the result you are getting** and **the result you are expecting** with respect to the example you have given us. – Michael Jun 05 '18 at 12:25
  • "But one date is already coming in UTC so above timezone will again set it in UTC." How do you know it is already in UTC? It's a datetime object that contains timezone? If yes, the question is irrelevant, the database should recognize the timezone in the object. If you get a timestamp without a timezone declaration but you know this source provides UTC in every single time, then as soon as the timestamp enters to your app, convert it to a datetime object with zone declaration -> problem gone. – Gabor Garami Jun 05 '18 at 12:33
  • @Michael- The current time is 6:41 PM here. The UI takes the time from user as input in UTC - `1:12 PM UTC`. So I want to save it in postgres as `1:12 PM` without any further conversion to UTC (because it is already in UTC). But I am getting it further converted in UTC as I see in database. So its 2 times conversion in UTC. – sjain Jun 05 '18 at 13:15
  • What you are getting from the user, is that in format `1528371000000L` or in format `1:12 PM UTC` or in some third format? And how would it look in the cases where it is not in UTC and thus needs conversion? (Seems you are at offset +05:30 from UTC.) – Ole V.V. Jun 05 '18 at 13:20
  • @OleV.V.- You are absolutely correct. I am getting in format `1528371000000L`. Then I am using `XMLGregorianCalendar` to set it. I am at offset +05:30 from UTC. The other dates are triggers at database level like created_ts and modified_ts for that row in table. – sjain Jun 05 '18 at 13:25
  • 1
    A timestamp like `1528371000000L` is milliseconds since the epoch of Jan 1, 1970 at 00:00:00 UTC. So provided that your values are correct, they are independent of time zone, and you don’t have a problem. – Ole V.V. Jun 05 '18 at 21:11

2 Answers2

1

Time in milliseconds has nothing about time zone.

Time in milliseconds is exact point in time regardless where you use it in Calendar, Date or Timestamp objects.

Please read Java documentation carefully. It represents always milliseconds passed from epoch (1/1/1970 00:00:00 UTC).

Time Zone has effect only when other representation used from those objects. e.g. from toString() methods, get fields from Calendar etc.)

UPDATE Let say you have time in milleseconds as 1528371250000L...

then you can have two Calendars (they are TimeZone aware) one in UTC one, in Eastern

see the full code example to illustrate what's going on when the same time in millisecond set in both of them:

public static void main(String... args) throws Exception {

    Calendar utcCalendar = Calendar
            .getInstance(TimeZone.getTimeZone("UTC"));

    utcCalendar.setTimeInMillis(1528371250000L);

    System.out.println("UTC Calendar: \t\t" + (utcCalendar.get(Calendar.MONTH) +1)
            + "/" + utcCalendar.get(Calendar.DAY_OF_MONTH) + "/"
            + utcCalendar.get(Calendar.YEAR) + " "
            + utcCalendar.get(Calendar.HOUR_OF_DAY) + ":"
            + utcCalendar.get(Calendar.MINUTE) + ":"
            + utcCalendar.get(Calendar.SECOND) + " " + utcCalendar.getTimeZone().getID());

    Calendar eastCalendar = Calendar.getInstance(TimeZone
            .getTimeZone("EST"));

    eastCalendar.setTimeInMillis(1528371250000L);

    System.out.println("Eastern Calendar: \t"
            + (eastCalendar.get(Calendar.MONTH)+1) + "/"
            + eastCalendar.get(Calendar.DAY_OF_MONTH) + "/"
            + eastCalendar.get(Calendar.YEAR) + " "
            + eastCalendar.get(Calendar.HOUR_OF_DAY) + ":"
            + eastCalendar.get(Calendar.MINUTE) + ":"
            + eastCalendar.get(Calendar.SECOND) + " " + eastCalendar.getTimeZone().getID());

    XMLGregorianCalendar xmlUtcCalendar = DatatypeFactory.newInstance()
            .newXMLGregorianCalendar((GregorianCalendar) utcCalendar);

    System.out.println("XML UTC Calendar: \t" + xmlUtcCalendar.toString());

    XMLGregorianCalendar xmlEastCalendar = DatatypeFactory.newInstance()
            .newXMLGregorianCalendar((GregorianCalendar) eastCalendar);

    System.out.println("XML Eastern Calendar: \t" + xmlEastCalendar.toString());

}

Output:

 UTC Calendar:          6/7/2018 11:34:10 UTC
 Eastern Calendar:      6/7/2018 6:34:10 EST
 XML UTC Calendar:      2018-06-07T11:34:10.000Z
 XML Eastern Calendar:  2018-06-07T06:34:10.000-05:00
Vadim
  • 4,027
  • 2
  • 10
  • 26
  • I understand that part that time in milliseconds is the instant since 1970. However, my problem is my code is not able to handle the time which is already in UTC converted to milliseconds and stored again in UTC. – sjain Jun 05 '18 at 13:54
  • 1
    Again, time in milliseconds cannot be converted to any time zone. See updated example in my answer. All 4 calendars have same time in millisecond values. – Vadim Jun 05 '18 at 17:46
1

tl;dr

You seem to be working much too hard.

You are using the wrong classes. Use modern java.time classes instead.

myPreparedStatement.setObject(
    … , 
    Instant.ofEpochMilli( 1_528_371_000_000L ) 
)

You are using the wrong data type in your database. The TIMEZONE WITHOUT TIME ZONE type cannot be used to store a specific moment.

Avoid setting default time zone

TimeZone.setDefault

This call immediately affects all code in all threads of all apps within the JVM! So make this call only in the most desperate of circumstances.

Instead, pass the desired/expected time zone as an optional ZoneId argument to many of the java.time methods.

The default time zone of your host OS and your JVM should both be irrelevant to your code. Specify explicitly your desired/expected time zone as a ZoneId (or ZoneOffset) object.

Avoid legacy date-time classes.

The old date-time classes bundled with the earliest versions of Java are terrible. This includes the GregorianCalendar class seen in your code. Instead, use their replacement, the modern java.time classes.

Specifically, GregorianCalendar is replaced by ZonedDateTime.

Instant

The Instant class in java.time is the basic building block. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction). 

Instant instant = Instant.now() ;  // Capture the current moment in UTC.

Apparently you have a count of milliseconds since the epoch reference of first moment of 1970 UTC, 1970-01-01T00:00:00Z. Simply parse that number as a Instant object by calling ofEpochMilli.

long input = 1_528_371_000_000L ;
Instant instant = Instant.ofEpochMilli( input ) ;

instant.toString(): 2018-06-07T11:30:00Z

Time zones

You may want to present the Instant to the user in a particular time zone. Apply a ZoneId to get a ZonedDateTime.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter pseudo-zones such as EST or IST as they are not true time zones, not standardized, and not even unique(!). 

ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;  // Or Europe/Paris, Africa/Tunis, etc.

Database

I do not know what you are doing with XMLGregorianCalendar. And I cannot address Hibernate as I am not a user, though I know Hibernate supports java.time types.

But if you trying to record and retrieve a moment with Postgres, you are working much too hard.

Storing a moment in UTC into a column of type TIMESTAMP WTH TIME ZONE (not WITHOUT! — see below).

String sql = "INSERT INTO tbl ( event ) VALUES ( ? ) ;" ;       // Writing a moment into a column of type `TIMESTAMP WTH TIME ZONE`.
…
Instant instant = Instant.ofEpochMilli( 1_528_371_000_000L ) ;  // Representing a moment in UTC.
myPreparedStatement.setObject( 1 , instant ) ;                  // As of JDBC 4.2 and later, we can directly exchange java.time objects with our database.

Retrieval.

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

Be aware that the SQL standard barely touches on the topic of date-time. Different databases behave very differently from one another in their handling of date-time.

The way Postgres works is that TIMESTAMP WITH TIME ZONE is something of a misnomer. The time zone is not stored as part of the timestamp. Any supplied time zone of offset-from-UTC information submitted with input is used to adjust into UTC. The UTC value is written to the database. The zone/offset is then discarded/forgotten. If remembering the original zone/offset of the input is important to you, you must store it separately in another column.

When retrieving a TIMESTAMP WITH TIME ZONE value from Postgres, you are always getting a value in UTC. This may not be apparent to you if your middleware tool connecting you (your app) with the database injects its own opinion as to a desirable time zone for presentation.

Avoiding this time zone injection confusion is easy with Java: Pass Instant objects, and retrieve Instant objects. An Instant is always in UTC. So calling ResultSet::getObject( … , Instant.class) will always represent faithfully the database’s TIMESTAMP WITH TIME ZONE value.

One tricky point is resolution of the fractional second. The java.time classes use nanoseconds resolution while Postgres uses microseconds. So you may want to truncate your Instant explicitly in your code to acknowledge this fact. Your JDBC driver will truncate for you, but I dislike such behind-the-scenes manipulation of data.

Instant instant = Instant.now().truncatedTo( ChronoUnit.MILLISECONDS ) ;  // Lop off any nanoseconds to match Postgres storing microseconds.

Wrong data type in your column

The type of startTime is timestamp without timezone in postgres.

The TIMESTAMP WITHOUT TIME ZONE is the wrong data type to store a moment. This type lacks any concept of time zone or offset-from-UTC. So it cannot represent a specific moment. It represents the set of potential moments along a range of about 26-27 hours (the range of all time zones).

Instead, you should be using the TIMESTAMP WITH TIME ZONE type to store a a moment, a specific point on the timeline.

So when we save then it again converts it in UTC.

Nope, not at all. Just the opposite. Any included time zone or offset-from-UTC information is ignored. The date and time-of-day are take as-is, with no accounting for time zone, no adjusting to UTC.

The SQL-standard type TIMEZONE WITHOUT TIME ZONE is equivalent to the java.time type LocalDateTime.

You should use these zone-less types only in these three situations:

  • The zone or offset is unknown.
    This is bad. This is faulty data. Analogous to having a price/cost without knowing the currency. You should be rejecting such data, not storing it.
  • The intention is “everywhere”, as in, every time zone.
    Example, a corporate policy that states “All our factories will break for lunch at 12:30" means the factory in Delhi will break hours before the factory in Düsseldorf which breaks hours before the factory in Detroit.
  • A specific moment in the future is intended, but we are afraid of politicians redefining the time zone.
    Governments change the rules of their time zones with surprising frequency and with surprisingly little warning or even no warning at all. So if you want to book an appointment at 3 PM on a certain date, and you really mean 3 PM regardless of any crazy decision a government might make in the interim, then store a LocalDateTime. To print a report or display a calendar, dynamically apply a time zone (ZoneId) to generate a specific moment (ZonedDateTime or Instant). This must be done on-the-fly rather than storing the value.

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?

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.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154