java sql date time

Question

When I insert a SQL DateTime to the database I get 2007-02-07 12:00:00.00

But I made the Date object like this : 2007-02-07 17:29:46.00

How to get the value of the seconds in the database. It always changes it back to 12:00:00.00

date.setYear(Integer.valueOf(parsedDate[2].replaceAll(" ", "")) - 1900);
date.setMonth(Integer.valueOf(parsedDate[0].replaceAll(" ", "")));
date.setDate(Integer.valueOf(parsedDate[1].replaceAll(" ", "")));
...
java.sql.Date sqlDate = new java.sql.Date(date.getTime());

Should I use any formatters?

Answer 1

java.sql.Date represents a date, not a date and time. From the docs:

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

If you want to store a date and time, you should look for another type - e.g. java.sql.Timestamp. EDIT: That's not suggesting you use a TIMESTAMP column type - as paulsm4 says in the comments, that's a different thing. However, as far as I can see, JDBC only supports:

• Date (no, you want a time too)
• Time (no, you want a date too)
• Timestamp (includes a date and time, but you don't want TIMESTAMP SQL semantics)

I would expect using the Java Timestamp type with a DATETIME column to work, although without the level of precision that Timestamp provides.

EDIT: After a bit more research, it looks like you may want to use the java.sql.Time type, but with special driver parameters - at least if you're using the Microsoft driver. See these docs on configuring JDBC for more information.

Answer 2

tl;dr

You are likely confused by not understanding that java.util.Date is a date-with-time type while its subclass java.sql.Date pretends to be a date-only class but actually has its time-of-day set to zero. Bloody awful design. Avoid both these classes entirely. Use java.time classes only.

For a date-only column in your database, define the column as the SQL-standard DATE type.

myPreparedStatement.setObject(
    … ,
    LocalDateTime.parse( "2007-02-07 17:29:46.00".replace( " " , "T" ) )
    .toLocalDate()
)

java.time

The modern approach uses the java.time classes added to Java 8 and later.

When I insert a SQL DateTime to the database I get 2007-02-07 12:00:00.00

There is no such thing as a SQL-standard type as DateTime, nor any such class in Java. So I do not know your intention there.

As for the input string, 2007-02-07 17:29:46.00, parse that as a LocalDateTime because it lacks any indicator of time zone or offset-from-UTC.

That SQL-style format almost complies with the ISO 8601 standard. To fully comply, replace the SPACE in the middle with a T. The java.time classes use the ISO 8601 formats by default when parsing/generating strings.

String input = "2007-02-07 17:29:46.00".replace( " " , "T" ) ;

Parse.

LocalDateTime ldt = LocalDateTime.parse( input ) ;

A LocalDateTime does not represent a moment, is not a point on the timeline. It represents potential moments along a range of about 26-27 hours.

Standard SQL does offer a data type for such a value, TIMESTAMP WITHOUT TIME ZONE.

Smart objects, not dumb strings

Your entire approach is misguided, wrangling text and using the legacy date-time classes. Instead, exchange java.time objects.

As of JDBC 4.2, you need not ever use the troublesome old java.sql types such as java.sql.Date or java.sql.Timestamp. You can directly exchange java.time objects with your database via setObject/getObject methods.

myPreparedStatement.setObject( … , ldt ) ;

And retrieval.

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

If you are trying to work with date-only values, use the SQL-standard type DATE and the Java class LocalDate.

LocalDate ld = ldt.toLocalDate() ;
myPreparedStatement.setObject( … , ld ) ;

How to get the value of the seconds in the database

Not sure what you mean by "value of the seconds".

Perhaps you want a count of seconds from the epoch reference of first moment of 1970 in UTC.

long secondsSinceEpoch = ldt.toEpochSecond() ;

If your goal was merely to instantiate a java.sql.Date, don’t bother. Never use that class again. But, FYI, your specific issue is likely a side-effect of the awful design used for that class. The java.sql.Date class inherits from java.util.Date which is a date-with-time type. The java.sql.Date class pretends to be a date-only value, but actually has its time-of-day set to 00:00:00. Even worse, the documentation tells us to ignore the fact of its being a subclass. Don’t bother trying to understand it; just use java.time instead.

If you are trying to work with the time-of-day alone, extract a LocalTime object.

LocalTime lt = ldt.toLocalTime() ;

If you want to set the time-of-day to zeros, then you likely want a date-only value. If so, use the LocalDate class for a date value without a time-of-day and without a time zone.

LocalDate ld = ldt.toLocalDate() :

If you do want the first moment of the day on that date, call LocalDate::atStartOfDay.

LocalDateTime ldtStart = ldt.toLocalDate().atStartOfDay() ;

BEWARE: If you are trying to track actual moments, specific points on the timeline, then all this code above is wrong. Search Stack Overflow to learn about Instant, ZoneId, and ZonedDateTime classes. Search both Stack Overflow and dba.StackExchange.com to learn about the SQL-standard type TIMESTAMP WITH TIME ZONE.

---

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.