PL/SQL Insert DateTimes

In PL/SQL, handling date and time values is a common requirement, whether we are working with databases that store transaction times, schedule events, or track user activities. Understanding how to insert these values correctly is crucial for accurate data management and retrieval.

In this article, we will go through the process of inserting various types of date and time values in PL/SQL, covering basic syntax, examples and output explanations.

Date and Time Data Types in PL/SQL

PL/SQL offers several data types for handling date and time values:

• DATE: It Stores the date and time up to seconds, but the time portion is optional.

• TIMESTAMP: It Extends DATE by including fractional seconds, offering more precision.

• TIMESTAMP WITH TIME ZONE: It Adds time zone information to TIMESTAMP, allowing storage of times in different time zones.

• TIMESTAMP WITH LOCAL TIME ZONE: It Stores the timestamp in the database's time zone and converts it to the user's session time zone upon retrieval.

Basic Syntax for Inserting Date and Time Values

The syntax for inserting date and time values into a PL/SQL table is as follows:

INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, TO_DATE('YYYY-MM-DD', 'YYYY-MM-DD'), ...);

Explanation:

• INSERT INTO table_name (column1, column2, ...): Specifies the target table and columns for data insertion.
  
• VALUES (value1, TO_DATE('YYYY-MM-DD', 'YYYY-MM-DD'), ...): Provides the values to insert and Uses TO_DATE to convert a string into a DATE format for date columns.

Examples of PL/SQL Insert DateTimes

for better understanding of PL/SQL Insert DateTimes we will create two sample tables for demonstration purposes such as Events and Logs. It helps us to perform various examples with output.

Creating the Events Table:

CREATE TABLE Events (
    Event_ID NUMBER PRIMARY KEY,
    Event_Name VARCHAR2(50),
    Event_Date DATE
);

Creating the Logs Table:

CREATE TABLE Logs (
    Log_ID NUMBER PRIMARY KEY,
    Log_Timestamp TIMESTAMP,
    Log_Message VARCHAR2(100)
);

Example 1: Inserting a Date Value

To insert a date value into the Events table, we use the TO_DATE function to convert a string into a DATE data type.

Query:

INSERT INTO Events (Event_ID, Event_Name, Event_Date) 
VALUES (1, 'Conference', TO_DATE('2024-09-01', 'YYYY-MM-DD'));

Output:

Explanation:

• A new record with Event_ID 1 is created.

• The Event_Name is 'Conference'.

• The Event_Date is '01-SEP-24'.

Example 2: Inserting a Timestamp

For inserting a timestamp with fractional seconds into the Logs table, the TO_TIMESTAMP function is used.

Query:

INSERT INTO Logs (Log_ID, Log_Timestamp, Log_Message) 
VALUES (1, TO_TIMESTAMP('2024-09-01 14:30:45.123456', 'YYYY-MM-DD HH24:MI:SS.FF6'), 'System Started');

Output:

Explanation:

• A new record with Log_ID 1 is created.

• The Log_Timestamp includes the date and time with fractional seconds.

• The Log_Message is 'System Started'.

Example 3: Inserting a Timestamp with Time Zone

To store a timestamp with a time zone, TO_TIMESTAMP_TZ is used, which allows including the time zone information.

Query:

INSERT INTO Logs (Log_ID, Log_Timestamp, Log_Message) 
VALUES (2, TO_TIMESTAMP_TZ('2024-09-01 14:30:45.123456 +05:30', 'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM'), 'User Login');

Output:

Explanation:

• A new record with Log_ID 2 is created.

• The Log_Timestamp includes the time zone offset.

• The Log_Message is 'User Login'.

Example 4: Inserting the Current Date and Time

To insert the current date and time into a table, the SYSDATE and SYSTIMESTAMP functions are used. SYSDATE returns the current date and time, while SYSTIMESTAMP returns the current timestamp with fractional seconds.

Query:

INSERT INTO Events (Event_ID, Event_Name, Event_Date) 
VALUES (2, 'Meeting', SYSDATE);

INSERT INTO Logs (Log_ID, Log_Timestamp, Log_Message) 
VALUES (3, SYSTIMESTAMP, 'System Check');

Output Table for Events:

Output Table for Logs:

Explanation:

• A new record with Event_ID 2 is created with the current date.

• A new record with Log_ID 3 is created with the current timestamp.

• The Log_Message is 'System Check'.

Conclusion

Inserting date and time values in PL/SQL is essential for accurately storing and retrieving temporal data. By understanding the various data types and using the correct syntax, you can ensure your data is stored correctly, whether it's a simple date, a precise timestamp, or a timestamp with a time zone. These examples provide a solid foundation for working with date and time values in your PL/SQL applications.