Date is not properly calculated in Java. Issue with zone

I have a date format stored in DB, for example:

Thu Aug 27 2020 00:00:00 GMT-0400 (Eastern Daylight Time)

I want to display the same date as output. Seems like I am missing something zone. It's evolving to be one day prior to this date.

I did the following:

DateTimeFormatter etFormat = DateTimeFormatter.ofPattern("MM/dd/yyyy 'at' hh:mma 'ET'");
ZoneId zoneId = ZoneId.of("America/New_York");
ZonedDateTime zonedDateTime = ((Timestamp) date).toLocalDateTime().atZone(zoneId);
etFormat.format(zonedDateTime)

Output:

08/26/2020 at 08:00PM ET

What am I doing wrong?

In your database you have the date time with offset UTC-04:40 (which is 4 hr behind from UTC assuming America/New_York timezone). And when it converts into Timestamp it will be stores in UTC without offset which is 08/26/2020 at 08:00PM.

So first convert the Timestamp into Instant of UTC and then convert the Instant into ZonedDateTime with the zone information

 ZonedDateTime dateTime = timestamp.toInstant()
                           .atZone(ZoneOffset.UTC)
                           .withZoneSameInstant(ZoneId.of("America/New_York"));
 etFormat.format(dateTime);     //08/27/2020 at 00:00PM ET

The central issue is this:

java.sql.Timestamp, which is what e.g. resultSet.getTimestamp() returns, does not contain any timezone data. It is simply an instant in time, and it is stored as milliseconds since the epoch (jan 1st, 1970), UTC zone.

This does not match what most DBs store, because most DBs do in fact explicitly store the timezone with that. If your DB does not do this, or you picked a column type which does not do this, you should strongly consider changing that.

So, if the database has stored 'midnight in new york, aug 27th', and the database is forced by JDBC to put this in java.sql.Timestamp terms, there's nothing the DB engine can do about it, other than do its best, which is to return that exact time, in UTC terms. If you then print the UTC timestamp in human terms, you end up with '4 at night', and not 'midnight' (because new york is 4 hours earlier than UTC).

You then, with your code say: Okay, take the timestamp, turn it into a local date time (that'd be the notion of '27th of august, 4 o clock at night', without any inkling of in which czone that is in, and by itself not a thing that can ever be turned back into an epoch with more info), and then you put this at the new york zone, giving you '4 at night in new york', which is 4 hours later than where we started.

Okay, but how do I fix this?

Every other answer (so far) is just giving you silly ways to fight the symptoms.

I propose you fix the disease.

The actual error occurs when you ask the DB to transfer the fully timezoned information from its tables into the timezoneless java.sql.Timestamp object. Stop doing that.

Don't call (I assume your column is called 'mark', fill in whatever it might be):

resultSet.getTimestamp("mark").

Call:

resultSet.getObject("mark", ZonedDateTime.class);

or possibly try LocalDateTime.class, or possibly OffsetDateTime.class, but ZDT is preferred.

Then if that does not work, complain to your DB and/or JDBC driver because they're messing up and making it next to impossible to do timezone stuff properly when interacting with that DB from the java side.

Actually, the DB should store just a moment-in-time

If truly the time being stored represents the notion of an 'instant in time' and not so much 'as humans would ever talk to you about it', then there are data types for that too, but convert your java.sql.Timestamp object to a java.time.Instant asap (via .toInstant()), or straight up ask for it: resultSet.getObject("colName", Instant.class) and have java and the db line up the datatypes straight away.

Eh, whatever. Cures are for wussies, just work around it

Eh, well, the only thing you really need to do then is not to magically add 4 hours. This will do it:

ZonedDateTime dateTime = timestamp.toInstant()
                           .atZone(ZoneOffset.UTC)
                           .withZoneSameInstant(ZoneId.of("America/New_York"));

even if the tz stored in the DB is something else (it'll then give you that instant in time, but in new york, e.g. if the db has stored 'midnight in amsterdam', this will give you a time 6 hours earlier (or possibly 7 or 5, there are a few days in the year where things go ape due to US and europe having different shift days for daylight savings).

The format that you have used is not correct. I hope you will be able to understand the difference by comparing your pattern with mine. The reason why I've presented the parsing logic is that you have not made it clear the type of date-time. Whatever type it may be, it looks like you have a date-time string, Thu Aug 27 2020 00:00:00 GMT-0400 (Eastern Daylight Time) which you want to parse into ZonedDateTime and display the same into the pattern of the date-time string you have. I guess, the main problem you are having is how to format the ZonedDateTime instance into the same form.

Do it as follows:

import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.format.TextStyle;

public class Main {
	public static void main(String[] args) {
		// Given date-time string
		String dateStr = "Thu Aug 27 2020 00:00:00 GMT-0400 (Eastern Daylight Time)";
		
		// Define the formatter for parsing
		DateTimeFormatter parsingFormat = new DateTimeFormatterBuilder()
											.appendPattern("EEE MMM dd uuuu HH:mm:ss zX")
											.appendLiteral(" (")
											.appendGenericZoneText(TextStyle.FULL)
											.appendLiteral(")")
											.toFormatter();

		// Parse the given date-time into ZonedDateTime
		ZonedDateTime zonedDateTime = ZonedDateTime.parse(dateStr, parsingFormat);

		// Display in default format [i.e. zonedDateTime.toString()]
		System.out.println(zonedDateTime);
		
		
		
		// Define the formatter for output
		DateTimeFormatter outputFormat = new DateTimeFormatterBuilder()
											.appendPattern("EEE MMM dd uuuu HH:mm:ss z")
											.appendLiteral(" (")
											.appendPattern("zzzz")
											.appendLiteral(")")
											.toFormatter();
		
		// Get the string representation in the custom format
		String strDate = zonedDateTime.format(outputFormat);

		// Display the string representation in the custom format
		System.out.println(strDate);
	}
}

Output:

2020-08-27T00:00-04:00[America/New_York]
Thu Aug 27 2020 00:00:00 GMT-04:00 (Eastern Daylight Time)

Note: By any chance, if you also have difficulty to convert the timestamp into ZonedDateTime, you can refer other answers on this page and use this answer to solve the problem with formatting.

The date is converted with timezone set to GMT.

final static String datePattern = "EEE MM/dd/yyyy HH:mm:ss 'GMT'Z '('z')'";
DateFormat df = new SimpleDateFormat(datePattern, Locale.getDefault());
simpledateformat.setTimeZone(TimeZone.getTimeZone("GMT"))

simpleDateFormat.format(givenDate)

java.time

I recommend that you use java.time, the modern Java date and time API, exclusively for your date work. Instead of getting a Date or Timestamp from your database, since JDBC 4.2 (in the case of MySQL that’s many years now) get a modern LocalDate from your result set. An example:

	PreparedStatement ps = yourDatabaseConnection.prepareStatement("select your_date from your_table;");
	ResultSet rs = ps.executeQuery();
	while (rs.next()) {
		LocalDate date = rs.getObject("your_date", LocalDate.class);
		// Do something with date
	}

A LocalDate is a date without time of day and without time zone. So this will relieve you of all time zone trouble.

If you want to print the start of the day in North American Eastern time zone to the user in the format used in the question:

	DateTimeFormatter etFormat = DateTimeFormatter.ofPattern("MM/dd/yyyy 'at' hh:mma v");
	ZoneId zoneId = ZoneId.of("America/New_York");
	
	LocalDate date = LocalDate.of(2020, Month.AUGUST, 27);
	ZonedDateTime startOfDay = date.atStartOfDay(zoneId);
	String result = startOfDay.format(etFormat);
	System.out.println(result);

Output from this example is:

> 08/27/2020 at 12:00AM ET

Do use pattern letter v for time zone in the format pattern rather than hard-coding ET. The latter will produce false and confusing results when one day a junior programmer feeds a ZonedDateTime in an other time zone into the code.

What went wrong in your code?

It’s not clear to me how you got your date from your database. Apparently date even though declared a Date was really a Timestamp (a bad practice since the inheritance relationship between the two classes is really one of implementation, not a conceptual one) denoting the start of the day in UTC. toLocalDateTime() is a dangerous and often meaningless call: it uses the time zone of the JVM for converting the Timestamp to a LocalDateTime. At 0:00 UTC it is 8 PM the evening before in Eastern time zone, so your LocalDateTime becomes 2020-08-26T20:00. Next atZone(zoneId) only gives the correct time because zoneId happens to coincide with the JVM’s time zone used in the previous step.

Link

• Oracle tutorial: Date Time explaining how to use java.time.