CURRENT_DATE and CURRENT_TIMESTAMP in SQL - Time & Space Complexity

Time Complexity: CURRENT_DATE and CURRENT_TIMESTAMP

O(1)

Understanding Time Complexity

We want to understand how the time it takes to get the current date or timestamp changes as the database grows.

Does asking for the current date or time take longer if the database has more data?

Scenario Under Consideration

Analyze the time complexity of the following SQL queries.

SELECT CURRENT_DATE;

SELECT CURRENT_TIMESTAMP;

SELECT CURRENT_TIMESTAMP + INTERVAL '1 day';

SELECT CURRENT_DATE FROM orders WHERE order_id = 123;
    

These queries get the current date or timestamp, sometimes combined with simple operations or used in a WHERE clause.

Identify Repeating Operations

Look for any repeated work done by the database when running these queries.

• Primary operation: Fetching the current date or timestamp from the system clock.
• How many times: Once per query, no loops or repeated scans.

How Execution Grows With Input

Getting the current date or timestamp is a simple call to the system clock.

Input Size (n)	Approx. Operations
10	1
100	1
1000	1

Pattern observation: The time to get the current date or timestamp stays the same no matter how much data is in the database.

Final Time Complexity

Time Complexity: O(1)

This means the time to get the current date or timestamp does not grow with the size of the database; it stays constant.

Common Mistake

[X] Wrong: "Getting the current date or timestamp takes longer if the database has more rows."

[OK] Correct: The current date and timestamp come from the system clock, not from scanning data, so the database size does not affect the time.

Interview Connect

Understanding that some operations take constant time helps you explain how databases handle time functions efficiently, a useful skill in many real-world tasks.

Self-Check

"What if we used CURRENT_TIMESTAMP inside a query that scans millions of rows, like in a WHERE clause? How would the time complexity change?"