Core JavaJava

Storing Date and Time in PostgreSQL Example

Photo of Mary Zheng Mary ZhengJanuary 13th, 2025Last Updated: January 10th, 2025
0 13 6 minutes read

1. Introduction

Handling date and time values is an essential task, especially when dealing with systems that track events, transactions, or logs. PostgreSQL is a powerful open-source relational database management system and it has data types: DATE, TIME, TIMESTAMP, and TIMESTAMPTZ to store date and time values. In this example, I will create a PostgreSQL store date time application in a Spring boot application.

Here is the mapping between PostgreSQL data types and Java Data types. In this example, I will use types from the java.time package instead of the java.sql package.

PostgreSQL Data TypePostgreSQL StorageDescriptionJava 7 or less Data TypeJava 8+ Data Type
DATE4 bytesrepresents the date via year, month, dayjava.sql.Datejava.time.LocalDate
TIME4 bytesrepresents the time via hour, minute, secondjava.sql.Timejava.time.LocalTime
TIMESTAMP8 bytesrepresents both the date and time, without a time zone.java.sql.Timestampjava.time.LocalDateTime
TIMESTAMPTZ8 bytesrepresents the date and time with a time zone.java.sql.Timestamp and manage the offsets manually.java.time.OffsetDateTime or java.time.ZonedDateTime

2. Setup

2.1 Start PostgreSQL locally

Downloaded the postgresql-17.2-3-windows-x64 from here and started the PostgreSQL locally.

2.2 Generate Spring Boot Project

In this step, I will create a gradle project along with Lombok and PostgreSQL libraries via Spring initializer.

Here is the generated build.gradle file.

build.gradle

plugins {
	id 'java'
	id 'org.springframework.boot' version '3.4.1'
	id 'io.spring.dependency-management' version '1.1.7'
}

group = 'org.zheng.demo'
version = '0.0.1-SNAPSHOT'

java {
	toolchain {
		languageVersion = JavaLanguageVersion.of(17)
	}
}

configurations {
  compileOnly {
    extendsFrom annotationProcessor
  }
}

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.postgresql:postgresql:42.7.4'	
	runtimeOnly 'org.postgresql:postgresql'
	compileOnly 'org.projectlombok:lombok'
    annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

tasks.named('test') {
	useJUnitPlatform()
}

Here is the generated spring boot application.

PostgresDateDemoApplication.java

package org.zheng.demo.postgresDateDemo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class PostgresDateDemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(PostgresDateDemoApplication.class, args);
	}
}

Here is the spring properties that include the PostgreSQL connection to the local PostgreSQL setup in step 2.1.

application.properties

spring.application.name=postgresDateDemo

# DataSource configuration
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username={REPLACE_WITH_YOUR_USERNAME}
spring.datasource.password={REPLACE_WITH_YOUR_PWD}
spring.datasource.driver-class-name=org.postgresql.Driver

# Hibernate Dialect for PostgreSQL
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# Hibernate Settings
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.orm.jdbc.bind=trace

# PostgreSQL connection pool settings (optional)
spring.datasource.hikari.maximum-pool-size=10

3. Java DemoDateTime

In this step, I will create a DemoDateTime.java entity class that maps demo_date_time_table. This table has columns with date, time, timestamp, and timestamptz types.

postgresql store date time table
Figure 1 demo_date_time_table columns

DemoDateTime.java

package org.zheng.demo.postgresDateDemo.entity;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.OffsetDateTime;
import java.time.ZonedDateTime;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Data;

@Entity
@Table(name = "demo_date_time_table")
@Data
public class DemoDateTime {

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

	@Column(name = "date_col")
	private LocalDate demoDate;

	@Column(name = "time_col")
	private LocalTime  demoTime;

	@Column(name = "local_timestamp_col")
	private LocalDateTime demoLocalDateTime;
	
	@Column(name = "zoned_timestamp_zocol")
	private OffsetDateTime demoZonedDateTimeOffset;

	@Column(name = "zoned_timestamp_zcol")
	private ZonedDateTime demoZonedDateTime;

	@Column(name = "zoned_timestamp_pcol")
	private ZonedDateTime demoZonedDateTimePrecision;

}

The @Column annotation maps the Java variable to its mapped database column.

4. DemoDateTimeRepo

In this step, I will create a DemoDateTimeRepo.java interface extended from the JpaRepository.

DemoDateTimeRepo.java

package org.zheng.demo.postgresDateDemo.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import org.zheng.demo.postgresDateDemo.entity.DemoDateTime;

@Repository
public interface DemoDateTimeRepo extends JpaRepository<DemoDateTime, Integer> {

}

I will create a DemoDateTimeRepoTest.java that saves data in the database.

DemoDateTimeRepoTest.java

package org.zheng.demo.postgresDateDemo.repository;

import static org.junit.jupiter.api.Assertions.assertTrue;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.zheng.demo.postgresDateDemo.entity.DemoDateTime;

@SpringBootTest
class DemoDateTimeRepoTest {

	@Autowired
	private DemoDateTimeRepo testClass;

	@Test
	void test_save_found() {
		DemoDateTime demoObj = new DemoDateTime();
		demoObj.setDemoDate(LocalDate.now());
		demoObj.setDemoTime(LocalTime.now());
		demoObj.setDemoLocalDateTime(LocalDateTime.now());
		demoObj.setDemoZonedDateTime(ZonedDateTime.now());
		demoObj.setDemoZonedDateTimePrecision(ZonedDateTime.now());
		demoObj.setDemoZonedDateTimeOffset(OffsetDateTime.of(LocalDateTime.now(), ZoneOffset.ofHours(-6)));
		testClass.save(demoObj);
		List<DemoDateTime> found = testClass.findAll();
		assertTrue( found.size() > 0);
	}

}

4.1. DemoRestController

In this step, I will create a DemoRestController.java that can save and retrieve the data from the PostgreSQL database.

DemoRestController.java

package org.zheng.demo.postgresDateDemo.rest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.zheng.demo.postgresDateDemo.entity.DemoDateTime;
import org.zheng.demo.postgresDateDemo.repository.DemoDateTimeRepo;

@RestController
public class DemoRestController {

	@Autowired
	private DemoDateTimeRepo demoRepo;

	@GetMapping("/demo")
	public List getDemoDates() {
		return demoRepo.findAll();
	}

	@PostMapping("/demo")
	public DemoDateTime saveDemo() {
		DemoDateTime demoObj = new DemoDateTime();

		demoObj.setDemoDate(LocalDate.now());
		demoObj.setDemoTime(LocalTime.now());
		demoObj.setDemoLocalDateTime(LocalDateTime.now());

		ZonedDateTime zonedNow = ZonedDateTime.now();

		demoObj.setDemoZonedDateTime(zonedNow);
		demoObj.setDemoZonedDateTimePrecision(zonedNow);
		demoObj.setDemoZonedDateTimeOffset(OffsetDateTime.of(LocalDateTime.now(), ZoneOffset.ofHours(-6)));

		return demoRepo.save(demoObj);
	}

}

5. Demonstrate

In this step, I will start the spring boot application and capture the server log.

Spring boot Server Log

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/

 :: Spring Boot ::                (v3.4.1)

2025-01-04T16:10:55.727-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.z.d.p.PostgresDateDemoApplication      : Starting PostgresDateDemoApplication using Java 17.0.11 with PID 28800 (C:\MaryTools\workspace\postgresDateDemo\bin\main started by azpm0 in C:\MaryTools\workspace\postgresDateDemo)
2025-01-04T16:10:55.736-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.z.d.p.PostgresDateDemoApplication      : No active profile set, falling back to 1 default profile: "default"
2025-01-04T16:10:56.667-06:00  INFO 28800 --- [postgresDateDemo] [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2025-01-04T16:10:56.766-06:00  INFO 28800 --- [postgresDateDemo] [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 80 ms. Found 1 JPA repository interface.
2025-01-04T16:10:57.585-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port 8080 (http)
2025-01-04T16:10:57.602-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2025-01-04T16:10:57.602-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.34]
2025-01-04T16:10:57.696-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2025-01-04T16:10:57.699-06:00  INFO 28800 --- [postgresDateDemo] [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1860 ms
2025-01-04T16:10:57.963-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2025-01-04T16:10:58.039-06:00  INFO 28800 --- [postgresDateDemo] [           main] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 6.6.4.Final
2025-01-04T16:10:58.083-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.h.c.internal.RegionFactoryInitiator    : HHH000026: Second-level cache disabled
2025-01-04T16:10:58.457-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.s.o.j.p.SpringPersistenceUnitInfo      : No LoadTimeWeaver setup: ignoring JPA class transformer
2025-01-04T16:10:58.499-06:00  INFO 28800 --- [postgresDateDemo] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2025-01-04T16:10:58.823-06:00  INFO 28800 --- [postgresDateDemo] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@45571cfc
2025-01-04T16:10:58.825-06:00  INFO 28800 --- [postgresDateDemo] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2025-01-04T16:10:58.880-06:00  WARN 28800 --- [postgresDateDemo] [           main] org.hibernate.orm.deprecation            : HHH90000025: PostgreSQLDialect does not need to be specified explicitly using 'hibernate.dialect' (remove the property setting and it will be selected by default)
2025-01-04T16:10:58.907-06:00  INFO 28800 --- [postgresDateDemo] [           main] org.hibernate.orm.connections.pooling    : HHH10001005: Database info:
	Database JDBC URL [Connecting through datasource 'HikariDataSource (HikariPool-1)']
	Database driver: undefined/unknown
	Database version: 17.2
	Autocommit mode: undefined/unknown
	Isolation level: undefined/unknown
	Minimum pool size: undefined/unknown
	Maximum pool size: undefined/unknown
2025-01-04T16:10:59.953-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000489: No JTA platform available (set 'hibernate.transaction.jta.platform' to enable JTA platform integration)
Hibernate: 
    create table demo_date_time_table (
        id integer not null,
        date_col date,
        local_timestamp_col timestamp(6),
        time_col time(6),
        zoned_timestamp_zcol timestamp(6) with time zone,
        zoned_timestamp_zocol timestamp(6) with time zone,
        zoned_timestamp_pcol timestamp(6) with time zone,
        primary key (id)
    )
2025-01-04T16:11:00.033-06:00  INFO 28800 --- [postgresDateDemo] [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2025-01-04T16:11:00.411-06:00  WARN 28800 --- [postgresDateDemo] [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2025-01-04T16:11:00.909-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 8080 (http) with context path '/'
2025-01-04T16:11:00.928-06:00  INFO 28800 --- [postgresDateDemo] [           main] o.z.d.p.PostgresDateDemoApplication      : Started PostgresDateDemoApplication in 5.749 seconds (process running for 6.25)
  • Line 37-46: the create table DDL is generated by the Hibernate. Note: the generated timestamp has the default precision of 6. If you like, you can change the precision with the following SQL: alter table demo_date_time_table alter column zoned_timestamp_pcol SET DATA TYPE TIMESTAMPTZ(3) ;

5.1 Test PostgreSQL Store Date Time via ThunderClient

In this step, I will test the POST http://localhost:8080/demo.

POST http://localhost:8080/demo Response

{
  "id": 652,
  "demoDate": "2025-01-04",
  "demoTime": "16:16:26.0627956",
  "demoLocalDateTime": "2025-01-04T16:16:26.0627956",
  "demoZonedDateTimeOffset": "2025-01-04T16:16:26.0627956-06:00",
  "demoZonedDateTime": "2025-01-04T16:16:26.0627956-06:00",
  "demoZonedDateTimePrecision": "2025-01-04T16:16:26.0627956-06:00"
}

Capture the server log.

POST http://localhost:8080/demo Server Log

Hibernate: 
    insert 
    into
        demo_date_time_table
        (date_col, local_timestamp_col, time_col, zoned_timestamp_zcol, zoned_timestamp_zocol, zoned_timestamp_pcol, id) 
    values
        (?, ?, ?, ?, ?, ?, ?)
2025-01-04T16:16:26.140-06:00 TRACE 28800 --- [postgresDateDemo] [nio-8080-exec-2] org.hibernate.orm.jdbc.bind              : binding parameter (1:DATE) <- [2025-01-04]
2025-01-04T16:16:26.143-06:00 TRACE 28800 --- [postgresDateDemo] [nio-8080-exec-2] org.hibernate.orm.jdbc.bind              : binding parameter (2:TIMESTAMP) <- [2025-01-04T16:16:26.062795600]
2025-01-04T16:16:26.143-06:00 TRACE 28800 --- [postgresDateDemo] [nio-8080-exec-2] org.hibernate.orm.jdbc.bind              : binding parameter (3:TIME) <- [16:16:26.062795600]
2025-01-04T16:16:26.143-06:00 TRACE 28800 --- [postgresDateDemo] [nio-8080-exec-2] org.hibernate.orm.jdbc.bind              : binding parameter (4:TIMESTAMP_UTC) <- [2025-01-04T16:16:26.062795600-06:00[America/Chicago]]
2025-01-04T16:16:26.143-06:00 TRACE 28800 --- [postgresDateDemo] [nio-8080-exec-2] org.hibernate.orm.jdbc.bind              : binding parameter (5:TIMESTAMP_UTC) <- [2025-01-04T16:16:26.062795600-06:00]
2025-01-04T16:16:26.143-06:00 TRACE 28800 --- [postgresDateDemo] [nio-8080-exec-2] org.hibernate.orm.jdbc.bind              : binding parameter (6:TIMESTAMP_UTC) <- [2025-01-04T16:16:26.062795600-06:00[America/Chicago]]
2025-01-04T16:16:26.143-06:00 TRACE 28800 --- [postgresDateDemo] [nio-8080-exec-2] org.hibernate.orm.jdbc.bind              : binding parameter (7:INTEGER) <- [652]
  • Line 8-13: show the binding value for each data type.

5.2 View PostgreSQL Date Time via DBeaver

In this step, I will view the date and time data via DBeaver tool with a select statement.

Select SQL

select * from demo_date_time_table;
postgresql store date time sql
Figure 2. Select SQL Results
  • DBeaver defaults the timestamp with the “yyyy-MM-dd HH:mm:ss.SSS” format. You can adjust it

6. Conclusion

In this example, I created a spring boot application that utilized PostgreSQL to store the date and time. By default, PostgreSQL’s TIMESTAMP data type has microsecond precision (TIMESTAMP(6)). In Java, the java.time API does not specify the precision of fractional seconds. However, we can control the precision of the timestamp by formatting the timestamp appropriately before passing it to PostgreSQL. PostgreSQL will accept timestamps with fractional seconds (e.g., 2025-01-04 14:30:00.123456 for microseconds), and it will round or truncate the values based on the column precision.

7. Download

This was an example of a gradle project which stored the date and time values in a PostgreSQL database.

Download
You can download the full source code of this example here: Storing Date and Time in PostgreSQL Example
Tags
Photo of Mary Zheng Mary ZhengJanuary 13th, 2025Last Updated: January 10th, 2025
0 13 6 minutes read
Photo of Mary Zheng

Mary Zheng

Mary graduated from the Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She worked as a lead Software Engineer where she led and worked with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button