Date and Time Functions

Working with dates and times is a fundamental aspect of database management. PostgreSQL provides comprehensive support for temporal data, offering powerful functions to manipulate, analyze, and format date and time values. Whether you're building scheduling systems, analyzing time-series data, or managing project deadlines, mastering these functions is essential.

Sample Data Setup

Let's create an events table to demonstrate PostgreSQL's date/time capabilities:

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_name TEXT,
  start_time TIMESTAMP,
  end_time TIMESTAMP
);

INSERT INTO events (event_name, start_time, end_time) VALUES
('Webinar A', '2025-06-10 14:00:00', '2025-06-10 15:30:00'),
('Conference B', '2025-07-01 09:00:00', '2025-07-01 17:00:00'),
('Meeting C', '2025-06-13 11:00:00', '2025-06-13 12:00:00');

Current Date and Time Functions

PostgreSQL provides several functions to retrieve current date and time:

SELECT 
  CURRENT_DATE AS today,
  CURRENT_TIME AS now_time,
  CURRENT_TIMESTAMP AS now_timestamp;

Extracting Date/Time Components

Use the EXTRACT() function to get specific parts of a date or timestamp:

SELECT
  event_name,
  EXTRACT(DAY FROM start_time) AS day,
  EXTRACT(MONTH FROM start_time) AS month,
  EXTRACT(YEAR FROM start_time) AS year,
  EXTRACT(DOW FROM start_time) AS day_of_week,
  EXTRACT(HOUR FROM start_time) AS hour
FROM events;

Date/Time Arithmetic

Perform calculations with dates and times using simple arithmetic:

SELECT
  event_name,
  end_time - start_time AS duration,
  start_time + INTERVAL '1 day' AS next_day,
  start_time - INTERVAL '2 hours' AS prep_time
FROM events;

Working with Intervals

PostgreSQL supports various interval formats for flexible date arithmetic:

SELECT
  event_name,
  start_time + INTERVAL '1 week' AS one_week_later,
  start_time + INTERVAL '3 months' AS quarterly_review,
  start_time + INTERVAL '1 year 2 months 3 days' AS complex_interval
FROM events;

Age Calculation

Calculate time differences using the AGE() function:

SELECT
  event_name,
  AGE(start_time) AS time_since_event,
  AGE(CURRENT_DATE, '1990-01-01') AS age_example
FROM events;

Date and Time Formatting

Format timestamps for display using TO_CHAR():

SELECT
  event_name,
  TO_CHAR(start_time, 'YYYY-MM-DD') AS date_only,
  TO_CHAR(start_time, 'HH12:MI AM') AS time_12hr,
  TO_CHAR(start_time, 'Day, DD Month YYYY') AS full_format
FROM events;

Date Range Filtering

Filter records based on date conditions:

-- Events after a specific date
SELECT * FROM events
WHERE start_time >= '2025-06-11';

-- Events within a date range
SELECT * FROM events
WHERE start_time BETWEEN '2025-06-01' AND '2025-06-30';

-- Events in the current month
SELECT * FROM events
WHERE EXTRACT(MONTH FROM start_time) = EXTRACT(MONTH FROM CURRENT_DATE)
  AND EXTRACT(YEAR FROM start_time) = EXTRACT(YEAR FROM CURRENT_DATE);

Timezone Handling

Work with different timezones using AT TIME ZONE:

SELECT
  event_name,
  start_time,
  start_time AT TIME ZONE 'UTC' AS utc_time,
  start_time AT TIME ZONE 'Asia/Kolkata' AS ist_time,
  start_time AT TIME ZONE 'America/New_York' AS est_time
FROM events;

Date Truncation

Round timestamps to specific intervals using DATE_TRUNC():

SELECT
  event_name,
  DATE_TRUNC('day', start_time) AS day_start,
  DATE_TRUNC('hour', start_time) AS hour_start,
  DATE_TRUNC('month', start_time) AS month_start
FROM events;

Generating Date Series

Create sequences of dates for calendar views or reporting:

-- Generate daily series
SELECT date_val::DATE
FROM GENERATE_SERIES(
  '2025-06-01'::DATE,
  '2025-06-07'::DATE,
  INTERVAL '1 day'
) AS date_val;

-- Generate hourly series
SELECT hour_val
FROM GENERATE_SERIES(
  '2025-06-10 09:00:00'::TIMESTAMP,
  '2025-06-10 17:00:00'::TIMESTAMP,
  INTERVAL '1 hour'
) AS hour_val;

Real-World Example: Project Deadline Tracker

Let's build a practical deadline management system:

CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  name TEXT,
  due_date DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO projects (name, due_date) VALUES
('Road Mapping', '2025-06-15'),
('Database Optimization', '2025-06-10'),
('UI Redesign', '2025-07-01');

SELECT
  name,
  due_date,
  CURRENT_DATE AS today,
  due_date - CURRENT_DATE AS days_remaining,
  CASE 
    WHEN due_date < CURRENT_DATE THEN 'Overdue'
    WHEN due_date - CURRENT_DATE <= 3 THEN 'Due Soon'
    ELSE 'On Track'
  END AS status,
  AGE(CURRENT_DATE, created_at) AS project_age
FROM projects
ORDER BY due_date;

Best Practices

1. Choose the right data type:
   • DATE for dates without time
   • TIMESTAMP for local date/time
   • TIMESTAMPTZ for timezone-aware data
2. Use DATE_TRUNC for grouping: Group by day, month, or year efficiently
3. Format only for display: Use TO_CHAR in SELECT, not WHERE clauses
4. Be timezone-aware: Always consider timezone implications in global applications
5. Index date columns: Create indexes on frequently queried date columns