Imagine a university database that stores student admission dates, fee payment dates, examination dates, and attendance records.
The university may need to perform different tasks:
PostgreSQL provides Date and Time Functions to perform these operations.
Before learning date and time functions, understand the commonly used date and time data types.
| Data Type | Description | Example |
|---|---|---|
DATE |
Stores date only | 2026-11-09 |
TIME |
Stores time only | 14:30:00 |
TIMESTAMP |
Stores date and time without time zone | 2026-11-09 14:30:00 |
TIMESTAMPTZ |
Stores date and time with time zone handling | 2026-11-09 14:30:00+05 |
INTERVAL |
Stores a duration or time period | 2 days |
SELECT DATE '2026-11-09';
SELECT TIME '14:30:00';
SELECT TIMESTAMP '2026-11-09 14:30:00';
SELECT INTERVAL '5 days';
The CURRENT_DATE keyword returns the current date according to the database session’s date and time settings.
SELECT CURRENT_DATE;
Example output:
2026-11-09
The actual output depends on the date when the query is executed.
The CURRENT_TIME keyword returns the current time with time zone information.
SELECT CURRENT_TIME;
Example output:
16:10:25.123+05
The actual output changes whenever the query is executed.
PostgreSQL provides different ways to retrieve the current date and time.
NOW() returns the current transaction’s starting date and time, including time zone information.
SELECT NOW();
SELECT CURRENT_TIMESTAMP;
This also returns the current transaction’s starting timestamp with time zone information.
SELECT LOCALTIMESTAMP;
Returns the current transaction’s starting date and time without time zone information.
| Function | Returns |
|---|---|
CURRENT_DATE |
Current date |
CURRENT_TIME |
Current time with time zone |
NOW() |
Current transaction timestamp with time zone |
CURRENT_TIMESTAMP |
Current transaction timestamp with time zone |
LOCALTIMESTAMP |
Current transaction timestamp without time zone |
Important: NOW() and CURRENT_TIMESTAMP do not continuously change during a transaction. They return the transaction’s starting time.
We will use a students table to understand date functions.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department VARCHAR(50),
admission_date DATE,
date_of_birth DATE
);
INSERT INTO students
VALUES
(1, 'Ali', 'Computer Science', '2024-09-10', '2005-03-15'),
(2, 'Sara', 'Mathematics', '2023-08-15', '2004-07-20'),
(3, 'Ahmed', 'Physics', '2025-01-20', '2006-11-05'),
(4, 'Hina', 'Computer Science', '2024-02-12', '2005-12-25'),
(5, 'Usman', 'Mathematics', '2025-09-01', '2006-05-10');
SELECT *
FROM students;
Expected output:
| student_id | student_name | department | admission_date | date_of_birth |
|---|---|---|---|---|
| 1 | Ali | Computer Science | 2024-09-10 | 2005-03-15 |
| 2 | Sara | Mathematics | 2023-08-15 | 2004-07-20 |
| 3 | Ahmed | Physics | 2025-01-20 | 2006-11-05 |
| 4 | Hina | Computer Science | 2024-02-12 | 2005-12-25 |
| 5 | Usman | Mathematics | 2025-09-01 | 2006-05-10 |
The EXTRACT() function retrieves a specific part of a date or timestamp.
For example:
EXTRACT(field FROM source);
SELECT
student_name,
admission_date,
EXTRACT(YEAR FROM admission_date) AS admission_year
FROM students;
SELECT
student_name,
admission_date,
EXTRACT(MONTH FROM admission_date) AS admission_month
FROM students;
SELECT
student_name,
admission_date,
EXTRACT(DAY FROM admission_date) AS admission_day
FROM students;
| Field | Description |
|---|---|
YEAR |
Extracts year |
MONTH |
Extracts month |
DAY |
Extracts day of month |
HOUR |
Extracts hour |
MINUTE |
Extracts minute |
SECOND |
Extracts second |
QUARTER |
Extracts quarter of year |
DOW |
Day of week (Sunday = 0) |
ISODOW |
Day of week (Monday = 1) |
DOY |
Day of year |
WEEK |
ISO week number |
Example:
SELECT
EXTRACT(YEAR FROM CURRENT_DATE) AS current_year,
EXTRACT(MONTH FROM CURRENT_DATE) AS current_month,
EXTRACT(DAY FROM CURRENT_DATE) AS current_day;
DATE_PART() also extracts a particular part of a date or timestamp.
DATE_PART('field', source);
SELECT
student_name,
DATE_PART('year', admission_date) AS admission_year
FROM students;
SELECT
student_name,
DATE_PART('month', admission_date) AS admission_month
FROM students;
Difference: EXTRACT() uses SQL-standard syntax, while DATE_PART() uses a function with a text field argument. Both can be used to extract date and time components.
The AGE() function calculates the difference between two dates or timestamps as an interval.
It is useful for calculating a person’s age or the duration between two dates.
AGE(end_date, start_date);
SELECT
student_name,
date_of_birth,
AGE(CURRENT_DATE, date_of_birth) AS student_age
FROM students;
The result is displayed in years, months, and days.
SELECT AGE(
DATE '2026-11-09',
DATE '2020-01-01'
);
Example result:
6 years 10 mons 8 days
Note: The result is an interval, not a simple number of days.
We can subtract one DATE value from another to calculate the number of days between them.
date1 - date2;
SELECT
DATE '2026-11-09' - DATE '2026-11-01' AS total_days;
Expected output:
| total_days |
|---|
| 8 |
SELECT
student_name,
admission_date,
CURRENT_DATE - admission_date AS days_since_admission
FROM students;
This calculates the number of days between each student’s admission date and the current date.
We can add a number of days to a date using the + operator.
SELECT DATE '2026-11-09' + 10 AS new_date;
Expected output:
2026-11-19
SELECT
student_name,
admission_date,
admission_date + 30 AS next_date
FROM students;
This calculates the date 30 days after each student’s admission date.
We can subtract days using the - operator.
SELECT DATE '2026-11-09' - 5 AS previous_date;
Expected output:
2026-11-04
SELECT
student_name,
admission_date,
admission_date - 7 AS previous_date
FROM students;
This calculates the date seven days before each student’s admission date.
PostgreSQL uses INTERVAL to add or subtract periods of time.
SELECT
DATE '2026-11-09' + INTERVAL '2 months' AS new_date;
SELECT
DATE '2026-11-09' + INTERVAL '1 year' AS new_date;
SELECT
DATE '2026-11-09' - INTERVAL '3 months' AS previous_date;
| Interval | Meaning |
|---|---|
'5 days' |
Five days |
'2 weeks' |
Two weeks |
'3 months' |
Three months |
'1 year' |
One year |
'4 hours' |
Four hours |
'30 minutes' |
Thirty minutes |
'15 seconds' |
Fifteen seconds |
The TO_CHAR() function converts a date or timestamp into formatted text.
It is useful when we want to display dates in a particular format.
TO_CHAR(date_value, 'format');
SELECT
student_name,
TO_CHAR(admission_date, 'DD-MM-YYYY') AS formatted_date
FROM students;
Example output:
| student_name | formatted_date |
|---|---|
| Ali | 10-09-2024 |
| Sara | 15-08-2023 |
| Ahmed | 20-01-2025 |
SELECT
student_name,
TO_CHAR(admission_date, 'DD Month YYYY') AS formatted_date
FROM students;
| Format | Meaning | Example |
|---|---|---|
DD |
Day | 09 |
MM |
Month number | 11 |
YYYY |
Four-digit year | 2026 |
YY |
Two-digit year | 26 |
Month |
Full month name, padded | November |
Mon |
Short month name | Nov |
Day |
Full weekday name, padded | Monday |
Dy |
Short weekday name | Mon |
HH24 |
Hour in 24-hour format | 16 |
HH12 |
Hour in 12-hour format | 04 |
MI |
Minute | 30 |
SS |
Second | 25 |
The TO_DATE() function converts a text value into a DATE value using a specified format.
TO_DATE(text_value, 'format');
SELECT TO_DATE('09-11-2026', 'DD-MM-YYYY');
Expected output:
2026-11-09
SELECT TO_DATE('15/08/2025', 'DD/MM/YYYY');
Expected output:
2025-08-15
Important: TO_DATE() is used for converting text into dates. It does not simply change the display format of an existing DATE value.
The TO_TIMESTAMP() function converts text into a timestamp with time zone information.
TO_TIMESTAMP(text_value, 'format');
SELECT TO_TIMESTAMP(
'09-11-2026 16:30:00',
'DD-MM-YYYY HH24:MI:SS'
);
This converts the supplied text into a timestamp value.
DATE_TRUNC() removes smaller parts of a timestamp and keeps the specified date or time level.
It is useful when grouping or reporting data by year, month, or day.
DATE_TRUNC('field', timestamp_value);
SELECT DATE_TRUNC(
'year',
TIMESTAMP '2026-11-09 16:30:45'
);
Result:
2026-01-01 00:00:00
SELECT DATE_TRUNC(
'month',
TIMESTAMP '2026-11-09 16:30:45'
);
Result:
2026-11-01 00:00:00
SELECT DATE_TRUNC(
'day',
TIMESTAMP '2026-11-09 16:30:45'
);
Result:
2026-11-09 00:00:00
| Field | Purpose |
|---|---|
year |
Beginning of year |
quarter |
Beginning of quarter |
month |
Beginning of month |
week |
Beginning of week |
day |
Beginning of day |
hour |
Beginning of hour |
minute |
Beginning of minute |
second |
Beginning of second |
We can use date functions with the WHERE clause to filter records.
SELECT *
FROM students
WHERE EXTRACT(YEAR FROM admission_date) = 2024;
SELECT *
FROM students
WHERE EXTRACT(MONTH FROM admission_date) = 9;
SELECT *
FROM students
WHERE date_of_birth >= DATE '2006-01-01';
Performance tip: For filtering a date range, direct comparisons are often more efficient than applying a function to the date column.
For example, to find admissions during 2024:
SELECT *
FROM students
WHERE admission_date >= DATE '2024-01-01'
AND admission_date < DATE '2025-01-01';
PostgreSQL provides GENERATE_SERIES() to generate a sequence of values.
We can use it to generate a list of dates.
SELECT GENERATE_SERIES(
DATE '2026-11-01',
DATE '2026-11-07',
INTERVAL '1 day'
)::DATE AS calendar_date;
Expected output:
| calendar_date |
|---|
| 2026-11-01 |
| 2026-11-02 |
| 2026-11-03 |
| 2026-11-04 |
| 2026-11-05 |
| 2026-11-06 |
| 2026-11-07 |
This function is useful for creating calendars, attendance reports, and date-based reports.
We can combine date functions with GROUP BY to prepare reports.
Suppose we want to count students admitted in each year.
SELECT
EXTRACT(YEAR FROM admission_date) AS admission_year,
COUNT(*) AS total_students
FROM students
GROUP BY EXTRACT(YEAR FROM admission_date)
ORDER BY admission_year;
Similarly, we can calculate monthly admissions:
SELECT
EXTRACT(YEAR FROM admission_date) AS admission_year,
EXTRACT(MONTH FROM admission_date) AS admission_month,
COUNT(*) AS total_students
FROM students
GROUP BY
EXTRACT(YEAR FROM admission_date),
EXTRACT(MONTH FROM admission_date)
ORDER BY admission_year, admission_month;
DATE stores date values.TIME stores time values.TIMESTAMP stores date and time without time zone.TIMESTAMPTZ stores an instant with time zone handling.CURRENT_DATE returns the current date.NOW() returns the current transaction timestamp.EXTRACT() retrieves a specific part of a date or timestamp.AGE() calculates the difference between dates as an interval.INTERVAL represents a duration.TO_CHAR() formats a date or timestamp as text.TO_DATE() converts text into a date.TO_TIMESTAMP() converts text into a timestamp.DATE_TRUNC() reduces a date or timestamp to a specified level.GENERATE_SERIES() can generate a sequence of dates.AGE().'25/12/2026' into a DATE value.| Function / Keyword | Purpose |
|---|---|
CURRENT_DATE |
Returns current date |
CURRENT_TIME |
Returns current time |
NOW() |
Returns current transaction timestamp |
CURRENT_TIMESTAMP |
Returns current transaction timestamp |
LOCALTIMESTAMP |
Returns timestamp without time zone |
EXTRACT() |
Extracts date/time components |
DATE_PART() |
Extracts date/time components |
AGE() |
Calculates date difference as an interval |
INTERVAL |
Represents a duration |
TO_CHAR() |
Converts date/time into formatted text |
TO_DATE() |
Converts text into DATE |
TO_TIMESTAMP() |
Converts text into TIMESTAMPTZ |
DATE_TRUNC() |
Truncates date/time to a specified level |
GENERATE_SERIES() |
Generates a sequence of values |
Next Topic: String Functions in PostgreSQL
Students will learn how to manipulate text using UPPER(), LOWER(), LENGTH(), CONCAT(), SUBSTRING(), TRIM(), REPLACE(), and other useful string functions.