SQL database (JDBC): A column of type date only stores date values and not time values

Question

I have a database like this where lastEdited is a date field.

I connected this database to a Java web application by using "RESTFUL web-services from database"

This automatically generated a class called ThreadChat for my THREADCHAT column. Given here is the getter and setter for the lastEdited part.

lastEdited is a java.util.Date object.

public Date getLastedited() {
    return lastedited;
}

public void setLastedited(Date lastedited) {
    this.lastedited = lastedited;
}

Here, I am creating a new ThreadChat object and adding the data to the database. Argument for lastEdited is a java.util.Date object.

    ThreadChat thread = new ThreadChat(threadName, new Date(), loginIdInt);
    threadChatFacadeREST.create(thread);

This would update the table like this.

This method returns all the records in the THREADCHAT table.

List<ThreadChat> list = getAllThreads();

However, getLastEdited() returns a XMLGregorianCalender object instead of a Date object as in the getter method above.

If I print this object to the console, the date part is there but I get 00:00 for the minutes and hours part every time.

2018-04-10T00:00:00+05:30

How do I store Date + Time in a JDBC database and retrieve both?

So, a really quick search of [SQL data types](https://www.google.com.au/search?client=safari&rls=en&q=sql+datatypes&ie=UTF-8&oe=UTF-8&gfe_rd=cr&dcr=0&ei=6pPMWqq8Ls3N8gekgaqwCw) provides a number of really interesting results. Just of the first hit - *"DATE() A date. Format: YYYY-MM-DD"*, well that explains a bit, but perhaps *"DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MI:SS"* would be more useful — MadProgrammer, Apr 10 '18 at 10:39
You don't seem to have asked a question here. What's the actual problem? Oh, and a DATE is just a date. A DATETIME is a date and a time. That's how it's _supposed_ to be. — Dawood ibn Kareem, Apr 10 '18 at 10:40
@DawoodibnKareem I want to store and retrieve the time along with the date. — Enzio, Apr 10 '18 at 10:40
Possible duplicate [java sql date time](https://stackoverflow.com/questions/8530545/java-sql-date-time) — MadProgrammer, Apr 10 '18 at 10:41
@MadProgrammer I didn't use java.sql.date. Used java.util.date which stores both date and time. — Enzio, Apr 10 '18 at 10:43
@Enzio Which is irrelevant - because it's about how the database stores the value, not how Java represents it. SQL date type ONLY stores date information, it doesn't store time information, so when you load it into Java, bang, time is zeroed out - use the correct data type in the database — MadProgrammer, Apr 10 '18 at 10:44
@Enzio Java's new Date/Time API has similar concept (`LocalDate`, `LocalTime`, `LocalDateTime`) which provides structures to mange the elements you're most interested in, SQL just did it first, along time ago — MadProgrammer, Apr 10 '18 at 10:46
Most of the information here are not needed. Basically you have table with a `DATE` column type, you get that value in a POJO and you only get a `Date` with the time unit. I don't see what is the point with most of the information above... — AxelH, Apr 10 '18 at 10:56

Paplusc · Answer 1 · 2018-04-11T07:13:11.833

2

The type of your field in your database is DATE so DATE only stores YYYY-MM-DD.

If you want to store also the time you must change the type of your field in your database to DATETIME. The representation of this is: YYYY-MM-DD HH:MI:SS

More information in: https://www.w3schools.com/sql/sql_datatypes.asp

edited Apr 11 '18 at 07:13

answered Apr 10 '18 at 11:29

Paplusc

1,080
1
12
24

1

I ended up using timestamp. I selected your answer because of the explanation and provided sources. – Enzio Apr 10 '18 at 11:56
You should clarify your answer as to “DATETIME. The format of this is: YYYY-MM-DD HH:MI:SS”. Databases do not have a “format“ in their stored date-time values, only textual representations such as input/output has a format. – Basil Bourque Apr 10 '18 at 19:59
When i said format i would say representation, sorry but my english isn't very good. – Paplusc Apr 11 '18 at 07:12

score 1 · Answer 2 · answered Apr 10 '18 at 11:00

1

You are looking for a DATA_TYPE TIMESTAMP. You should change your Database if you can.

answered Apr 10 '18 at 11:00

Alvaro Castro Mendez

11
3

Basil Bourque · Accepted Answer · 2018-04-10T20:25:35.027

tl;dr

You are using a date-only type to hold a date-time value – trying to fit a square peg into a round hole.

How do I store Date + Time in a JDBC database and retrieve both?

To store a moment, a date-time value, use a date-time type:

TIMESTAMP WITH TIME ZONE in your database.
Instant in Java.

You are using terribly confusing legacy classes. Use only java.time classes instead.

Databases vary

Databases vary widely in their date-time data types and their behavior. You do not specify your database, so we can only guess or abide by the SQL standard.

The SQL standard barely touches on the subject of date-time handling, unfortunately. The standard briefly defines:

DATE as a date-only value without time-of-day.
A TIMESTAMP WITHOUT TIME ZONE is a date and a time-of-day, but lacks any concept of time zone or offset-from-UTC, so it does not represent actual moments.
For actual moments, use TIMESTAMP WITH TIME ZONE. That type name can be misleading as some implementations such as Postgres do not store a zone with the value, but instead use any passed zone information to adjust into a UTC value for storage.

java.time

You are using terrible old date-time classes from the earliest versions of Java. Those were supplanted in Java 8 and later by the java.time classes. Never use Date or Calendar again.

Generally best to think, work, store, log, and exchange date-time values in UTC unless specifically required by the business logic or user-interface. So generally you should focus on the Instant class. 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). This class replaces java.util.Date.

Capture the current moment in UTC.

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

Store this Instant in a database column of SQL-standard type TIMESTAMP WITH TIME ZONE or whatever is akin to that in your particular database.

JDBC 4.2

As of JDBC 4.2 and later, you can directly exchange java.time objects with your database.

myPreparedStatement.setObject( … , instant ) ;

Retrieval.

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

Converting

If handed an obsolete XMLGregorianCalendar, immediately convert into java.time ZonedDateTime by way of the obsolete GregorianCalendar class.

ZonedDateTime zdt = myXmlGregCal.toGregorianCalendar().toZonedDateTime() ;  // Convert from legacy classes to modern java.time class.

If you want to see that same moment in UTC rather than the wall-clock time used by the people of that particular region (time zone), extract an Instant.

Instant instant = zdt.toInstant() ;  // Adjust from zoned moment to UTC. Same moment, same point on the timeline, different wall-clock time.

If you must inter-operate with old code not yet updated to java.time, you can convert back and forth. Tip: Try to stay within java.time as much as possible. The legacy classes are an awful mess of poor design.

java.util.Date myJavaUtilDate = java.util.Date.from( instant ) ;

And, going the other direction.

Instant instant = myJavaUtilDate.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.