PostgreSQL Date Functions

Working with dates and time is a common requirement in database applications. PostgreSQL provides a rich set of functions to manipulate, format, and perform calculations on date and time values. This guide explores these functions with practical examples to help you effectively manage temporal data in your applications.

Introduction to Date/Time Data Types

Before diving into date functions, let's understand the date-related data types in PostgreSQL:

Data Type	Description	Example
DATE	Calendar date (year, month, day)	2023-05-15
TIME	Time of day (without time zone)	15:30:45
TIMESTAMP	Date and time (without time zone)	2023-05-15 15:30:45
TIMESTAMPTZ	Date and time with time zone	2023-05-15 15:30:45+00
INTERVAL	Time period	2 days 3 hours 40 minutes

Current Date and Time Functions

PostgreSQL offers several functions to retrieve the current date and time:

sql

-- Current date
SELECT CURRENT_DATE;
-- Output: 2023-06-12

-- Current time
SELECT CURRENT_TIME;
-- Output: 14:30:45.123456+00

-- Current timestamp
SELECT CURRENT_TIMESTAMP;
-- Output: 2023-06-12 14:30:45.123456+00

-- Current timestamp without time zone info
SELECT NOW();
-- Output: 2023-06-12 14:30:45.123456+00

Date Extraction Functions

You can extract specific parts of a date using the EXTRACT() function:

sql

-- Extract year from a date
SELECT EXTRACT(YEAR FROM DATE '2023-06-12');
-- Output: 2023

-- Extract month from a date
SELECT EXTRACT(MONTH FROM TIMESTAMP '2023-06-12 14:30:45');
-- Output: 6

-- Extract day from a date
SELECT EXTRACT(DAY FROM TIMESTAMP '2023-06-12 14:30:45');
-- Output: 12

-- Extract hour from a timestamp
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-06-12 14:30:45');
-- Output: 14

PostgreSQL also provides the DATE_PART() function, which works similarly:

sql

SELECT DATE_PART('month', TIMESTAMP '2023-06-12 14:30:45');
-- Output: 6

Date Formatting Functions

The TO_CHAR() function allows you to format dates according to your needs:

sql

-- Format date as 'Month DD, YYYY'
SELECT TO_CHAR(DATE '2023-06-12', 'Month DD, YYYY');
-- Output: June 12, 2023

-- Format timestamp with custom format
SELECT TO_CHAR(TIMESTAMP '2023-06-12 14:30:45', 'YYYY-MM-DD HH24:MI:SS');
-- Output: 2023-06-12 14:30:45

-- Format with day name
SELECT TO_CHAR(DATE '2023-06-12', 'Day');
-- Output: Monday

Common format patterns for TO_CHAR():

Pattern	Description	Example
YYYY	4-digit year	2023
MM	2-digit month	06
DD	2-digit day	12
HH24	Hour (24-hour)	14
HH	Hour (12-hour)	02
MI	Minutes	30
SS	Seconds	45
Month	Full month name	June
Mon	Abbreviated month	Jun
Day	Full day name	Monday
Dy	Abbreviated day	Mon

Date Arithmetic

PostgreSQL makes it easy to perform date calculations:

sql

-- Add days to a date
SELECT DATE '2023-06-12' + 5;
-- Output: 2023-06-17

-- Subtract days from a date
SELECT DATE '2023-06-12' - 5;
-- Output: 2023-06-07

-- Add an interval to a timestamp
SELECT TIMESTAMP '2023-06-12 14:30:45' + INTERVAL '2 hours 30 minutes';
-- Output: 2023-06-12 17:00:45

-- Calculate difference between dates (returns integer days)
SELECT DATE '2023-06-12' - DATE '2023-06-01';
-- Output: 11

-- Calculate age between timestamps
SELECT AGE(TIMESTAMP '2023-06-12', TIMESTAMP '2023-01-15');
-- Output: 4 mons 28 days 00:00:00

Date Comparison Functions

Compare dates and find the earliest or latest date:

sql

-- Find the earliest date
SELECT LEAST(DATE '2023-06-12', DATE '2023-01-15', DATE '2023-09-22');
-- Output: 2023-01-15

-- Find the latest date
SELECT GREATEST(DATE '2023-06-12', DATE '2023-01-15', DATE '2023-09-22');
-- Output: 2023-09-22

Date Truncation Functions

The DATE_TRUNC() function truncates a timestamp to a specified precision:

sql

-- Truncate to month (first day of the month)
SELECT DATE_TRUNC('month', TIMESTAMP '2023-06-12 14:30:45');
-- Output: 2023-06-01 00:00:00

-- Truncate to year (first day of the year)
SELECT DATE_TRUNC('year', TIMESTAMP '2023-06-12 14:30:45');
-- Output: 2023-01-01 00:00:00

-- Truncate to day (midnight)
SELECT DATE_TRUNC('day', TIMESTAMP '2023-06-12 14:30:45');
-- Output: 2023-06-12 00:00:00

-- Truncate to hour
SELECT DATE_TRUNC('hour', TIMESTAMP '2023-06-12 14:30:45');
-- Output: 2023-06-12 14:00:00

Working with Time Zones

PostgreSQL handles time zones effectively:

sql

-- Convert timestamp to a different time zone
SELECT TIMESTAMP WITH TIME ZONE '2023-06-12 14:30:45 UTC' AT TIME ZONE 'America/New_York';
-- Output: 2023-06-12 10:30:45

-- Get the current timestamp in a specific time zone
SELECT NOW() AT TIME ZONE 'Europe/Paris';
-- Output: 2023-06-12 16:30:45

Practical Examples

Example 1: Calculate User Account Age

Imagine we have a users table with registration dates. Let's calculate how long each user has been registered:

sql

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  registered_at DATE NOT NULL
);

INSERT INTO users (username, registered_at) VALUES
  ('john_doe', '2022-01-15'),
  ('jane_smith', '2022-06-22'),
  ('bob_williams', '2023-02-03');

-- Calculate account age in days
SELECT 
  username, 
  registered_at, 
  CURRENT_DATE - registered_at AS days_registered,
  AGE(CURRENT_DATE, registered_at) AS account_age
FROM users;

/* Output (if CURRENT_DATE is 2023-06-12):
 username    | registered_at | days_registered | account_age
-------------+---------------+----------------+-------------
 john_doe    | 2022-01-15    | 513            | 1 year 4 mons 28 days
 jane_smith  | 2022-06-22    | 355            | 11 mons 21 days
 bob_williams | 2023-02-03    | 129            | 4 mons 9 days
*/

Example 2: Generate Date Ranges

Generate a series of dates for reporting purposes:

sql

-- Generate dates for the current month
SELECT 
  generate_series(
    DATE_TRUNC('month', CURRENT_DATE)::DATE,
    (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month - 1 day')::DATE,
    '1 day'
  ) AS date;

/* Output (if CURRENT_DATE is in June 2023):
    date
------------
 2023-06-01
 2023-06-02
 ...
 2023-06-30
*/

Example 3: Group Sales by Month

Analyze monthly sales data:

sql

CREATE TABLE sales (
  sale_id SERIAL PRIMARY KEY,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);

INSERT INTO sales (sale_date, amount) VALUES
  ('2023-01-05', 120.50),
  ('2023-01-15', 65.75),
  ('2023-02-03', 45.00),
  ('2023-02-20', 189.99),
  ('2023-03-07', 250.50);

-- Group sales by month
SELECT 
  DATE_TRUNC('month', sale_date)::DATE AS month,
  TO_CHAR(sale_date, 'Month YYYY') AS month_name,
  COUNT(*) AS sales_count,
  SUM(amount) AS total_sales
FROM sales
GROUP BY month, month_name
ORDER BY month;

/* Output:
    month    | month_name   | sales_count | total_sales
------------+--------------+-------------+------------
 2023-01-01 | January 2023 | 2           | 186.25
 2023-02-01 | February 2023| 2           | 234.99
 2023-03-01 | March 2023   | 1           | 250.50
*/

Summary

PostgreSQL provides powerful functions for working with dates and times that can help you:

• Retrieve the current date and time
• Extract parts of dates and times
• Format dates for display
• Perform arithmetic on dates
• Compare and truncate dates
• Work with time zones effectively

These functions are essential for tasks like tracking user activity, analyzing time-series data, generating reports, and scheduling operations in your applications.

Additional Resources

Here are some exercises to practice your PostgreSQL date function skills:

1. Write a query to find all users who registered in the last 30 days.
2. Create a query that groups orders by week and calculates weekly revenue.
3. Generate a report showing the number of events per day for the current month.
4. Write a function that calculates business days between two dates (excluding weekends).
5. Create a query that converts timestamps from UTC to your local time zone.

For more information, refer to the official PostgreSQL documentation on date and time functions.