CODEWITHSUNDEEP
javapostgresqljpa
Nelio Alves
Nelio Alves

Reputation: 1371

JPA, Postgresql and SQL INSERT statement: UTC timestamp not working properly

I wrote a simple Java entity with two Instant fields:

@Entity
@Table(name = "tb_foo")
public class Foo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(columnDefinition = "TIMESTAMP WITHOUT TIME ZONE")
    private Instant date1;
    
    @Column(columnDefinition = "TIMESTAMP WITHOUT TIME ZONE")
    private Instant date2;
    ...

Then I wrote a simple import.sql in the project's resources root folder to test if UTC/Local is working:

INSERT INTO tb_foo (date1, date2) VALUES ('2020-07-22T20:50:01.12345Z', NOW());

As my local time zone is GMT-3, that 20:50:01 above should appear on client as 17:50:01, and that NOW() should appear equivalent to my local time (around 0:25 at the moment I tested). I tested it in H2 database and it worked perfectly:

enter image description here

This is the DDL & INSERT auto generated by JPA to H2:

create table tb_foo (
    id bigint generated by default as identity,
    date1 TIMESTAMP WITHOUT TIME ZONE,
    date2 TIMESTAMP WITHOUT TIME ZONE,
    primary key (id)
)
INSERT INTO tb_foo (date1, date2) VALUES ('2020-07-22T20:50:01.12345Z', NOW())

However, when I tested the same Java and SQL codes on Postgres (12.x), it didn't work (20h UTC showed as 20 instead of 17):

enter image description here

This is the DDL & INSERT auto generated by JPA to Postgresql:

create table tb_foo (
    id int8 generated by default as identity,
    date1 TIMESTAMP WITHOUT TIME ZONE,
    date2 TIMESTAMP WITHOUT TIME ZONE,
    primary key (id)
)
INSERT INTO tb_foo (date1, date2) VALUES ('2020-07-22T20:50:01.12345Z', NOW())

What did I miss?

Update:

I've made another test: I wrote another database insertion through Java (not SQL INSERT):

Foo foo = new Foo(null, Instant.parse("2020-07-23T20:50:01Z"), Instant.now());
fooRepository.save(foo);

And the literal UTC timestamp 20:50 was properly inserted into Postgresql this time! It appeared as 17:50 on pgadmin client! So maybe the right question is:

How to properly specify a literal UTC timestamp to Postgresql in a SQL INSERT call to store it in a timestamp WITHOUT time zone?

I've tried it in two ways:

INSERT INTO tb_foo (date1, date2) VALUES ('2020-07-22T20:50:01.12345Z', NOW());

INSERT INTO tb_foo (date1, date2) VALUES ('2020-07-22 20:50:01 +00', NOW());

And both didn't work (they both show 20:50 instead of 17:50 on the pgadmin client in a GMT-03 system).

Upvotes: 2

Views: 1565

Answers (2)

Nelio Alves
Nelio Alves

Reputation: 1371

Got it. To specify an UTC timestamp literal in a SQL INSERT to work on a Postgresql timestamp field WITHOUT time zone, you must declare TIMESTAMP WITH TIME ZONE first:

INSERT INTO tb_foo (date1, date2) VALUES (TIMESTAMP WITH TIME ZONE '2020-07-23 20:50:01.12345+00', NOW());

or

INSERT INTO tb_foo (date1, date2) VALUES (TIMESTAMP WITH TIME ZONE '2020-07-23T20:50:01.12345Z', NOW());

That also worked for H2 database. So this is a way to provide a SQL script that works for both databases.

Upvotes: 0

cen
cen

Reputation: 2943

Probably because of: If a time zone is specified in the input for time without time zone, it is silently ignored. It ignores your 'Z' and inserts the datetime as presented.

https://www.postgresql.org/docs/12/datatype-datetime.html

Upvotes: 1

Related Questions