Storing Date and Time in PostgreSQL Using Java

1. Introduction

Storing date and time information in a database is a common task in software development. With many different formats, time zones, and storage formats, dealing with date and time can be a complex task that can lead to many problems if not handled carefully.

In this tutorial, we’ll look at the date and time classes the Java Date/Time API provides and how PostgreSQL persists them.

2. Setup

Throughout this article, we’ll leverage a Spring Boot application with Spring Data JPA to persist date and time values in a PostgreSQL database.

First, let’s create an entity that contains the fields for the different date and time classes from Java’s Date/Time API:

@Entity
public class DateTimeValues {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private Date date;
    private LocalDate localDate;
    private LocalDateTime localDateTime;
    private Instant instant;
    private ZonedDateTime zonedDateTime;
    private LocalTime localTime;
    private OffsetDateTime offsetDateTime;
    private java.sql.Date sqlDate;

    // getters and setters ...
}

In addition, we’ll add a default constructor to initialize all date/time fields with a fixed time:

public DateTimeValues() {
    Clock clock = Clock.fixed(Instant.parse("2024-08-01T14:15:00Z"), ZoneId.of("UTC"));

    this.date = new Date(clock.millis());
    this.localDate = LocalDate.now(clock);
    this.localDateTime = LocalDateTime.now(clock);
    this.zonedDateTime = ZonedDateTime.now(clock);
    this.instant = Instant.now(clock);
    this.localTime = LocalTime.now(clock);
    this.offsetDateTime = OffsetDateTime.now(clock);
    this.sqlDate = java.sql.Date.valueOf(LocalDate.now(clock));
}

Here, we pass the fixed clock as a parameter to methods that create a date or time object for the current time. Notably, we set the time zone to UTC.

3. PostgreSQL Mapping

We can have Spring Boot generate the database schema for our entity automatically. For this to work, we need to configure Spring Data JPA:

spring.jpa.generate-ddl=true

Now, let’s look at how the field mapping works.

3.1. Default Mapping

Without further configuration, Spring Data JPA creates a database table for us and maps the Java types to PostgreSQL data types:

We should note that there’s a TIME WITH TIME ZONE data type in PostgreSQL. The official documentation discourages using this data type as it’s for legacy purposes. Therefore, there is no default mapping for this data type. The main reason for that is that a time zone for a time without a date doesn’t make sense in most use cases.

3.2. Custom Mapping

By default, Spring Data JPA chooses a reasonable data type mapping for us. If we want to change the mapping, we can do so using the @Column annotation:

@Column(columnDefinition = "date")
private LocalDateTime localDateTime;

Here, the @Column annotation makes sure that the column type in the database a DATE instead of TIMESTAMP WITHOUT TIME ZONE.

In addition, there’s the @Temporal annotation that maps fields of java.util.Date and java.util.Calendar:

@Temporal(TemporalType.DATE)
private Date dateAsDate;

@Temporal(TemporalType.TIMESTAMP)
private Date dateAsTimestamp;

@Temporal(TemporalType.TIME)
private Date dateAsTime;

This is mostly for legacy purposes if we still use java.util.Date, which isn’t recommended.

We cannot use the annotation on any other type. If we do that, for example, for a LocalDate field, we get an error message:

@Temporal(TemporalType.TIMESTAMP)
private LocalDate localDateAsTS;

[org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: 
  TemporalJavaType(javaType=java.time.LocalDate) as
  `jakarta.persistence.TemporalType.TIMESTAMP` not supported

4. PostgreSQL Date and Time Persistence

The official PostgreSQL documentation contains detailed information on how date and time values are persisted and converted.

Internally, date and time values are persisted as microseconds since January 1st, 2000 UTC. They are a specific moment in time and all calculations and conversions are based on that. PostgreSQL doesn’t persist the original time zone information.

Let’s look at an example to better understand what that means.

4.1. Example

Here are two timestamps that we want to persist as a TIMESTAMP WITH TIME ZONE:

Instant timeUTC = Instant.parse("2024-08-01T14:15:00+00:00");
Instant timeCET = Instant.parse("2024-08-01T14:15:00+01:00");

The first timestamp, timeUTC, is defined in UTC. The second timestamp, timeCET, is defined in CET (with an offset of +1 hour).

Even though we used two different time zones to create the timestamps, PostgreSQL stores both of them in UTC. Of course, it converts the timestamp to UTC. Simply put, TIMESTAMP WITH TIME ZONE doesn’t store the time zone but only uses the offset to convert the timestamp to UTC.

As a result, when we read the timestamps from the database, we don’t have any information about which time zone was used while persisting it.

4.2. Timestamps With and Without Time Zone

PostgreSQL provides two types of TIMESTAMP data types: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE. Values of both data types are stored in UTC, the type only affects how a timestamp is interpreted. As an example, let’s look at the following SQL code snippet:

TIMESTAMP '2024-11-22 13:15:00+05'

Here, we define the data type as TIMESTAMP, therefore PostgreSQL ignores the time zone information and treats the date literal the same as:

TIMESTAMP '2024-11-22 13:15:00'

If we want to make sure that the time zone is considered, we need to use the TIMESTAMP WITH TIME ZONE type:

TIMESTAMP WITH TIME ZONE '2024-11-22 13:15:00+05'

In general, we should use the TIMESTAMP WITH TIME ZONE data type.

5. Storing Time Zone Information

As we’ve just learned, PostgreSQL doesn’t store the time zone information used to create a date/time value. If we have a use case where we need this information, we need to store the time zone ourselves. Let’s add a field to our DateTimesValues class to handle this:

private String zoneId;

We can use this field to store the ZoneId:

this.zoneId = ZoneId.systemDefault().getId();

Then we can use the ZoneId in our application to convert the retrieved date/time values to that time zone. However, it’s important to note that this is simply a custom field of type String and doesn’t affect any other date/time field.

6. Attention Points

There are many pitfalls when working with date and time values. Let’s discuss two of them: custom data type mappings and time zone settings.

6.1. Custom Mappings

We need to be careful when using custom mappings. The Java class may contain time information which will be disregarded when converting it to a date type in PostgreSQL:

@Column(columnDefinition = "date")
private Instant instantAsDate;

Here, we map an Instant with time information to a DATE in PostgreSQL.

Let’s test the persistence with the following test:

@Test
public void givenJavaInstant_whenPersistedAsSqlDate_thenRetrievedWithoutTime() {
    DateTimeValues dateTimeValues = new DateTimeValues();
    DateTimeValues persisted = dateTimeValueRepository.save(dateTimeValues);
    DateTimeValues fromDatabase = dateTimeValueRepository.findById(persisted.getId()).get();

    Assertions.assertNotEquals(
        dateTimeValues.getInstantAsDate(),
        fromDatabase.getInstantAsDate()
    );

    Assertions.assertEquals(
        dateTimeValues.getInstantAsDate().truncatedTo(ChronoUnit.DAYS),
        fromDatabase.getInstantAsDate()
    );
}

The first assertion proves that the persisted date isn’t equal to the date that was created in Java. The second assertion proves that the date persisted without the time information.

The reason is that the PostgreSQL data type doesn’t contain time or time zone information, therefore, only the date part persists.

Similar issues might occur with other conversions, therefore it’s highly recommended to understand these implications when working with date/time values.

6.2. Time Zone Settings

Both Java and PostgreSQL provide a way to configure the time zone.

In Java, we can do this at the JVM level:

java -Duser.timezone="Europe/Amsterdam" com.baeldung.postgres.datetime

Alternatively, we can configure the time zone directly in the code:

Clock.fixed(Instant.parse("2024-08-01T14:15:00Z"), ZoneId.of("UTC"));

Here, the time zone configuration in the code takes precedence over the JVM setting.

We can find all available time zones with getAvailableZoneIds():

Set<String> zones = ZoneId.getAvailableZoneIds();

In PostgreSQL, we can configure the system time zone in the postgresql.conf configuration file, for example:

timezone = 'Europe/Vienna'

Or we can configure it at the session level:

SET TIMEZONE TO 'GMT';

We can query the possible values in PostgreSQL:

SELECT *
FROM pg_timezone_names;

The setting for the session takes preference over the server setting.

These settings only affect how values are interpreted and displayed, not how they are stored.

As a general guideline, we should never rely on the database or client setting when working with date and time. These settings can change. For example, our operating system might automatically change to another time zone when we travel. Therefore, it’s good practice to always specify time zones explicitly in the code and not depend on a specific server or client setting.

7. Conclusion

In this article, we’ve seen how to store date and time values in a PostgreSQL database using Java. We’ve learned how data types are mapped from Java to their corresponding PostgreSQL data type, that date and time are stored in UTC, and we’ve discussed some best practices when working with date and time.