Java Efficient Conversion of LocalDateTime to byte[] [closed]

A lot depends on your DB engine. That's generally the case when asking 'I need to operation X a few million times twice a day with a DB engine'. SQL is a standard for a syntax, not for a performance profile.

For the rest of this answer I shall assume postgreSQL.

PostgreSQL adheres to the SQL standard on this and treats the type timestamp as short for timestamp without time zone, which, indeed, matches java class java.time.LocalDateTime the best.

However, a ton of conversion has to happen even if you just invoke rs.getObject(1, java.time.LocalDateTime.class) on your JDBC ResultSet. Yes, the JDBC 4.2 spec will guarantee this works, and, yes, this causes guaranteed lossless conversion. However, Java's LDT type has a boatload of fields (one for year, one for month, and so on), whereas psql bitpacks the data into an 8-byte sequence. Hence, if you so much as ask JDBC to give you a LocalDateTime object, you've already pretty much lost the game then and there - that's doing a boatload of work that isn't required. In fact, it's actively painful if the goal is to produce a hash.

So, don't, if you can. Let the DB to the work:

SELECT EXTRACT(epoch FROM TIMESTAMP '1999-01-08 04:05:06')

You can then get that via rs.getLong(1) via JDBC.

This gets you 915768306. Which is the amount of seconds that have passed since the epoch (midnight, jan 1st, 1970), for the UTC timezone. If you find the millisecond value relevant, you'd have to select 1000 * EXTRACT instead:

try (var stmt = con.createStatement()) {
  try (var rs = stmt.executeQuery("SELECT 1000 * EXTRACT(epoch FROM TIMESTAMP '1999-01-08 04:05:06')")) {
    rs.next();
    long a = rs.getLong(1);
    long b = LocalDateTime.of(1999, 1, 8, 4, 5, 6).toInstant(ZoneOffset.UTC).toEpochMilli();
    assertEquals(a, b); // this will hold.
  }
}

Is that faster? Probably. Certainly converting an LDT to a string and hashing that is making it worse. just call .hashCode() on your LDT if you must.