Transfer date from form to database
2021-01-05 | java | js | frontend | backend | postgresql

There are Angular, PrimeNG, Spring Boot, JDBC, PostgreSQL

I need to save date and time from form to database and show it again.

Prepare DB

create database test_date;

CREATE TABLE test_table (
	test_date date NULL,
	test_timestamp timestamp NULL,
	test_timestamptz timestamptz NULL,
	id serial2,
	CONSTRAINT test_table_pk PRIMARY KEY (id)
);

java.util.Date

Solution 1

I need just date without a time. I will use the column with type date. Choose 2020-12-22 on the form. 2020-12-21T21:00:00.000Z is sent. It is a begining of day in UTC, because browser is in +3. Java makes statement 

statement.setObject(1, entity.getTestDate(), Types.DATE)

insert into test_table (test_date) values ('2020-12-22 +03')

JDBC cuts the time out and adds time zone (by default system zone or -Duser.timezone=Europe/Moscow). Postgres doesn't look at zone for column date. 2020-12-22 is saved. Same is read from DB. Result Json is 

{ "testDate": "2020-12-22" }

Browser interprets this format as a day start in UTC.

new Date('2020-12-22')
new Date('2020-12-22T00:00:00.000+00:00')
Tue Dec 22 2020 03:00:00 GMT+0300 (Moscow Standard Time)

I see on form 2020-12-22 03:00 or just 2020-12-22 without a time. It is right.

I have faced a situation where Chrome and Firefox interpreted a date without a time differently. Someone did it as the beginning of the day in local time. This is not currently reproduced on updated versions. The documentation says that this format is now standardized. But if the string differs from the format 2020-12-22T00:00:00.000+00:00, then the behavior is not guaranteed.

Error

Error rises if there is user that is east of sever's time zone. For example Europe/Samara (+4). He choose 2020-12-22. Browser sends 2020-12-21T20:00:00.000Z (2020-12-22 00:00 +4). Server (works in +3) translate it to 2020-12-21T23:00:00.000+03:00, cuts time out and inserts it as 

insert into test_table (test_date) values ('2020-12-21 +03')

Then server returns 2020-12-21. Browser makes 2020-12-21 04:00. User sees 2020-12-21. Error.

Solution 2

Specify the user's time zone, not the server's zone, when saving to the database. 

String userZoneId = entity.getZoneId();
statement.setDate(1, new java.sql.Date(entity.getTestDate().getTime()),
        Calendar.getInstance(TimeZone.getTimeZone(userZoneId)));

I can get it as a separate parameter in the request. I can do this in JavaScript. 

Intl.DateTimeFormat().resolvedOptions().timeZone;

I can try new API with polyfill: Temporal.now().timeZone().id.

Request:

{"testDate":"2020-12-21T20:00:00.000Z","zoneId":"Europe/Samara"}

Saving to DB:

insert into test_table (test_date) values ('2020-12-22 +04')

Knowing the zone, the JDBC driver transformes 2020-12-21T23:00:00.000+03:00 to 2020-12-22T00:00:00.000+04:00, and makes 2020-12-22 +04. When reading it turns to 2020-12-22 -> 2020-12-22 04:00:00. 2020-12-22 is on the form. All right.

Error

Now let's test the situation where the user is west of UTC. For example America/Chicago (-6). The selected date 2020-12-22 saved well. When reading, the server returns it back, but it turns into 2020-12-21 18:00 in user's local time and is displayed as 2020-12-21.

Solution 3

The server should return the date with the time 2020-12-22T00:00:00.000 and without a zone, then it will be converted by the browser to the beginning of the day according to local time. For this I will make a date serializer 

import java.text.SimpleDateFormat;
import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.SerializerProvider;

public class DateSerializer extends JsonSerializer<Date> {

	private final static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");

	@Override
	public void serialize(Date value, JsonGenerator gen, SerializerProvider serializers)
      throws IOException {
		gen.writeString(format.format(value));
	}
}

Now it is all right. The selected date is saved in the DB correctly. And it returns back. Tested on different time zones. Ready for prod.

Error

There will be bug on prod. User may see wrong date. It is not a day before or a day after, it is another user's date.

Solution 4

java.text.SimpleDateFormat.format() is not thread safe. But I created it once for the whole application. I must create new instance for each serialization. 

private static final String format = "yyyy-MM-dd'T'HH:mm:ss";

@Override
public void serialize(Date value, JsonGenerator gen, SerializerProvider serializers)
    throws IOException {
  gen.writeString(new SimpleDateFormat(format).format(value));
}

The result is a service with a strange interface for saving the date. In another client, I have to transfer not only the date, but also calculate the time and transfer the zone for which this time is calculated. If I want to save 2020-12-22, then I must first decide on the zone. If it is +3, then I need to transfer 

{"testDate":"2020-12-21T21:00:00.000Z","zoneId":"Europe/Moscow"}

or

{"testDate":"2020-12-22T00:00:00.000+03:00","zoneId":"Europe/Moscow"}

In the last one there is the duplication of necessary information about the offset. I should remove zoneId and leave the date in the format 2020-12-22. If request contains 2020-12-21T21:00:00.000Z, then ignore a time and a zone parts - save it as 2020-12-21). The result should be returned without time too.

Solution 5

I remove my serializer from java. At the front, it is necessary to process 2020-12-22 as the beginning of the day according the local time. 

const ymd: string[] = obj.testDate.split('-');
const date: Date = new Date(ymd);

It is convenient and works in Chrome and Firefox. But the constructor with the Array parameter is not described in the standard. Therefore, the parameter is converted to a string and passed to Date.parse(). But this method works by default only for 2020-12-22. So, I write code to make string according to the standard. 

const ymd: number[] = obj.testDate.split('-').map((s: string) => Number(s));
const date: Date = new Date(ymd[0], ymd[1] - 1, ymd[2])

The next step is to discard time when saving. JDBC already does this. But this causes error for east users. Because the time is discarded from the instant by server time. So, the time must be cuted off before converting the string to a date according to server time. Another problem appears here: the browser sends the date as the beginning of the day in UTC time. That is, the code must be written on the front before sending it to the server. 

public saveEntity(entity: TestEntity): Observable<number> {
  const date: Date = entity.testDate;
	const testDate: string = [date.getFullYear(), date.getMonth() + 1, date.getDate()]
		.map(n => String(n).padStart(2, '0')).join('-');
  const body: any = Object.assign({}, entity, {testDate});
  return this.http.post<number>(CONTROLLER, body);
}

This solution works, it works for users from all zones. And I don't need to program anything on the server.

Error

Then a developer from Chicago appears on the project. And he tests the application on his computer. 2020-12-22 is sent to the server. The server turns this into 2020-12-21 18:00:00. And saves as 

insert into test_table (test_date) values ('2020-12-21 -06')

Bug. Solution works only on a server east of UTC.

Solution 6

The simplest solution is to hardcode time zone.

System.setProperty("user.timezone", "UTC")

But it is not entirely correct. What if the application already has a lot of logic depending on the fact that the server is located somewhere in the local time in the west? The problem is that Jackson treats the received date as the start of the day in UTC. I want the date to be the start of the day for the server, so I have to hardcode that zone and tell Jackson to use the server's zone for conversion. 

public class TestDateApplication {

	public static final String APP_TIMEZONE = "America/Chicago";

	public static void main(String[] args) {
		System.setProperty("user.timezone", APP_TIMEZONE);
		SpringApplication.run(TestDateApplication.class, args);
	}

import com.fasterxml.jackson.annotation.JsonFormat;

public class TestEntity {
	private int id;
	@JsonFormat(timezone = TestDateApplication.APP_TIMEZONE, pattern = "yyyy-MM-dd")
	private Date testDate;

Since the browser now send the date without a time, I can restrict the interface and not allow the format with time. If someone starts sending the time, then an error with the time zones is possible.

Solution 7

Jackson skips dates like this by not counting the time. Therefore, I need to write my own deserializer. It throws an exception if the string is longer than the specified format. 

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.JsonToken;
import com.fasterxml.jackson.databind.DeserializationContext;
import com.fasterxml.jackson.databind.JsonDeserializer;
import com.fasterxml.jackson.databind.exc.InvalidFormatException;

public class DateDeserializer extends JsonDeserializer<Date> {

  private static final String format = "yyyy-MM-dd";

  @Override
  public Date deserialize(JsonParser p, DeserializationContext ctxt)
      throws IOException, JsonProcessingException {
    if (p.hasToken(JsonToken.VALUE_STRING)) {
      String text = p.getText().trim();
      if (text.length() != format.length()) {
        throw new InvalidFormatException(p, "Wrong date", text, Date.class);
      }
      try {
        Date result = new SimpleDateFormat(format).parse(text);
        return result;
      } catch (ParseException e) {
        throw new InvalidFormatException(p, "Wrong date", text, Date.class);
      }
    }
    return (Date) ctxt.handleUnexpectedToken(Date.class, p);
  }
}

If I save the date in the east and open it in the west, then this date is the same. But in the west it may be only yesterday. This may be a mistake, depends on the task. If we are talking about the date of birth, then there is no mistake. If about the publication date of the news, then the reader in the west may see news from the future. It might look strange.

Solution 8

For such a case, I have to store the time along with the date. It depends on whether the day is the same for two time zones at this time or is different. Two types can be used for DB column: timestamp or timestamp with time zone. It seems, I don't need to store the zone, so I will use a timestamp. 

private static final String COLUMN_LABEL = "test_timestamp";

entity.setTestDate(rs.getTimestamp(COLUMN_LABEL));

statement.setTimestamp(1, new Timestamp(entity.getTestDate().getTime()));

The date 2020-12-21T20:00:00.000Z come from front. It is transferred to the base as 

insert into test_table (test_timestamptz) values ('2020-12-21 14:00:00-06')

And is saved as 2020-12-21 14:00:00. Time 2020-12-21T20:00:00.000+00:00 returns to front and user see local time. It works.

Error

There is error, if server's time zone changes. The time in DB is time in server's zone. Another server in different time zone reads wrong time. Time 2020-12-21 14:00:00 on Europe/Moscow server becomes 2020-12-21T11:00:00.000+00:00. Should be 2020-12-21T20:00:00.000+00:00.

Solution 9

The server must always be in the same zone. Or it is necessary to store dates in one zone and indicate it explicitly. Since the server was previously in America/Chicago and the time is stored in such a zone, I will hard code this zone 

private static final String COLUMN_TIMEZONE = "America/Chicago";

entity.setTestDate(rs.getTimestamp(COLUMN_LABEL,
        Calendar.getInstance(TimeZone.getTimeZone(COLUMN_TIMEZONE))));

statement.setTimestamp(1, new Timestamp(entity.getTestDate().getTime()),
        Calendar.getInstance(TimeZone.getTimeZone(COLUMN_TIMEZONE)));

To make it easier to debug, it is better to make the zone UTC. And I convert the time to UTC in the database. 

update test_table
set test_timestamp =
    (test_timestamp at time zone 'America/Chicago') at time zone 'UTC';

private static final String COLUMN_TIMEZONE = "UTC";

2020-12-21T20:00:00.000Z is come from front. Server sends to DB 

insert into test_table (test_timestamp) values ('2020-12-21 20:00:00+00')

It is stored as 2020-12-21 20:00:00. It will be readed as 2020-12-21 14:00:00 on server (-6). 2020-12-21T20:00:00.000+00:00 returns on front. It contains time zone and is shown on the form as local time.

Solution 10

It turned out the same thing that could be done right away using timestamp with time zone. This type does not store a zone. It stores the time for the UTC zone and automatically converts it to the time for the other zone. So, the code can be rewritten without specifying the zone when saving and reading. 

private static final String COLUMN_LABEL = "test_timestamptz";

entity.setTestDate(rs.getTimestamp(COLUMN_LABEL));

statement.setTimestamp(1, new Timestamp(entity.getTestDate().getTime()));

2020-12-21T20:00:00.000Z comes from front. It is sent to DB as 

insert into test_table (test_timestamptz) values ('2020-12-21 14:00:00-06')

-06 is because of java server timezone. And it is saved in the database regardless of the postgresql zone as the time 2020-12-21T20:00:00.000Z. When reading, the server receives 2020-12-21 14:00:00 according to java server timezone. The time comes to the front with the indication of the zone 2020-12-21T20:00:00.000+00:00 and the local time is shown.

Solution 11

I need the time is always shown what was selected on the form, and does not depend on the time zone of the browser or server.
For network transmission I use the format without specifying the zone. For storage I use the timestamp column. 

public saveEntity(entity: TestEntity): Observable<number> {
	const date: Date = entity.testDate;
	const testDate: string = [date.getFullYear(), date.getMonth() + 1, date.getDate()]
		.map(n => String(n).padStart(2, '0')).join('-')
		+ 'T' + [date.getHours(), date.getMinutes(), date.getSeconds()]
		.map(n => String(n).padStart(2, '0')).join(':');
	const body: any = Object.assign({}, entity, {testDate});
	return this.http.post<number>(CONTROLLER, body);
}

@JsonDeserialize(using = DateDeserializer.class)
@JsonFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss",
  timezone = TestDateApplication.APP_TIMEZONE)
private Date testDate;

In deserializer:

Date result = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss").parse(text);

I choose 2020-12-22 14:14 on the form. 2020-12-22T14:14:00 is sent. This format is translated as server local time. The same is sent to DB. 

insert into test_table (test_timestamp) values ('2020-12-22 14:14:00+04')

Since the column type is timestamp without time zone, the transferred zone is discarded, and there is no additional conversion. When reading, everything is the same. 

new Date('2020-12-22T14:14:00')
Tue Dec 22 2020 14:14:00 GMT-0600 (Central Standard Time)

Browser displays what was saved in any time zone.

Time API

Solution 1

I need just date without time. Column type - date. DTO field type - LocalDate. 

private LocalDate testDate;

statement.setObject(1, entity.getTestDate(), Types.DATE);

entity.setTestDate(rs.getObject("test_date", LocalDate.class));

Choose 2020-12-22. Browser sends 2020-12-21T21:00:00.000Z. Jackson translates it to LocalDateTime for zone UTC and cuts time off. 

insert into test_table (test_date) values ('2020-12-21'::date)

Date 2020-12-21 returns back.

new Date('2020-12-21')
Mon Dec 21 2020 03:00:00 GMT+0300 (Moscow Standard Time)

Wrong.

Solution 2

I need to deserialize with server's time zone.

public LocalDate deserialize(JsonParser p, DeserializationContext ctxt)
        throws IOException, JsonProcessingException {
    if (p.hasToken(JsonToken.VALUE_STRING)) {
        String text = p.getText().trim();
        try {
            DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                    .append(DateTimeFormatter.ISO_LOCAL_DATE_TIME)
                    .appendZoneId()
                    .toFormatter();
            LocalDate result = ZonedDateTime.parse(text, formatter)
                    .withZoneSameInstant(ZoneId.systemDefault())
                    .toLocalDate();
            return result;
        } catch (Exception e) {
            throw new InvalidFormatException(p, "Wrong date", text, Date.class);
        }
    }
    return (LocalDate) ctxt.handleUnexpectedToken(LocalDate.class, p);
}

Now DB gets the right date.

insert into test_table (test_date) values ('2020-12-22'::date)

But this doesn't work for user east of the server.

Solution 3

I can solve this by transferring the user's zone. So I need to change the field type to the type with time: LocalDateTime. 

String zoneId = entity.getZoneId();
statement.setObject(1,
    ZonedDateTime.of(entity.getTestDate(), ZoneId.systemDefault())
      .withZoneSameInstant(ZoneId.of(zoneId))
      .toLocalDate(),
    Types.DATE);

entity.setTestDate(
		LocalDateTime.of(rs.getObject(COLUMN_LABEL, LocalDate.class), LocalTime.MIN));

The date is returned from the server with the time: 2021-12-22T00:00:00. Therefore, this solution will work for users from west as well.

Solution 4

If I form a date without time on the front, then on the server I can leave only a field with the LocalDate type. And there are no more additional conversions. 

statement.setObject(1, entity.getTestDate(), Types.DATE);

entity.setTestDate(rs.getObject(COLUMN_LABEL, LocalDate.class));

This solution works, even I move the server to another time zone.

To prevent the error from transferring the date in ISO format with time, it is enough to specify the format. 

@JsonFormat(pattern = "yyyy-MM-dd")
private LocalDate testDate;

Jackson throws an exception at additional characters.

Solution 5

I can use LocalDateTime to save time. But to convert the string 2020-12-21T20:00:00.000Z to local time, I need a deserializer using ZoneDateTime. So I will use this type for field. 

statement.setObject(1,
    entity.getTestDate()
        .withZoneSameInstant(ZoneId.systemDefault())
        .toLocalDateTime(),
    Types.TIMESTAMP);

entity.setTestDate(
    ZonedDateTime.of(
        rs.getObject(COLUMN_LABEL, LocalDateTime.class),
        ZoneId.systemDefault()
        )
    );

But if I change server's time zone, all saved dates is broken.

Solution 6

I hard code time zone to save date in DB.

private static final String COLUMN_TIMEZONE = "UTC";

statement.setObject(1,
    entity.getTestDate()
      .withZoneSameInstant(ZoneId.of(COLUMN_TIMEZONE))
      .toLocalDateTime(),
    Types.TIMESTAMP);

entity.setTestDate(
    ZonedDateTime.of(
        rs.getObject(COLUMN_LABEL, LocalDateTime.class),
        ZoneId.of(COLUMN_TIMEZONE)
        )
    );

Solution 7

Now I can switch to the column type timestamptz. To insert ZonedDateTime in such column, I can use LocalDateTime. But I must to convert the time to the server's time zone. 

statement.setObject(1,
    entity.getTestDate()
      .withZoneSameInstant(ZoneId.systemDefault())
      .toLocalDateTime());

Because JDBC adds an offset based on the server's zone to the request. Postgres uses this offset for conversion to UTC. 

insert into test_table (test_timestamptz)
values ('2020-12-21 23:30:00+03'::timestamp)

I can use OffsetDateTime.

statement.setObject(1, entity.getTestDate().toOffsetDateTime());

Then what came from the browser will be transferred to DB. 

insert into test_table (test_timestamptz)
values ('2020-12-21 20:30:00+00'::timestamp with time zone)

JDBC can read only OffsetDateTime from timestamptz. 

entity.setTestDate(
    rs.getObject(COLUMN_LABEL, OffsetDateTime.class).toZonedDateTime()
    );

So I change the DTO field to OffsetDateTime.

Solution 8

To save the selected time and display it regardless of the browser zone or server zone, it is enough to send the local time without zone. 

public saveEntity(entity: TestEntity): Observable<number> {
	const date: Date = entity.testDate;
	const testDate: string = [date.getFullYear(), date.getMonth() + 1, date.getDate()]
		.map(n => String(n).padStart(2, '0')).join('-')
		+ 'T' + [date.getHours(), date.getMinutes(), date.getSeconds()]
		.map(n => String(n).padStart(2, '0')).join(':');
	const body: any = Object.assign({}, entity, {testDate});
	return this.http.post<number>(CONTROLLER, body);
}

I use LocalDateTime and timestamp. 

statement.setObject(1, entity.getTestDate());

entity.setTestDate(rs.getObject(COLUMN_LABEL, LocalDateTime.class));

No additional Jackson setup is needed. When transferring time with the zone, Jackson throws error.

Conclusion

To avoid some mistakes, I should make some setup from start: the date format in request and response, the zone in which the Java server works, the time zone in which the time is stored in DB.

Additional errors may appear due to outdated tzdata. PostgreSQL has its own tzdata. If there are columns timestamptz, then tzdata is used. Minor releases need to be tracked. If Postgres is compiled with the with-system-tzdata flag, then I need to update the system tzdata. Java has its own tzdata. I need to update it too. Update can be made apart from full jre update. Joda-time has its own tzdata.

All solutions are available in repository https://github.com/Qwertovsky/test_date.