CURRENT_TIMESTAMP

In Oracle, the CURRENT_TIMESTAMP function is used to retrieve the current date and time from the system, including the time zone information, in the session's time zone. It returns a TIMESTAMP WITH TIME ZONE data type, which is different from the DATE type returned by SYSDATE or CURRENT_DATE. The key distinction is that CURRENT_TIMESTAMP includes not only the date and time but also the time zone of the session.

 

1. Syntax

SELECT CURRENT_TIMESTAMP FROM dual;

• dual is a special table in Oracle that is used when you need to select a value without needing data from any specific table (e.g., selecting system values like CURRENT_TIMESTAMP).

 

2. Return Value

• CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE data type, which includes:
• Date and Time (down to fractional seconds).
• Time Zone Offset (in hours and minutes from UTC).

Example format:

YYYY-MM-DD HH24:MI:SS.FF TZH:TZM

Where:

• YYYY-MM-DD represents the date.
• HH24:MI:SS.FF represents the time, with fractional seconds.
• TZH:TZM represents the time zone offset in hours and minutes (e.g., +02:00).

 

3. Time Zone

• CURRENT_TIMESTAMP uses the session's time zone to calculate the date and time.
• If the session’s time zone is different from the database server's system time zone, the value of CURRENT_TIMESTAMP will reflect the session's time zone.

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

SELECT sessiontimezone FROM dual;

If you want to change the time zone of the current session, you can do it with:

ALTER SESSION SET TIME_ZONE = 'UTC';  -- Change to UTC time zone

 

4. Comparison with Other Date and Time Functions

SYSDATE vs CURRENT_TIMESTAMP

• SYSDATE returns the current date and time based on the server's system clock without any time zone information. It returns a DATE data type, which includes only the date and time (without fractional seconds or time zone).
• CURRENT_TIMESTAMP returns the current date and time based on the session's time zone with time zone offset, returned as a TIMESTAMP WITH TIME ZONE.

Example:

SELECT SYSDATE, CURRENT_TIMESTAMP FROM dual;

If the session is in a different time zone from the server, SYSDATE and CURRENT_TIMESTAMP might give different results.

CURRENT_DATE vs CURRENT_TIMESTAMP

• CURRENT_DATE returns the current date and time based on the session's time zone but only as a DATE type. It does not include the time zone offset or fractional seconds.
• CURRENT_TIMESTAMP includes both the time zone information and fractional seconds in the TIMESTAMP WITH TIME ZONE format.

 

5. Practical Use Cases

Here are some practical use cases for CURRENT_TIMESTAMP:

Retrieve Current Date and Time with Time Zone Information

SELECT CURRENT_TIMESTAMP FROM dual;

This query will return the current date and time along with the session’s time zone offset.

Store the Timestamp with Time Zone

You can use CURRENT_TIMESTAMP to store the exact timestamp of when an event or record is created, considering the time zone of the session.

INSERT INTO log_table (log_id, log_message, created_at)

VALUES (log_seq.NEXTVAL, 'Record Created', CURRENT_TIMESTAMP);

Compare Dates with Time Zone Information

If you have a TIMESTAMP WITH TIME ZONE column and want to compare it with the current timestamp, use CURRENT_TIMESTAMP:

SELECT * FROM orders

WHERE order_timestamp > CURRENT_TIMESTAMP - INTERVAL '1' DAY;

This query retrieves orders placed in the last 24 hours based on the current time zone.

Use with Time Zone Conversion

If you need to convert a timestamp from one time zone to another, you can use CURRENT_TIMESTAMP and the FROM_TZ function:

SELECT FROM_TZ(CURRENT_TIMESTAMP, 'UTC') AT TIME ZONE 'America/New_York' FROM dual;

This query converts the current timestamp from UTC to the America/New_York time zone.

 

6. Example with Time Zone Adjustments

If your session is in a different time zone (e.g., UTC), you can adjust for that using CURRENT_TIMESTAMP. For example:

1.     Change Session Time Zone to UTC:

ALTER SESSION SET TIME_ZONE = 'UTC';

SELECT CURRENT_TIMESTAMP FROM dual;

2.     Change Session Time Zone to a Specific Time Zone:

ALTER SESSION SET TIME_ZONE = 'America/New_York';

SELECT CURRENT_TIMESTAMP FROM dual;

3.     Compare Two Time Zones: You can compare the current timestamp in two different time zones:

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC', CURRENT_TIMESTAMP AT TIME ZONE 'America/Los_Angeles' FROM dual;

 

7. Handling Fractional Seconds

CURRENT_TIMESTAMP includes fractional seconds, so you can get the exact time down to fractions of a second:

SELECT CURRENT_TIMESTAMP FROM dual;

Output:

2025-02-28 12:34:56.123456 -05:00

You can also specify the precision for fractional seconds:

SELECT CURRENT_TIMESTAMP(3) FROM dual;

This limits the fractional seconds to 3 digits (milliseconds):

2025-02-28 12:34:56.123 -05:00

 

8. Using CURRENT_TIMESTAMP in Calculations

You can perform date arithmetic with CURRENT_TIMESTAMP just like SYSDATE or CURRENT_DATE. For example:

·        Adding 5 days to the current timestamp:

SELECT CURRENT_TIMESTAMP + INTERVAL '5' DAY FROM dual;

·        Subtracting 3 hours:

SELECT CURRENT_TIMESTAMP - INTERVAL '3' HOUR FROM dual;

 

9. Time Zone and Daylight Saving Time (DST)

Since CURRENT_TIMESTAMP reflects the session's time zone, it will respect daylight saving time adjustments. For example, if you switch the session’s time zone from UTC to America/New_York, the result of CURRENT_TIMESTAMP will automatically reflect any DST changes in that time zone.

 

10. Performance Considerations

• CURRENT_TIMESTAMP is a non-deterministic function, meaning it can return different values if executed multiple times during a query or transaction.
• Time Zone Sensitivity: If your application needs to account for time zone differences, be aware that switching the session time zone will affect the result of CURRENT_TIMESTAMP.

 

11. Example of Using CURRENT_TIMESTAMP for Logging

You can use CURRENT_TIMESTAMP for precise logging of events, particularly if your application spans multiple time zones.

CREATE TABLE event_log (

    event_id NUMBER,

    event_description VARCHAR2(255),

    event_time TIMESTAMP WITH TIME ZONE

);

INSERT INTO event_log (event_id, event_description, event_time)

VALUES (1, 'User logged in', CURRENT_TIMESTAMP);

This will store the timestamp of the event in the session's time zone.

 

Summary of Key Points

• CURRENT_TIMESTAMP returns the current date and time in the session’s time zone, including the time zone offset and fractional seconds.
• It is useful when you need to record or compare timestamps with time zone awareness.
• Unlike SYSDATE and CURRENT_DATE, which return data types DATE and DATE (without time zone), CURRENT_TIMESTAMP returns TIMESTAMP WITH TIME ZONE, which includes time zone information.
• CURRENT_TIMESTAMP respects daylight saving time changes in the session’s time zone.

Let me know if you need further clarification or examples!