Home Java Passing the datefrom the form to the database

Passing the datefrom the form to the database

by admin

Given by

Angular, PrimeNG, Spring Boot, JDBC, PostgreSQL

To be

Transmit datefrom form to database and back

Prepare

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 only need to save the date without time.I use a column with type date

Selecting the date on the form 2020-12-22 The server receives 2020-12-21T21:00:00.000Z This is the current time by UTC , as the browser is in zone +3. Java will query

statement.setObject(1, entity.getTestDate(), Types.DATE)
insert into test_table (test_date) values ('2020-12-22+03')

Java discards the time and passes automatically the timezone (by default the server zone or -Duser.timezone=Europe/Moscow ). Postgres does not consider the zone for the data type date Will be stored 2020-12-22 Reading from the database will return the same date. The Json will get

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

The browser will read this format as the beginning of the day 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)

That is, the form shows 2020-12-2203:00 or just no time 2020-12-22 All correct.

I've encountered a situation where Chrome and Firefox interpreted the date without time differently. Some as the beginning of the day by local time. At the moment, this is not reproduced on updated versions. The documentation says that this format is now standardized. But if the string is different from the format 2020-12-22T00:00:00.000+00:00 , then the behavior is not guaranteed.

The error will only pop up if you start testing a user who is east of the server's time zone. For example Europe/Samara (+4). Selects on the form 2020-12-22 The server will send 2020-12-21T20:00:00.000Z (2020-12-2200:00 +4). The server (running in zone +3) will translate this to 2020-12-21T23:00:00.000+03:00 , will discard the time and save it as

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

When reading, the server will give 2020-12-21 which will turn into 2020-12-2104:00 On the form we see 2020-12-21 Error.

Solution 2

When saving to the database, specify the user's time zone, not the server's zone.

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

You can get it as a separate parameter in the query. To do this in JS you can execute.

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

You can try the new API with polyfil Temporal.now().timeZone().id This parameter should contain the default zone. On older browsers it may not work or may return the wrong zone.

Request to the server :

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

Saving :

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

Knowing the zone, the driver converted 2020-12-21T23:00:00.000+03:00 in 2020-12-22T00:00:00.000+04:00 , and formed a string 2020-12-22+04 When reading , it will turn out 2020-12-22 -> 2020-12-2204:00:00 On the form we see 2020-12-22 That's right.

Now let's test the situation when the user is west of UTC. For example America/Chicago (-6). The selected date will be kept 2020-12-22 When it is read, the server will give it back, but it will turn into 2020-12-21 18:00 local time of the user and will be displayed as 2020-12-21

Solution 3

We need the server to give the date and time 2020-12-22T00:00:00.000 and no zone, then this will be converted by the browser to the beginning of the day by local time. To do 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");@Overridepublic void serialize(Date value, JsonGenerator gen, SerializerProvider serializers) throws IOException {gen.writeString(format.format(value));}}

Now everything is correct. The date selected by the user is stored in the database correctly. And it returns to the form correctly. Tested on different timezones. You can do it on ext.

There is a bug coming from the sale. Users see the wrong date. And the offset is not one day, but the wrong date at all.

Solution 4

Method java.text.SimpleDateFormat.format() Isn't thread-safe. And I created it once for the whole application. I have to make a different instance for each serialization.

private static final String format = "yyyy-MM-dd'T'HH:mm:ss";@Overridepublic void serialize(Date value, JsonGenerator gen, SerializerProvider serializers) throws IOException {gen.writeString(new SimpleDateFormat(format).format(value));}

We got a service with a weird interface for saving the date. In another client, I would have to pass not only the date, but also calculate the time and pass the zone for which that time is calculated. If I want to save 2020-12-22 then I have to decide the zone first. If it is +3, then I need to pass

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

or

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

In the last variant in general there is a duplication of the necessary information about the offset. We should remove the zone from the query and leave the date in the format 2020-12-22 If you get 2020-12-21T21:00:00.000Z , then ignore the time with the zone - save as 2020-12-21 ). The result should also return without time.

Solution 5

Getting my serializer out of java. On the front end, I need to process 2020-12-22 without time, so that it is the start of the day at local time.

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

This is handy and it works in Chrome and Firefox. But the constructor with the parameter Array is not described in the standard. Therefore, the parameter will be converted to a string and passed to Date.parse() And this method works by default only for 2020-12-22

Therefore I will write 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 the time when saving. This is already done by JDBC. But this causes an error for users from the east. Because the time is discarded from the date by server time. So the time must be discarded before converting string to server time date. Here appears another problem: the browser sends the date as the beginning of the day in UTC time. So the code has to be written before sending at the front.

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 variant works, for users from all zones works. And you do not need to program anything on the server.

Then a developer from Chicago shows up on the project. And he tests the application in his place. The server is sent 2020-12-22 The server turns this into a 2020-12-21 18:00:00 local time. And saves

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

Error. The solution works only for the server east of UTC.

Solution 6

The easiest solution is to hardcode the application area.

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

But not quite right. What if the application already has a bunch of logic depending on the fact that the server is somewhere in the local time in the west? The problem is that Jackson takes the received date as the beginning of the day in UTC. And I wanted the date to be the start of the day for the server.Then you have to hardcode that zone and tell Jackson to use the server zone for the conversion.

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 {@JsonFormat(timezone = TestDateApplication.APP_TIMEZONE, pattern = "yyyy-MM-dd")private Date testDate;

Since the browser now transmits only the date without time. Then you can restrict the interface and not allow the format with time. If someone starts transmitting the time, then a time zone bug is possible.

Solution 7

Jackson skips such dates without considering the time. So you have to write your own deserializer. It will throw 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";@Overridepublic 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 you save the datein the east and open it in the west, it will be the same.But in the west, it may still be only yesterday.This could be mistaken for an error.It depends on the task at hand.If it's about the date of birth, there's no error.If it's about the date the news was published, the reader in the west will see the news from the future. This may look strange.

Solution 8

For this case, you will have to save the time along with the date. It depends on the time whether the date is the same for different time zones at that time or different. Two types can be used to save : timestamp or timestampwith time zone I don't seem to need to store the zone, so I will 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 will arrive from the front 2020-12-21T20:00:00.000Z It will be transferred to the database as

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

And saved in the database as time 2020-12-21 14:00:00 The front will arrive at the time indicating the zone 2020-12-21T20:00:00.000+00:00 and the local time will be shown. Works.

Trouble will come if the server timezone changes. The time in the base is saved by the server's timezone. When reading on a server with a different timezone, the time will be wrong. From 2020-12-21 14:00:00 On the server Europe/Moscow you get 2020-12-21T11:00:00.000+00:00 It should have been 2020-12-21T20:00:00.000+00:00

Solution 9

Either the server must always be in the same zone. Or you have to store dates in the same zone and explicitly specify it. Since the server used to be in America/Chicago and the time is saved in that zone, I will specify that 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 zone UTC And in the base translate the time to UTC

update test_tableset test_timestamp =(test_timestamp at time zone 'America/Chicago') at time zone 'UTC';
private static final String COLUMN_TIMEZONE = "UTC";

The date will be received from the front 2020-12-21T20:00:00.000Z It will be transferred to the database as

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

And saved in the database as time 2020-12-21 20:00:00 On reading, the server will get 2020-12-21 14:00:00 by its time (-6). The front end will receive the time indicating the zone 2020-12-21T20:00:00.000+00:00 and the local time will be shown.

Solution 10

You got the same thing you could have done immediately using timestamp with time zone This type doesn't store a zone. It stores the time for the zone UTC and automatically converts it to a time for another zone. Therefore, the code can be rewritten without specifying a 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()));

The date will be received from the front 2020-12-21T20:00:00.000Z It will be transferred to the database as

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

And saved in the database regardless of the server zone as the time 2020-12-21T20:00:00.000Z On reading, the server will receive 2020-12-21 14:00:00 on its own time. The front will receive the time with the zone 2020-12-21T20:00:00.000+00:00 and the local time will be shown.

Solution 11

I want the time to be always shown as entered and not depending on the time zone of the browser or server. For network transmission I will use a format without zone indication. For storing I will use column timestamp

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);

When selecting on form 2020-12-2214:14 will send 2020-12-22T14:14:00 This is taken as the local time. And will be sent to the base.

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

Since the column type is timestamp without time zone , then the passed zone will just be discarded and there will be no additional conversion. When reading it is the same. It will display what you saved in any time zone.

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

Time API

Solution 1

Saving only the date. Column date Type of DTO field LocalDate

private LocalDatetestDate;statement.setObject(1, entity.getTestDate(), Types.DATE);entity.setTestDate(rs.getObject("test_date", LocalDate.class));

I will select on the form 2020-12-22 From the browser will come 2020-12-21T21:00:00.000Z Jackson will turn this into a LocalDateTime for the zone UTC and will discard the time.

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

The browser will return 2020-12-21 Incorrect.

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

Solution 2

You have to do deserialization with the server's timezone.

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);}

The date entered is now stored.

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

But this will not work for a user east of the server.

Solution 3

You can go by passing the user zone. Then you have to change the field type to a 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 will be returned from the server with the time 2021-12-22T00:00:00 Therefore, this solution will also work for users west of UTC.

Solution 4

If you form the date without time on the front, the server can leave only the field with the type LocalDate And do no more additional conversions.

statement.setObject(1, entity.getTestDate(), Types.DATE);entity.setTestDate(rs.getObject(COLUMN_LABEL, LocalDate.class));

This solution works even if you move the server to another area.

To prevent an error due to transmitting the date in ISO format by UTCtime, just specify the format. Additional characters will be scolded.

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

Solution 5

To save time you can use LocalDateTime But to convert the string 2020-12-21T20:00:00.000Z to local time needs a deserializer using ZoneDateTime So I will use it right away.

statement.setObject(1, entity.getTestDate().withZoneSameInstant(ZoneId.systemDefault()).toLocalDateTime(), Types.TIMESTAMP);entity.setTestDate(ZonedDateTime.of(rs.getObject(COLUMN_LABEL, LocalDateTime.class), ZoneId.systemDefault()));

If the server zone is changed, the dates will go.

Solution 6

I will explicitly specify the zone UTC to store the time.

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

You can now switch to the column type timestamptz

To save ZonedDateTime into such a column, you can use LocalDateTime But be sure to convert it to server zone. Because JDBC itself will add an offset to the query based on the server zone. Postgres will use it to convert it to UTC.

statement.setObject(1, entity.getTestDate().withZoneSameInstant(ZoneId.systemDefault()).toLocalDateTime());
insert into test_table (test_timestamptz) values ('2020-12-21 23:30:00+03'::timestamp)

You can save OffsetDateTime Then what came from the browser will be transmitted.

statement.setObject(1, entity.getTestDate().toOffsetDateTime());
insert into test_table (test_timestamptz)values ('2020-12-21 20:30:00+00'::timestampwith time zone)

The driver allows to read from the database only in OffsetDateTime

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

Therefore, the DTO field can be immediately converted to OffsetDateTime

Solution 8

To save the selected time and display it independently of the browser zone, it is sufficient to send the local time 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('-')+ '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);}

On the server use LocalDateTime and timestamp No additional Jackson setup is needed. It will swear when passing the time with the zone.

statement.setObject(1, entity.getTestDate());entity.setTestDate(rs.getObject(COLUMN_LABEL, LocalDateTime.class));

Conclusion

To avoid some mistakes, you need to agree on a few things initially. The date format in the request and response. The zone in which the server will run. The zone in which the time is stored.

Additional errors may appear, due to outdated tzdata. PostgreSQL has its own tzdata. If there are columns timestamptz , then this base is in use. It is necessary to update minor releases. PostgreSQL can be built with the flag with-system-tzdata Then you have to update the system-tzdata. Java has its own tzdata. It has to be updated as well. Can be separate from the whole jre. Joda-time has its own tzdata.

All solutions are available at repositories There are two branches there.

You may also like