2

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

7
  • share the DDL used in H2 & Postgres Commented Jul 23, 2020 at 4:26
  • @PrasadU there you go. I've edited the question. Commented Jul 23, 2020 at 12:04
  • 2
    You stored 2020-07-22T20:50:01.12345Z into a column defined as timestamp WITHOUT time zone so the value is taken "as is" and returned "as is". It sounds as if you actually want timestamp WITH time zone in Postgres Commented Jul 23, 2020 at 12:09
  • @a_horse_with_no_name I think that's not the point. To clarify that I've tested the NOW() timestamp. As the NOW() timestamp is being shown by the pgadmin client as my local time (GTM-3), the UTC literal 20:50 should be appearing as 17:50, like the H2 client. The NOW() timestamp is a way to test if the db client shows timestamps without time zone to the user as local or as UTC (in both cases it was local). Commented Jul 23, 2020 at 12:18
  • 1
    Nothing is really forcing the client (I assume pgadmin in this case) to adjust the stored time to your zone. I would argue postgres client is showing you the correct value by not doing anything to it. It is up to your backend or web client to do the final display adjustments when serving the data to end user. Commented Jul 23, 2020 at 12:40

2 Answers 2

1

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

Sign up to request clarification or add additional context in comments.

Comments

0

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.

1 Comment

You can either use timestamp WITHOUT TIMEZONE and handle UTC-everywhere pattern in your backend applications. Or use WITH TIMEZONE but make sure your server and sessions are properly setting the UTC zone. See dba.stackexchange.com/questions/59006/… (remember to read Shay Rojansky answer in addition to top voted)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.