PostgreSQL Date Time Types

Introduction

Working with dates and times is a fundamental aspect of database management. Whether you're tracking user registrations, monitoring transaction timestamps, or scheduling future events, PostgreSQL provides robust date and time data types to handle these requirements efficiently.

This guide will walk you through PostgreSQL's date and time types, showing you how to store, manipulate, and query temporal data. By the end, you'll have a solid understanding of how to work with date and time data in your PostgreSQL database.

Date and Time Types Overview

PostgreSQL offers several specialized types for handling temporal data:

Data Type	Description	Storage Size	Range
DATE	Calendar date (year, month, day)	4 bytes	4713 BC to 5874897 AD
TIME	Time of day (without time zone)	8 bytes	00:00:00 to 24:00:00
TIME WITH TIME ZONE	Time of day (with time zone)	12 bytes	00:00:00+1559 to 24:00:00-1559
TIMESTAMP	Date and time (without time zone)	8 bytes	4713 BC to 294276 AD
TIMESTAMP WITH TIME ZONE	Date and time (with time zone)	8 bytes	4713 BC to 294276 AD
INTERVAL	Time interval	16 bytes	-178000000 years to 178000000 years

Let's explore each of these types in detail.

The DATE Type

The DATE type stores a calendar date (year, month, day) without time information.

Syntax and Formats

sql

-- Creating a table with a DATE column
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE
);

-- Inserting DATE values
INSERT INTO events (event_name, event_date) VALUES 
('Company Anniversary', '2023-06-15'),
('Product Launch', '2023-07-22'),
('Annual Meeting', 'July 4, 2023');  -- PostgreSQL accepts various formats

PostgreSQL accepts several date input formats:

• ISO 8601 format: 'YYYY-MM-DD'
• SQL standard format: 'MM/DD/YYYY'
• Traditional PostgreSQL format: 'DD-MON-YYYY'
• Text month format: 'Month DD, YYYY'

DATE Functions and Operations

PostgreSQL provides numerous functions for working with dates:

sql

-- Current date
SELECT CURRENT_DATE;

-- Creating a date
SELECT DATE '2023-06-15';

-- Extract parts from a date
SELECT EXTRACT(YEAR FROM DATE '2023-06-15') AS year,
       EXTRACT(MONTH FROM DATE '2023-06-15') AS month,
       EXTRACT(DAY FROM DATE '2023-06-15') AS day;

-- Date arithmetic
SELECT DATE '2023-06-15' + 7 AS one_week_later;
SELECT DATE '2023-06-15' - DATE '2023-06-01' AS days_difference;

Output:

 year | month | day 
------+-------+-----
 2023 |     6 | 15
(1 row)

 one_week_later 
----------------
 2023-06-22
(1 row)

 days_difference 
----------------
 14
(1 row)

The TIME Type

The TIME type stores the time of day without date information.

TIME vs TIME WITH TIME ZONE

PostgreSQL offers two time types:

• TIME (without time zone): Stores only hours, minutes, seconds, and fractional seconds
• TIME WITH TIME ZONE: Additionally stores the time zone offset

sql

CREATE TABLE shifts (
    shift_id SERIAL PRIMARY KEY,
    shift_name VARCHAR(50),
    start_time TIME,
    end_time TIME,
    international_start TIME WITH TIME ZONE
);

INSERT INTO shifts (shift_name, start_time, end_time, international_start) VALUES
('Morning', '08:00:00', '16:00:00', '08:00:00-05:00'),
('Evening', '16:00:00', '00:00:00', '16:00:00+01:00');

SELECT * FROM shifts;

Output:

 shift_id | shift_name | start_time | end_time | international_start 
----------+------------+------------+----------+---------------------
        1 | Morning    | 08:00:00   | 16:00:00 | 08:00:00-05
        2 | Evening    | 16:00:00   | 00:00:00 | 16:00:00+01
(2 rows)

TIME Functions and Operations

sql

-- Current time
SELECT CURRENT_TIME;

-- Creating time values
SELECT TIME '13:30:45';

-- Extract parts from time
SELECT EXTRACT(HOUR FROM TIME '13:30:45') AS hour,
       EXTRACT(MINUTE FROM TIME '13:30:45') AS minute,
       EXTRACT(SECOND FROM TIME '13:30:45') AS second;

-- Time arithmetic (returns interval)
SELECT TIME '15:00:00' - TIME '09:30:00' AS time_difference;

-- Adding an interval to time
SELECT TIME '09:30:00' + INTERVAL '2 hours 30 minutes' AS new_time;

Output:

 hour | minute | second 
------+--------+--------
   13 |     30 |     45
(1 row)

 time_difference 
-----------------
 05:30:00
(1 row)

    new_time    
----------------
 12:00:00
(1 row)

The TIMESTAMP Type

The TIMESTAMP type combines both date and time information, providing a complete point in time.

TIMESTAMP vs TIMESTAMP WITH TIME ZONE

Like TIME, TIMESTAMP comes in two variants:

• TIMESTAMP (without time zone): Stores year, month, day, hour, minute, second, and fractional seconds
• TIMESTAMP WITH TIME ZONE: Additionally stores the time zone offset

sql

CREATE TABLE user_activities (
    activity_id SERIAL PRIMARY KEY,
    user_id INTEGER,
    activity_type VARCHAR(50),
    local_timestamp TIMESTAMP,
    global_timestamp TIMESTAMP WITH TIME ZONE
);

INSERT INTO user_activities (user_id, activity_type, local_timestamp, global_timestamp) VALUES
(101, 'Login', '2023-06-15 08:30:00', '2023-06-15 08:30:00-05:00'),
(102, 'Purchase', '2023-06-15 14:45:00', '2023-06-15 14:45:00+01:00');

SELECT * FROM user_activities;

Output:

 activity_id | user_id | activity_type |    local_timestamp     |       global_timestamp       
-------------+---------+---------------+------------------------+------------------------------
           1 |     101 | Login         | 2023-06-15 08:30:00    | 2023-06-15 08:30:00-05
           2 |     102 | Purchase      | 2023-06-15 14:45:00    | 2023-06-15 14:45:00+01
(2 rows)

TIMESTAMP Functions and Operations

sql

-- Current timestamp
SELECT CURRENT_TIMESTAMP;

-- Creating timestamp values
SELECT TIMESTAMP '2023-06-15 14:30:00';
SELECT TIMESTAMP WITH TIME ZONE '2023-06-15 14:30:00-07:00';

-- Extract parts from timestamp
SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-06-15 14:30:00') AS year,
       EXTRACT(MONTH FROM TIMESTAMP '2023-06-15 14:30:00') AS month,
       EXTRACT(DAY FROM TIMESTAMP '2023-06-15 14:30:00') AS day,
       EXTRACT(HOUR FROM TIMESTAMP '2023-06-15 14:30:00') AS hour;

-- Timestamp arithmetic
SELECT TIMESTAMP '2023-06-15 14:30:00' + INTERVAL '2 days 3 hours' AS future_time;
SELECT TIMESTAMP '2023-06-15 14:30:00' - TIMESTAMP '2023-06-10 08:15:00' AS time_elapsed;

Output:

 year | month | day | hour 
------+-------+-----+------
 2023 |     6 |  15 |   14
(1 row)

      future_time       
------------------------
 2023-06-17 17:30:00
(1 row)

 time_elapsed 
--------------
 5 days 06:15:00
(1 row)

The INTERVAL Type

The INTERVAL type represents a duration or span of time. It's useful for calculations involving time periods.

Working with INTERVALS

sql

-- Creating intervals
SELECT INTERVAL '1 year 2 months 3 days 4 hours 5 minutes 6 seconds';
SELECT INTERVAL '1-2';  -- 1 year, 2 months
SELECT INTERVAL '4 5:12:10.222';  -- 4 days, 5 hours, 12 minutes, 10.222 seconds

-- Arithmetic with intervals
SELECT INTERVAL '1 day' + INTERVAL '3 hours';
SELECT INTERVAL '1 month' * 3;
SELECT INTERVAL '1 day' / 2;

-- Using intervals with timestamps
SELECT CURRENT_TIMESTAMP + INTERVAL '7 days' AS one_week_from_now;

Output:

           interval           
------------------------------
 1 year 2 mons 3 days 04:05:06
(1 row)

  interval  
------------
 1 year 2 mons
(1 row)

    interval     
-----------------
 4 days 05:12:10.222
(1 row)

    interval     
-----------------
 1 day 03:00:00
(1 row)

    interval    
---------------
 3 mons
(1 row)

  interval   
-------------
 12:00:00
(1 row)

Practical Examples

Let's look at some real-world scenarios where date and time types are essential.

Example 1: Tracking User Registration and Activity

sql

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    registration_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP WITH TIME ZONE
);

INSERT INTO users (username, email) VALUES 
('johndoe', '[email protected]'),
('janedoe', '[email protected]');

-- Update last login time
UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE username = 'johndoe';

-- Find users who registered in the last 30 days
SELECT username, registration_date 
FROM users 
WHERE registration_date > CURRENT_TIMESTAMP - INTERVAL '30 days';

-- Calculate days since last login
SELECT username, 
       EXTRACT(DAY FROM (CURRENT_TIMESTAMP - last_login)) AS days_since_login
FROM users
WHERE last_login IS NOT NULL;

Example 2: Scheduling System

sql

CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    client_name VARCHAR(100) NOT NULL,
    appointment_start TIMESTAMP WITH TIME ZONE NOT NULL,
    appointment_end TIMESTAMP WITH TIME ZONE NOT NULL,
    appointment_type VARCHAR(50),
    CONSTRAINT valid_period CHECK (appointment_end > appointment_start)
);

INSERT INTO appointments (client_name, appointment_start, appointment_end, appointment_type) VALUES
('Alice Johnson', '2023-06-20 09:00:00-05:00', '2023-06-20 10:00:00-05:00', 'Consultation'),
('Bob Smith', '2023-06-20 11:00:00-05:00', '2023-06-20 12:30:00-05:00', 'Treatment');

-- Find appointments for a specific date
SELECT client_name, 
       appointment_start,
       appointment_end,
       appointment_end - appointment_start AS duration
FROM appointments
WHERE appointment_start::DATE = '2023-06-20';

-- Check for available time slots
SELECT EXISTS (
    SELECT 1 FROM appointments
    WHERE appointment_start < '2023-06-20 14:00:00-05:00'
    AND appointment_end > '2023-06-20 13:00:00-05:00'
) AS time_slot_taken;

Example 3: Sales Analysis by Date

sql

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    sale_date DATE NOT NULL,
    quantity INTEGER NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
);

INSERT INTO sales (product_id, sale_date, quantity, amount) VALUES
(101, '2023-05-15', 5, 249.95),
(102, '2023-05-15', 2, 99.98),
(101, '2023-05-16', 3, 149.97),
(103, '2023-06-01', 1, 199.99);

-- Daily sales summary
SELECT sale_date, SUM(amount) AS daily_total
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

-- Monthly sales summary
SELECT 
    EXTRACT(YEAR FROM sale_date) AS year,
    EXTRACT(MONTH FROM sale_date) AS month,
    SUM(amount) AS monthly_total
FROM sales
GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
ORDER BY year, month;

-- Compare sales with previous month
WITH monthly_sales AS (
    SELECT 
        EXTRACT(YEAR FROM sale_date) AS year,
        EXTRACT(MONTH FROM sale_date) AS month,
        SUM(amount) AS monthly_total
    FROM sales
    GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
)
SELECT 
    current.year,
    current.month,
    current.monthly_total,
    previous.monthly_total AS prev_month_total,
    current.monthly_total - COALESCE(previous.monthly_total, 0) AS difference
FROM monthly_sales current
LEFT JOIN monthly_sales previous ON
    (current.year = previous.year AND current.month = previous.month - 1)
    OR (current.year = previous.year + 1 AND current.month = 1 AND previous.month = 12)
ORDER BY current.year, current.month;

Date Time Functions and Operators

PostgreSQL provides a rich set of functions for working with date and time data:

Common Date/Time Functions

sql

-- Current date/time functions
SELECT 
    CURRENT_DATE AS today,
    CURRENT_TIME AS current_time,
    CURRENT_TIMESTAMP AS now,
    NOW() AS also_now,
    TIMEOFDAY() AS time_of_day;

-- Formatting date/time
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'Month DD, YYYY HH12:MI:SS AM') AS formatted_date;

-- Date/time parts
SELECT 
    DATE_PART('year', CURRENT_DATE) AS year,
    DATE_PART('month', CURRENT_DATE) AS month,
    DATE_PART('day', CURRENT_DATE) AS day;

-- Date/time truncation
SELECT 
    DATE_TRUNC('hour', CURRENT_TIMESTAMP) AS truncated_to_hour,
    DATE_TRUNC('day', CURRENT_TIMESTAMP) AS truncated_to_day,
    DATE_TRUNC('month', CURRENT_TIMESTAMP) AS truncated_to_month;

-- Age calculation
SELECT AGE(CURRENT_DATE, DATE '1990-05-15') AS age_since_date;

-- Day of week
SELECT 
    TO_CHAR(DATE '2023-06-15', 'Day') AS day_name,
    EXTRACT(DOW FROM DATE '2023-06-15') AS day_number; -- 0 (Sunday) to 6 (Saturday)

Working Across Time Zones

sql

-- Convert between time zones
SELECT 
    TIMESTAMP WITH TIME ZONE '2023-06-15 12:00:00-05:00' AS original_time,
    TIMESTAMP WITH TIME ZONE '2023-06-15 12:00:00-05:00' AT TIME ZONE 'UTC' AS in_utc,
    TIMESTAMP WITH TIME ZONE '2023-06-15 12:00:00-05:00' AT TIME ZONE 'Europe/London' AS in_london;

-- Get available time zones
SELECT * FROM pg_timezone_names LIMIT 5;

-- Current time in different time zones
SELECT 
    CURRENT_TIMESTAMP AS server_time,
    CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AS utc_time,
    CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York' AS ny_time,
    CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS tokyo_time;

Best Practices for Date and Time

1. Use TIMESTAMP WITH TIME ZONE for global applications

   When working with users across different time zones, store timestamps with time zone information to avoid confusion.

2. Be consistent with time zones

   Consider standardizing on UTC for storage and converting to local time zones only for display purposes.

3. Use appropriate indexing for date/time columns

   sql

   CREATE INDEX idx_appointments_start ON appointments(appointment_start);

4. Validate date ranges

   Use constraints to ensure logical date/time values:

   sql

   ALTER TABLE appointments 
   ADD CONSTRAINT valid_appointment_period 
   CHECK (appointment_end > appointment_start);

5. Be careful with date/time arithmetic

   Remember that months have different lengths, and watch out for daylight saving time changes.

6. Format dates consistently

   Use ISO 8601 format (YYYY-MM-DD) for data exchange and consistent storage.

Summary

PostgreSQL's date and time types provide powerful tools for handling temporal data in your database:

• DATE for calendar dates
• TIME and TIME WITH TIME ZONE for time-of-day values
• TIMESTAMP and TIMESTAMP WITH TIME ZONE for complete date and time points
• INTERVAL for durations and time periods

These types, along with PostgreSQL's comprehensive set of date/time functions and operators, allow you to effectively manage all aspects of temporal data in your applications.

Additional Resources

• Practice building a scheduling system that handles different time zones
• Create a reporting dashboard that aggregates data by various time periods
• Implement a feature that calculates business days while excluding weekends and holidays

Exercises

1. Write a query to find all events happening next week.
2. Create a function that returns the number of business days between two dates.
3. Write a query to categorize timestamps by time of day (morning, afternoon, evening, night).
4. Implement a table structure to store recurring events (daily, weekly, monthly).
5. Create a query to show data aggregated by hour of day to identify peak usage times.