CODEWITHSUNDEEP
javahibernatepostgresql-9.4
Avinash Kawale
Avinash Kawale

Reputation: 145

DEFAULT value setting of Postgres timestamp column not getting used correctly

We are facing one issue related to storing DEFAULT value of timestamp in Postgres table from a Java based hibernate-spring REST API.

The column is set as

load_ts timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,

We do not send any timestamp value from our code. This is how we specify the timestamp field in the entity class.

@Column(name = "LOAD_TS", insertable = false, updatable = false)
    private Timestamp loadTimeStamp;

We are using Amazon RDS Postgres DB. The DB parameter group value 'timezone' is set to 'UTC'.

Expected - When an insert request comes to the Database without any load_ts value sent to DB the load_ts should get a timestamp of UTC based on the default setting.

Actual - When an insert request comes to the Database without any load_ts value sent to DB the load_ts is getting set to EASTERN TIME + 10 hours. The API is running in eastern time.

When we insert records manually in the DB the timestamp gets generated and stored correctly. WE looked at the log_statements in RDS postgres but do not see that the API is sending any timestamp in the insert query. Also another interesting observation was that when we change the API timezone to UTC the timestamp is stored correctly. So somehow the API is communicating the timezone to the DB.

Application stack - 1) Hibernate core version - 5.0.7

2) Spring - 4.2

3) Postgres - 9.4

4) Postgres driver - 9.4.1212.jre7

5) RDS Postgres DB Timezone parameter group value - UTC

6) Timezone the application is running on - US Eastern time.

Upvotes: 1

Views: 3979

Answers (1)

Peter Eisentraut
Peter Eisentraut

Reputation: 36729

Your column definition doesn't make sense. The function now() returns type timestamp with time zone. The function timezone() comes in several overloaded variants, but the one you end up calling returns timestamp without time zone. And then you are inserting that into a column of type timestamp with time zone.

I don't want to wrap my head around what is actually happening here, but I suppose since U.S. Eastern time and UTC are often 5 hours apart, and you are getting a 10 hour difference, you probably have the time zone offset applied twice.

From your description, I think what you really want is to define your column as simply

load_ts timestamp with time zone DEFAULT now() NOT NULL,

and everything should be fine.

Upvotes: 1

Related Questions