Question 264940 in PostgreSQL Question posted in
The official documentation can be found here.

Why Instant is not converted from UTC to timezone of PostgreSQL?

On my PostgreSQL, if I run the script:

show timezone;

it returns

Europe/Rome

so according to this, I have configured

spring.jpa.properties.hibernate.jdbc.time_zone: Europe/Rome

I have a table TestPostgre with a column datetime_using_instant of timestamp data type.

@Data
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@Entity
@Table(name = "test_postgre")
public class TestPostgre {

    @Id
    @Column(name = "pk", nullable = false)
    private String pk;
    
    @Column(name = "datetime_using_instant", nullable = false)
    private Instant dateTimeUsingInstant;
    
}

Using JpaRepository:

@Repository
public interface TestPostgreRepository extends JpaRepository<TestPostgre, String> {
    
}

when saving a TestPostgre instance with dateTimeUsingInstant value of 2023-10-11T12:30:00Z, on PostgreSQL the value is still 2023-10-11 12:30:00.000, but between UTC and Europe/Rome there are 2 hours of offset.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    PostgreSQL Time Stamps

    In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.

    A nice tutorial for the difference between timestamp (TIMESTAMP WITHOUT TIME ZONE) and timestampz (TIMESTAMP WITH TIME ZONE) with examples can be found at postgresqltutorial


  2. 0

    Instant is an instantaneous point on the timeline. It is independent of a time zone and always provides a moment in UTC i.e. with a time zone offset of "+00:00", typically displayed as Z (stands for Zulu).

    The recommended practice is to keep the value of jdbc.time_zone as UTC

    spring.jpa.properties.hibernate.jdbc.time_zone=UTC

    There is a good tutorial here and here is what the PostgreSQL documentation says:

    For timestamp with time zone, the internally stored value is always in
    UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
    Time, GMT).

    You can always convert an Instant into other types e.g. ZonedDateTime, OffsetDateTime etc. for display purposes e.g.

    Instant instant = Instant.now();
ZonedDateTime zdt = instant.atZone(ZoneId.of("Europe/Rome"));
System.out.println(instant);
System.out.println(zdt);

    Output from a sample run:

    2023-10-12T11:18:44.036798Z
2023-10-12T13:18:44.036798+02:00[Europe/Rome]

    Online Demo

    Learn more about the modern Date-Time API from Trail: Date Time.

    Check this answer and this answer to learn how to use java.time API with JDBC.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search