Date, time, and timestamp types in PostgreSQL

Introduction

We use date, time, and timestamp types to store information about when things happen. This helps us keep track of events, appointments, or any time-related data.

To record a person's birthday in a database.

To store the time a user logs into a website.

To save the exact moment a purchase was made.

To schedule meetings with date and time.

To calculate how many days have passed between two events.

Syntax

PostgreSQL

DATE
TIME
TIMESTAMP
TIMESTAMP WITH TIME ZONE

DATE stores only the date (year, month, day).

TIME stores only the time (hours, minutes, seconds).

TIMESTAMP stores both date and time without time zone.

TIMESTAMP WITH TIME ZONE stores date and time with time zone info.

Examples

This creates a table with columns for date, time, and timestamp.

PostgreSQL

CREATE TABLE events (
  event_date DATE,
  event_time TIME,
  event_timestamp TIMESTAMP
);

Inserts a row with a date, a time, and a timestamp.

PostgreSQL

INSERT INTO events (event_date, event_time, event_timestamp) VALUES
('2024-06-01', '14:30:00', '2024-06-01 14:30:00');

Retrieves the stored date, time, and timestamp values.

PostgreSQL

SELECT event_date, event_time, event_timestamp FROM events;

Sample Program

This example creates a table for appointments with date, time, and timestamp columns. It inserts two appointments and then selects all rows ordered by their ID.

PostgreSQL

CREATE TABLE appointments (
  id SERIAL PRIMARY KEY,
  appointment_date DATE,
  appointment_time TIME,
  appointment_timestamp TIMESTAMP
);

INSERT INTO appointments (appointment_date, appointment_time, appointment_timestamp) VALUES
('2024-06-10', '09:00:00', '2024-06-10 09:00:00'),
('2024-06-11', '15:30:00', '2024-06-11 15:30:00');

SELECT * FROM appointments ORDER BY id;

Important Notes

When you only need the day, use DATE to save space and keep it simple.

TIMESTAMP does not store time zone info, so times are stored as-is.

Use TIMESTAMP WITH TIME ZONE if you need to handle different time zones correctly.

Summary

Date, time, and timestamp types help store when things happen.

Use DATE for just the day, TIME for just the time, and TIMESTAMP for both.

Choose TIMESTAMP WITH TIME ZONE if you need to track time zones.