CODEWITHSUNDEEP
javasql-server
David Tunnell
David Tunnell

Reputation: 7532

Inserting the exact time into a database

When you insert the following into a SQL database:

Date now = new Date(System.currentTimeMillis());
PreparedStatement audit = sqlConn.prepareStatement("INSERT INTO [Table](Date) VALUES (?)");
audit.setDate(1, now);
audit.execute();

This is the result:

2013-09-06 00:00:00.000

How do I also get the time information inserted into the database?

Edit: The column datatype is DateTime.

Upvotes: 1

Views: 1552

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 338181

You may be confusing:

  • The java.util.Date class which represents a date and a time-of-day in UTC
  • The java.sql.Date class which represents a date-only, without time-of-day and without time zone.

Actually, java.sql.Date extends java.util.Date so it does in fact inherit a time-of-day but pretends to not have a time-of-day by setting it to 00:00:00.0 in UTC. The class doc tells you ignore the inheritance relationship (it's a hack). Confusing? Yes, these old date-time classes are a mess, poorly designed, confusing, and troublesome.

These old classes were supplanted by the java.time classes built into Java 8 and later. Much of the functionality has been back-ported to Java 6 & 7 in ThreeTen-Backport and further adapted to Android in ThreeTenABP.

If your database column is of a type similar to the SQL standard type TIMESTAMP WITH TIME ZONE, then you can use either:

  • Instant
    A moment on the timeline in UTC with a resolution up to nanoseconds.
    Instant instant = Instant.now();
  • ZonedDateTime
    Like an Instant but adjusted into a particular time zone.
    ZonedDateTime zdt = ZonedDateTime.now( ZoneId.of( "America/Montreal" ) );

If your JDBC driver complies with JDBC 4.2 spec, you can pass either of these via the setObject method on a PreparedStatement.

If your driver does not support the direct use of java.time types, fall back to using java.sql types. Find new methods added to the old classes for converting to/from java.time types. You are looking for the java.sql.Timestamp class in particular for a date-time value, which can convert to/from Instant. From a ZonedDateTime you can extract an Instant for this purpose of conversion.

java.sql.Timestamp ts = java.sql.Timestamp.from( instant );
java.sql.Timestamp ts = java.sql.Timestamp.from( zdt.toInstant() );

Going the other direction.

Instant instant = mySqlTimestamp.toInstant();

Upvotes: 1

Sotirios Delimanolis
Sotirios Delimanolis

Reputation: 279880

Some database only store up to the second precision for the date.

SQLserver's datetime supports fractional seconds, but it rounds to the nearest 3 milliseconds.

If you need more precision, store your date as a bigint type and pass the now.getTime() Java long value instead.

Upvotes: 1

Related Questions