Learn how to use the PostgreSQL INTERVAL data type to represent time durations and calculate dates and timestamps with NOW().
In PostgreSQL, INTERVAL '1 day' represents a time duration of one day (24 hours).
Let’s understand this expression:
INTERVAL '1 day' * p_days
INTERVAL is a PostgreSQL data type used to represent a period of time.
Examples:
INTERVAL '1 day'
INTERVAL '2 days'
INTERVAL '1 month'
INTERVAL '1 year'
INTERVAL '3 hours'
INTERVAL '30 minutes'
Consider this expression:
NOW() - INTERVAL '1 day' * p_days
It calculates a date and time by subtracting the specified number of days from the current date and time.
Suppose:
p_days = 7;
The expression becomes:
NOW() - INTERVAL '1 day' * 7
Which is equivalent to:
NOW() - INTERVAL '7 days'
Suppose the current date and time is:
2026-11-19 10:00:00
If p_days = 7, PostgreSQL calculates:
Current date and time
7 days
Calculated date and time
DELETE FROM rental
WHERE rental_date < NOW() - INTERVAL '1 day' * p_days;
Explanation:
NOW() gets the current date and time.INTERVAL '1 day' represents one day.* p_days multiplies one day by the specified number of days.NOW() - ... calculates the cutoff date and time.WHERE selects rentals older than the calculated cutoff.Example: If p_days = 7, the procedure deletes rentals whose rental_date is earlier than seven days ago.
If p_days = 30, what does this expression represent?
INTERVAL '1 day' * p_days
Remember: INTERVAL represents a time duration, while NOW() returns the current date and time.