Java:timestamp is wrong

Question

I got a error timestamp

try {
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    LocalDateTime localDateTime =  LocalDateTime.parse("1991-04-14 10:30:00", formatter);
    ZoneId zoneId = ZoneId.systemDefault();
    ZonedDateTime zdt = localDateTime.atZone(zoneId);
    Instant instant = zdt.toInstant();
    System.out.println(instant.toEpochMilli());
} catch (DateTimeParseException e) {
    e.printStackTrace();
}

print => 671592600000

But MYSQL got a different value

SELECT FROM_UNIXTIME(671592600000/1000);

result is 1991-04-14 09:30:00.0000
time-zone = +8.00

Answer 1

You are mixing time zones.

Presumably the “UNIXTIME” in your line of SQL code refers to Unix Time which is tracked as a count of seconds or milliseconds (or some such granularity) since the epoch reference of first moment of 1970 in UTC. ⬅ That UTC part is crucial.

Your Java code took the LocalDateTime object, and placed it into the context of some (unknown to us here) time zone. So of course the results vary.

You should have placed that input string into the context of UTC (an offset-from-UTC of zero).

LocalDateTime               // Represent a date with a time-of-day. But lacking any concept of time zone or offset-from-UTC, this does *NOT* represent a moment, is *NOT* a point on the timeline.
.parse( 
    "1991-04-14 10:30:00"
    .replace( " " , "T" )   // Replace SPACE in middle with `T` to comply fully with ISO 8601 standard format.
)                           // Returns a `LocalDateTime` object.
.atOffset(                  // Give meaning to the `LocalDateTime` by determining a moment with the context of an offset-from-UTC.
    ZoneOffset.UTC          // A constant for UTC (an offset of zero).
)                           // Returns an `OffsetDateTime` object.
.toInstant()                // Convert from the more flexible `OffsetDateTime` to the basic building-block class of `Instant`. An `Instant` is *always* in UTC, by definition.
.toEpochMilli()             // Returns a `long`, a count of milliseconds since 1970-01-01T00:00:00Z. Beware possible data-loss as any microseconds or nanoseconds will be ignored when counting in milliseconds.

You need to be really clear on the idea that a LocalDateTime does not represent a moment, is not a point on the timeline. When you said 10:30 AM on the 14th of April, that could be the morning in Tokyo, Paris, or Montréal, all different moments. Without the context of zone or offset, a LocalDateTime has no real meaning. See: What's the difference between Instant and LocalDateTime?