I have a simple spring boot REST API application, using plain jdbc to fetch data from a MSSQL DB. I am trying to figure out how best to retrieve a DATETIME2 column from the DB (which stores no timezone info), and serialize it as a UTC timestamp (and treat it as such in general in code).

My DB server timezone is set to UTC. I know that everything stored to this column is stored as UTC and I cannot change the column type unfortunately. It's a bit of a legacy DB, so all the dates will need to fetch will have this same problem, hence looking for a clean neat and tidy solution.

Ideally in my Java app, I would ideally like all my "date" fields to be of type java.time.Instant, since it is easy to handle and will serialize to json looking something like "someDate": "2022-05-30T15:04:06.559896Z".

The options as I see them are:

1. Use a custom RowMapper to do something like myModel.setDate(rs.getTimestamp("Date").toLocalDateTime().toInstant(ZoneOffset.UTC));, but this just seems verbose. I suppose I could tuck it away in some utility class static function?
2. Use LocalDateTime everywhere and do myModel.setDate(rs.getTimestamp("Date").toLocalDateTime()). But then Jackson will serialize it without timezone information.
3. Set the whole app timezone to UTC on startup. But this could be changed by other code, and from what I read is generally a bad idea.

CodePudding user response：

Caveat: I am not a user of Spring.

moment versus not-a-moment

You need to get clear on one fundamental issue with date-time handling: moment versus not-a-moment.

• By “moment” I mean a specific point on the timeline. Without even thinking about time zones and such, we all know that time flows forward, one moment at a time. Each moment is simultaneous for everyone around the world (sticking with Newtonian time, ignoring Einstein Relativity here). To track a moment in Java, use Instant, OffsetDateTime, or ZonedDateTime. These are three different ways to represent a specific point on the timeline.
• By “not-a-moment” I mean a date with a time-of-day, but lacking the context of a time zone or offset-from-UTC. If I were to say to you, “Call me at noon tomorrow” without the context of a time zone, you would have no way of knowing if you should call at noon time in Tokyo Japan, noon time in Toulouse France, or noon time in Toledo Ohio US — three very different moments, several hours apart. For not-a-moment, use LocalDateTime.

So never mix LocalDateTime with the other three classes, Instant, OffsetDateTime, or ZonedDateTime. You would be mixing your apples with your oranges.

You said:

I would ideally like all my "date" fields to be of type java.time.Instant

Yes, I would agree on generally using Instant as the member field on any Java object tracking a moment. This is generally a good idea — but only for moments. For not-a-moment, as discussed above, you should use LocalDateTime instead.

TIMESTAMP WITH TIME ZONE

Another issue, Instant was not mapped in JDBC 4.2 and later. Some JDBC drivers may optionally handle an Instant object, but doing so is not required.

So convert your Instant to a OffsetDateTime. The OffsetDateTime class is mapped in JDBC to a database column of a type akin to the SQL-standard type TIMESTAMP WITH TIME ZONE.

OffsetDateTime odt = instant.atOffset( Offset.UTC ) ;

Writing to database.

myPreparedStatement.setObject( … , odt ) ;  // Pass your `OffsetDateTime` object. 

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

TIMESTAMP WITHOUT TIME ZONE

For database columns of a type akin to the SQL-standard type TIMESTAMP WITHOUT TIME ZONE, use LocalDateTime class.

Writing to database.

myPreparedStatement.setObject( … , ldt ) ; // Pass your `LocalDateTime` object. 

Retrieval.

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

Specify time zone

You said:

My DB server timezone is set to UTC.

That should be irrelevant. Always write your Java code in such as way as to not rely on the JVM’s current default time zone, the host OS’ current default time zone, or the database’s current default time zone. All of those lay outside your control as a programmer.

Specify your desired/expected time zone explicitly.

Retrieve a moment from the database, and adjust into a desired time zone.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
ZoneId z = ZoneId.of( "Africa/Tunis" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

Generate text localized to the user's preferred locale.

Locale locale = Locale.JAPAN ; 
DateTimeFormatter f = DateTimeFormatter.ofLocalizedDateTime( FormatStyle.LONG ).withLocale( locale ) ;
String output = zdt.format( f ) ;

DATETIME2 in MS SQL Server

The type DATETIME2 type in MS SQL Server stores a date with time-of-day, but lacks the context of a time zone or offset-from-UTC.

That is exactly the wrong type to use for storing a moment. As discussed above, that type is akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONE, and maps to the Java class LocalDateTime.

You seem to understand that fact given your comment:

I know that everything stored to this column is stored as UTC and I cannot change the column type unfortunately. It's a bit of a legacy DB …

Let me point out that you do not know the values in that column are intended to represent a moment as seen with an offset of zero. You can expect that, and hope so. But without using the protection of the database’s type system, you cannot be certain. Every user, every DBA, and every SysAdmin must have always been aware of this unfortunate scenario, and must have always done the right thing. You’ll need lots of luck with that.

I must mention that the ideal solution is to refactor your database, to correct this wrong choice of data type for that column. But I understand this could be a burdensome and challenging fix.

So given this unfortunate scenario without a fix being feasible, what to do?

Options 1, 2, & 3 you listed

Option 1

Regarding your option # 1, yes that makes sense to me. Except two things:

• I would change the name of your model method to be more precise: setInstant. Or use a descriptive business name such as setInstantWhenContractGoesIntoEffect.
• Never use the awful legacy date-time classes in Java such as Timestamp. Change this:

myModel.setDate(rs.getTimestamp("Date").toLocalDateTime().toInstant(ZoneOffset.UTC));

… to:

myModel
    .setInstantWhenContractGoesIntoEffect
    ( 
        resultSet
        .getObject( "Date" , LocalDateTime.class )  // Returns a `LocalDateTime` object. 
        .toInstant( ZoneOffset.UTC )                // Returns an `Instant` object.
    )
;

Option 2

As for your option # 2, I am not quite sure what you have in mind. But my impression is that would be the wrong way to go. I believe the best approach, for long-term maintenance without "technical debt", and for avoiding confusing and mishaps, is to “tell the truth”. Do not pretend to have a zebra when you actually have donkey. So:

• On the database side, be clear and explicit that you have a date with time but lack the context of an offset. Add lots of documentation to explain that this is based on a faulty design, and that we are intend to store moments as seen in UTC.
• On the app side, the Java side, deal only with Instant, OffsetDateTime, and ZonedDateTime objects, because within the data model we are representing moments. So use classes that represent a moment. So no use of LocalDateTime where you really mean a specific point on the timeline.

Obviously, there is some kind of a dividing line between your database side and your app side. Crossing that line is where you must convert between your Java type for a moment and your database type faking it as a moment. Where you draw that line is up to you.

Option 3

As for your option # 3, yes that would be a very bad idea.

Setting such a default is not reliable. Any SysAdmin, or even an unfortunate OS update, could change the OS’s current default time zone. Like wise for the database’s current default time zone. And likewise for the JVM’s current default time zone.

So you end up three default time zones that could be changing, with each affecting various parts of your environment. And changing the current default time zone in any of those places immediately affects all other software depending on that default, not just your particular app.

As mentioned above, I recommend just the opposite: Code without any reliance on default time zones anywhere.

The one place for accessing a default time zone is maybe for presentation to the user. But even then, if the context in crucial, you must confirm the desired/expected time zone with the user. And where you do make use of a current default time zone, do so explicitly rather than implicitly. That is, make explicit calls such as ZoneId.getSystemDefault() rather than using omitted optional arguments.

CodePudding user response：

I'm not sure I see a problem.

Instant values are UTC by definition, and java.sql.Timestamps have no zone other than the one implied by the database setting. You know the database is strictly UTC. This is lucky for you since it eliminates one error-prone conversion. Then, reading java.sql.Timestamps and keeping them as Instants at runtime is trivial, given java.sql.Timestamp#toInstant(). DON'T convert through LocalDateTime.

This has nothing to do with setting any "default" timezone in your application. Design and write your code so that internally (i.e. runtime memory and database) you deal ONLY with UTC (i.e. instants). The only point at which you should convert instants to anything local is at external interface points... i.e.

• when outputting date/time values, either for human consumption or for other software that expects a specific timezone.
• when reading date/time values from the user or another program (for which you will need to know any implied zone if it's not explicit)

Leave your "default" timezone as whatever is given to you by your environment. Then, no matter where your code is running, it will produce meaningful local dates/times.

Establish a strict rule that you deal only with UTC internally. This will make reasoning about your code MUCH simpler in the long run.

I guess the only real stumbling block is realizing that things depending on local conditions, such as day boundaries, have to be done in the local zone... but write your code to "think" UTC internally.