Date and Time Types

Overview of Temporal Types

PostgreSQL provides several types for working with temporal data:

Creating and Inserting Temporal Data

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  name TEXT,
  event_date DATE,
  start_time TIME,
  full_datetime TIMESTAMP,
  full_datetime_tz TIMESTAMPTZ DEFAULT now()
);

INSERT INTO events (name, event_date, start_time, full_datetime)
VALUES
  ('Meeting', '2025-06-01', '14:30:00', '2025-06-01 14:30:00');

Check current date and time:

SELECT current_date, current_time, now();

Time Zones and TIMESTAMPTZ

PostgreSQL can handle global time via TIMESTAMPTZ. It stores time in UTC and displays it based on session settings.

-- Show current time zone
SHOW timezone;

-- Change time zone for session
-- Common timezone examples:
-- SET timezone = 'America/New_York';
-- SET timezone = 'Europe/London';
-- SET timezone = 'Asia/Tokyo';
-- SET timezone = 'Australia/Sydney';
-- SET timezone = 'Pacific/Auckland';
-- SET timezone = 'UTC';
-- SET timezone = 'America/Los_Angeles';
-- SET timezone = 'Europe/Paris';
-- SET timezone = 'Asia/Dubai';
-- SET timezone = 'Africa/Cairo';

SET timezone = 'Asia/Kolkata';

-- Time will now display in local time
SELECT now();

-- Create timestamp with specific timezone
SELECT TIMESTAMP WITH TIME ZONE '2025-06-01 10:00:00+00';

Use TIMESTAMPTZ when dealing with international or web applications.

Working with INTERVALS

-- Add an interval to a date
SELECT now() + INTERVAL '7 days';

-- Subtract an interval
SELECT now() - INTERVAL '2 hours';

-- Calculate duration between two timestamps
SELECT age('2025-06-01 12:00:00', '2025-05-30 09:00:00');

-- Store intervals
CREATE TABLE durations (
  label TEXT,
  gap INTERVAL
);

INSERT INTO durations VALUES ('1 week break', INTERVAL '1 week');

Filtering with Temporal Columns

-- Filter future events
SELECT * FROM events WHERE event_date > current_date;

-- Events within next 7 days
SELECT * FROM events
WHERE full_datetime BETWEEN now() AND now() + INTERVAL '7 days';

Formatting Date/Time Output

SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');
SELECT to_char(event_date, 'Day, DD Mon YYYY') FROM events;

Common Time Functions

PostgreSQL offers a variety of functions to manipulate and extract values from date/time data:

-- Truncate to start of hour
SELECT date_trunc('hour', now());

-- Get day of the week (0 = Sunday)
SELECT extract(dow FROM current_date);

-- Get epoch time (seconds since Jan 1, 1970)
SELECT extract(epoch FROM now());

-- Convert from epoch to timestamp
SELECT to_timestamp(1686566400);

These are essential for reporting, grouping, and formatting date/time results.

Dummy Dataset for Practice

CREATE TABLE logs (
  id SERIAL,
  description TEXT,
  log_time TIMESTAMPTZ DEFAULT now()
);

INSERT INTO logs (description) VALUES
  ('Server started'),
  ('Scheduled job executed'),
  ('Database backup completed');

-- Get logs from the last 24 hours
SELECT * FROM logs
WHERE log_time > now() - INTERVAL '1 day';

Handling Invalid Dates or Edge Cases

PostgreSQL follows the Gregorian calendar and adjusts date/time operations accordingly.

Leap Years and Month-End Handling

-- Adding 1 year to Feb 29 in a leap year
SELECT DATE '2024-02-29' + INTERVAL '1 year'; -- 2025-02-28

Invalid Date Insertion

-- This will fail
INSERT INTO events (event_date) VALUES ('2025-13-01');

Always validate date formats when inserting data from external sources.