Store date in AEST timezone in database-CodePudding

We have two tables in oracle db as below,

TableA => Column DateA (type DATE)

TableB => Column DateB (type DATE)

DateA column in TableA has dates stored in UTC. DateB column in TableB has dates stored in AEST.

When I checked the code (its in java) where the dates are being stored in these tables, both are just instantiating Date type like below.

Date now = new Date();

How are they having dates in different timezones when they are using same code ? I am wondering if there might be any other config elsewhere ?

Suggestions please. Ultimately, I need to update code so all dates are stored in AEST.

CodePudding user response：

DateA column in TableA has dates stored in UTC. DateB column in TableB has dates stored in AEST.

Nope, neither column is being stored in UTC or AEST. The DATE type in Oracle database sores only a date with time of day. That data type purposely lacks any concept of time zone or offset-from-UTC. Whatever date and time you put in that column is saved, but without the zone/offset we cannot determine a moment (a point on the timeline).

Date now = new Date();

Never use either Date class in Java. These terrible classes were designed by people who did not understand date-time handling. These classes are now legacy, supplanted years ago by the modern java.time classes defined in JSR 310.

Ultimately, I need to update code so all dates are stored in AEST.

Since you have lost the context of zone/offset in your existing stored data, I cannot see any way to clean up that mess.

But, FYI, I can show the correct way to store such data.

First, define your database column as a data type akin to the SQL-standard type of TIMESTAMP WITH TIME ZONE (versus WITHOUT). By my reading of this doc, for Oracle Database 21 that would be he same name, TIMESTAMP WITH TIME ZONE.

In Java, specify the desired time zone.

AEST is not a time zone. It is an indicator of whether Daylight Saving Time (DST) is engaged. Never use such values in your busines logic and stored data. Use only real time zone names, in new format of Continent/Region.

ZoneId z = ZoneId.of( "Australia/Sydney" ) ;

Capture the current moment as seen with an offset from UTC of zero hours-minutes-seconds.

Instant instant = Instant.now() ;

Adjust to your time zone.

ZonedDateTime zdt = instant.atZone( z ) ;

The SQL standard does not handle time zones unfortunately. So we must convert to offset instead of zone, for exchanging values with a database.

OffsetDateTime odt = zdt.toOffsetDateTime() ;

Pass via JDBC 4.2 or later.

myPreparedStatement.setObject( … , odt ) ;

Retrieval.

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

Adjust to your time zone.

ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;