CURRENT_DATE

In Oracle, CURRENT_DATE is a built-in function that returns the current date and time according to the session's time zone. Unlike SYSDATE, which returns the current date and time based on the database server's system clock, CURRENT_DATE respects the time zone of the session in which it is executed.

Here are the key details:

 

1. Syntax

SELECT CURRENT_DATE FROM dual;

• dual is a special table in Oracle, commonly used when a query doesn't need data from any actual table but rather just to perform calculations or retrieve system values like CURRENT_DATE.

 

2. Return Value

• CURRENT_DATE returns a DATE data type, which contains both the date and time. By default, Oracle will show the date and time in the format DD-MON-YY HH24:MI:SS, though the exact format depends on your session's settings.

 

3. Time Zone

• The key difference between SYSDATE and CURRENT_DATE is that CURRENT_DATE reflects the session's time zone. If the session is set to a specific time zone, CURRENT_DATE will return the current date and time in that time zone, not the database server's time zone.
• For example, if the session is set to the UTC time zone, CURRENT_DATE will return the current date and time in UTC, regardless of the server's time zone.

 

4. Comparison with SYSDATE

• SYSDATE: Returns the current system date and time based on the database server’s operating system time zone.
• CURRENT_DATE: Returns the current date and time based on the session's time zone. The session’s time zone can be different from the server’s time zone.

Example:

If your session's time zone is different from the database server's time zone, CURRENT_DATE and SYSDATE may return different results:

-- Query to get SYSDATE and CURRENT_DATE

SELECT SYSDATE, CURRENT_DATE FROM dual;

• If the session time zone is different from the server time zone, the results will be different.

 

5. Practical Use Cases

Here are some practical use cases for CURRENT_DATE in SQL queries:

·        Retrieve the current date and time according to the session's time zone:

SELECT CURRENT_DATE FROM dual;

·        Calculate the difference between the current date and another date:

SELECT CURRENT_DATE - hire_date AS days_since_hire

FROM employees;

This would calculate how many days have passed since each employee was hired, based on the current session time zone.

·        Filter data based on the current date in the session's time zone:

SELECT * FROM orders

WHERE order_date >= CURRENT_DATE;

This query retrieves all orders placed today, considering the session’s current date and time.

·        Get only the current date without the time: You can extract the date part by using TRUNC:

·        SELECT TRUNC(CURRENT_DATE) FROM dual;

This will return the current date without the time component, which might be useful in some scenarios where you only need the date.

 

6. Changing the Session Time Zone

·        You can set or change the time zone for the current session using the ALTER SESSION command:

·        ALTER SESSION SET TIME_ZONE = 'UTC';

After changing the session's time zone, the result of CURRENT_DATE will reflect the new time zone.

·        You can check the current session’s time zone with the following query:

·        SELECT sessiontimezone FROM dual;

 

7. Performance Considerations

·        CURRENT_DATE is a non-deterministic function, meaning that it can return different results if the query is executed multiple times in a single session. This is because the time continuously changes.

·        Be cautious when using CURRENT_DATE in queries that are run repeatedly, especially if you are comparing it to other timestamps (e.g., a date column) within a loop or large dataset.

 

8. Example: Using CURRENT_DATE for Date Calculations

You can perform date arithmetic with CURRENT_DATE to find future or past dates:

·        Adding days: To get the date 10 days from today:

SELECT CURRENT_DATE + 10 FROM dual;

·        Subtracting days: To get the date 5 days ago:

SELECT CURRENT_DATE - 5 FROM dual;

 

9. Difference from SYSTIMESTAMP

• CURRENT_DATE returns the current date and time in the session's time zone, with the data type DATE.
• SYSTIMESTAMP returns a TIMESTAMP WITH TIME ZONE, which includes the current date, time, and the session's time zone (in addition to the TIMEZONE information). SYSTIMESTAMP is more precise if you need high-precision time data.

Example of SYSTIMESTAMP:

SELECT SYSTIMESTAMP FROM dual;

 

10. Practical Examples in Real-World Scenarios

·        Determine how long a user has been active: Suppose you are calculating how much time has passed since a user logged in:

·        SELECT username, CURRENT_DATE - last_login AS hours_since_last_login

·        FROM users;

This query calculates the difference between the current date/time and the user’s last login time, assuming last_login is a DATE field.

·        Data timestamp conversion: If your application needs to adjust timestamps based on time zones, CURRENT_DATE can help convert times accordingly, especially if you’re dealing with users in different locations.

 

11. Time Zone-Related Examples

When your session is using a different time zone than the server, CURRENT_DATE can return a different result. For example:

·        Session set to UTC:

ALTER SESSION SET TIME_ZONE = 'UTC';

SELECT CURRENT_DATE FROM dual;

·        Session set to a specific time zone (e.g., 'America/New_York'):

ALTER SESSION SET TIME_ZONE = 'America/New_York';

SELECT CURRENT_DATE FROM dual;

 

12. Key Differences Between SYSDATE, CURRENT_DATE, and SYSTIMESTAMP

Function	Description	Time Zone Consideration	Data Type
SYSDATE	Returns the current date and time of the server	Server time zone	DATE
CURRENT_DATE	Returns the current date and time of the session	Session time zone	DATE
SYSTIMESTAMP	Returns the current timestamp with time zone	Session time zone	TIMESTAMP WITH TIME ZONE

 

Summary

• CURRENT_DATE gives the current date and time in the session’s time zone, unlike SYSDATE, which uses the server’s time zone.
• It is useful in queries where you want to ensure that the time zone of your application or user session is respected.
• You can perform arithmetic, comparisons, and filtering based on the current session’s date and time, and the time zone can be changed as needed for specific use cases.

Let me know if you have more specific scenarios in mind!