Data type recommendes to save datetime in oracle and java

Question

I have add a column (name updatedAt) in Oracle database table to save updated datetime.

1. What data type should I choose - Date or Timestamp in oracle?

2. To create a model/domain class for this table in Java, shall I use java.sql.Timestamp or java.time.Instant for this column. Or is there any other better and recommended data type to use in Java? I am using java 17.

Please suggest data type for both java and oracle to save date and time.

Answer 1

What data type should I choose - Date or Timestamp in oracle?

Whichever you want and/or best represents your data.

In Oracle:

• A DATE is a binary data-type consisting of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has all those components.
• A TIMESTAMP is a binary data-type consisting of 7-13 bytes representing: century, year-of-century, month, day, hour, minute, second and 0-6 bytes for fraction seconds depending on the precision of the timestamp.
• A TIMESTAMP WITH TIME ZONE is a binary data-type consisting of 14-20 bytes representing: century, year-of-century, month, day, hour, minute, second, 7 bytes for time-zone information and 0-6 bytes for fraction seconds depending on the precision of the timestamp.

Note: None of those data-types store data in any particular human-readable format. The format in which DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE data types are displayed is controlled by the client application which reads from the database.

• If you want year-to-integer-second precision then use either DATE or TIMESTAMP(0) data-types (which you use is personal preference - although there is a slight difference in behaviour if you subtract DATEs you get a NUMBER data-type representing the difference in days, or fraction of days, and if you subtract TIMESTAMPs you get an INTERVAL DAY TO SECOND data-type).
• If you want year-to-fractional-second precision then use the TIMESTAMP(N) data-type (where N is the desired number of digits precision for the fractional seconds).
• If you want to include a time zone then use the TIMESTAMP(N) WITH TIME ZONE data-type (where N is the desired number of digits precision for the fractional seconds).

Answer 2

Record a moment in Oracle database as TIMESTAMP WITH TIME ZONE

To record a moment, a specific point on the timeline, always use a data type akin to the SQL standard type TIMESTAMP WITH TIME ZONE. In Oracle database, the appropriate type has the same name, TIMESTAMP WITH TIME ZONE. (Other databases may use other names for such a type.)

Be aware that Oracle database actually records the specified time zone information submitted to the databases. Some other databases behave differently. Some databases such as Postgres instead use the specified time zone or offset to adjust each submitted value to an offset from UTC of zero hours-minutes-seconds.

I suggest you employ that same policy when submitting any moment that does not need to remember the original time zone. For example, when recording in Java when an object was created (or last modified), use java.time.Instant which always represents a moment as seen in UTC (an offset of zero).

// Record moment when object was created. 
this.created = Instant.now() ;

JDBC requires the use of OffsetDateTime type rather than Instant. Use the constant ZoneOffset.UTC to make an Instant into an OffsetDateTime.

myPreparedStatement.setObject( … , this.created.atOffset( ZoneOffset.UTC ) ) ;

You said:

What data type should I choose - Date or Timestamp in oracle?

DATE type in Oracle Database stores the following information: century, year, month, date, hour, minute, and second. Note that time zone or offset is not included. Without the context of zone/offset, values of this type are inherently ambiguous. We do not know if a value of noon on January 23, 2024 was meant to be noon in Tokyo, noon in Toulouse, or noon in Toledo Ohio — three very different moments several hours apart.

The TIMESTAMP type in Oracle Database extends the DATE type to add a fraction of a second. But this type too lacks the context of a zone or offset. So values of this type are also ambiguous.

So neither DATE nor TIMESTAMP in Oracle Database should be used for recording a moment.

In Oracle Database, record a moment in a column of type TIMESTAMP WITH TIME ZONE.

Use only java.time classes

The terribly flawed legacy date-time classes were years ago supplanted by the modern java.time classes defined in JSR 310.

OffsetDateTime for exchanging a moment via JDBC

The java.sql.Timestamp class was replaced by OffsetDateTime for purposes involving a database via JDBC.

shall I use java.sql.Timestamp or java.time.Instant for this column

Never use java.sql.Timestamp class.

Instant for representing a moment in UTC

Use java.time.Instant in your domain model.

To save and retrieve that value in the database, JDBC requires the use of java.sql.OffsetDateTime.

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

You could use OffsetDateTime in your domain model. But that gives the impression that the offset might vary across instances. If you intend to use only offset values of zero hours-minutes-seconds, then Instant makes clear your intention.