PL SQL Datetime Format: Date and Time Functions In PL/SQL

Learn about PL SQL Datetime Format and some useful functions around Datetime, Timestamp, and Interval:

In the PL/SQL Triggers in PL SQL series, we learned about their types, usage and advantages.

In this article, we will explore the date and time in PL/SQL and some of the functions on Datetime, Timestamp, and Interval data types. Also, we shall do some basic operations on Datetime and Interval.

Let’s begin with the discussion!!

PL SQL Datetime Format

PL/SQL has a date/time datatype that allows us to hold and calculate dates, intervals, and times. The variable that is of type date or time contains a value called DateTime. The variable that keeps the interval data type is called the interval. Each of these data types has fields that set the value.

The DateTime data types are listed below:

1. TIMESTAMP
2. TIMESTAMP WITH TIME ZONE
3. TIMESTAMP WITH LOCAL TIME ZONE
4. DATE

The Interval data types are listed below:

1. INTERVAL DAY TO SECOND
2. INTERVAL YEAR TO MONTH

DATE

The fixed-length datetimes are stored in the data type DATE. It comprises the time of the day from midnight in seconds. The date section points to the first day of the present month and the time section points to midnight. It holds date and time information in both number and character data types.

SYSDATE is a date function that fetches the present time and date. The proper date range is from January 1, 4712 BC to December 31, 9999 AD. The character values in the default format (determined by Oracle initialization parameter NLS_DATE_FORMAT) are converted naturally by PL/SQL to DATE values.

We can apply mathematical operations like addition and subtraction on dates. PL/SQL interprets integer literals in the form of days. For example, SYSDATE + 1 points to tomorrow.

TIMESTAMP

The timestamp data type is an extension of the DATE data type. It is used to hold the year, month, hour, and second. The default timestamp format is determined by the Oracle initialization parameter NLS_TIMESTAMP_FORMAT.

Syntax:

TIMESTAMP[(precision)]

Here, the precision is not a mandatory parameter and points to the count of the number of digits which is in the fractional part of the seconds field. The precision should be any integer literal from 0 to 9. The default value is set to 6.

TIMESTAMP WITH TIME ZONE

This data type is an extension of the TIMESTAMP data type and contains time zone displacement. The time zone displacement is the time difference (in hours and minutes) between local time and Coordinated Universal Time (UTC).

The default timestamp with time zone format is determined by Oracle initialization parameter NLS_TIMESTAMP_TZ_FORMAT. Syntax:

TIMESTAMP[(precision)] WITH TIME ZONE

Here the precision is not a mandatory parameter and points to the count of the number of digits which is in the fractional part of the seconds field. The precision should be any integer literal from 0 to 9. The default value is set to 6.

We can mention the time zone with symbols. It can be of long-form like ‘US/Pacific’ or in short like ‘PDT’ or a combination of both. Thus this data type is used for covering and computing information across geographic locations.

TIMESTAMP WITH LOCAL TIME ZONE

Timestamp with local time zone data type is an extension of the TIMESTAMP data type and contains time zone displacement. The time zone displacement is the time difference (in hours and minutes) between local time and Coordinated Universal Time (UTC).

Syntax:

TIMESTAMP [(precision)] WITH LOCAL TIME ZONE

Here, the precision is not a mandatory parameter and points to the count of the number of digits which is in the fractional part of the second’s field. The precision should be any integer literal from 0 to 9. The default value is set to 6.

TIMESTAMP WITH LOCAL TIME ZONE is different from TIMESTAMP WITH TIME ZONE due to the fact that while we insert value to the database, the value is set to the time zone of the database and the time zone displacement is not held in the database column. However, on fetching the value, it is returned in the local time zone session.

INTERVAL YEAR TO MONTH

This data type is used to store and compute years and months interval.

Syntax:

INTERVAL YEAR [(precision)] TO MONTH

Here, the precision is the count of the number of digits in a year’s field. The precision should be any integer literal from 0 to 4. The default value is set to 2.

INTERVAL YEAR TO SECOND

Interval year to second data type is used to store and compute days, hours, minutes, and seconds intervals.

Syntax:

INTERVAL DAY [(l_precision)] TO SECOND [(fractional_s_precision)]

Here, the l_precision and fractional_s_precision are the count of the number of digits in the days and seconds field, respectively.

The precision should be any integer literal from 0 to 9. The default values are set to 2 and 6 respectively.

Field Values: Datetime And Interval

• SECOND: The values for valid DateTime range are from 00 to 59.9(m) where m denotes the time-fractional seconds. The values for the valid interval range are from 00 to 59.9(m) where m denotes the interval fractional seconds.
• MINUTE: The values for valid DateTime range are from 00 to 59. The values for the valid interval range are from 0 to 59.
• HOUR: The values for valid DateTime range are from 00 to 23. The values for the valid interval range are from 0 to 23.
• DAY: The values for valid DateTime range are from 01 to 31 (limited by the values of YEAR and MONTH, as per the rules of the locale calendar). The value for a valid interval range is any non-zero integer.
• MONTH: The values for valid DateTime range are from 01 to 12. The values for the valid interval range are from 0 to 11.
• YEAR: The values for valid DateTime range are from -4712 to 9999 not including year 0. The value for the valid interval range is any non-zero integer.
• TIMEZONE_HOUR: The values for valid DateTime range are from -12 to 14, it includes daylight saving time changes. This is not applicable to the valid interval range.
• TIMEZONE_MINUTE: The values for valid DateTime range are from 00 to 59. This is not applicable to the valid interval range.
• TIMEZONE_REGION: The values for valid DateTime range are not applicable for DATE or TIMESTAMP. This is not applicable for a valid interval range.
• TIMEZONE_ABBR: The values for valid DateTime range are not applicable for DATE or TIMESTAMP. This is not applicable to the valid interval range.

PL SQL Functions In Datetime

Here, m and n contain the values of the datetime.

PL SQL Functions in Timestamp

Here, m contains the value of the timestamp.

Code Implementation with Datetime and Timestamp functions:

BEGIN
  /* get the current date and time */ 
  DBMS_OUTPUT.put_line ('The output of SYSDATE is:'|| SYSDATE);
  
  /* get the TIMESTAMP WITH TIME ZONE with database time and time zone */ 
  DBMS_OUTPUT.put_line 
('The output of SYSTIMESTAMP is:' ||SYSTIMESTAMP);

/* get the TIMESTAMP with local time in session time zone */
  DBMS_OUTPUT.put_line
 ('The output of LOCALTIMESTAMP:' ||LOCALTIMESTAMP);
  
 /*get the TIMESTAMP WITH TIME ZONE with present session time with session time zone */ 
  DBMS_OUTPUT.put_line
 ('The output of CURRENT_TIMESTAMP:'||CURRENT_TIMESTAMP);
  
  /*convert string to TIMESTAMP*/ 
  DBMS_OUTPUT.put_line
 ('The output of TIMESTAMP:'||TO_TIMESTAMP('12-JAN-2011'));
  
  /*to add months*/ 
  DBMS_OUTPUT.put_line
('The output of ADD_MONTHS:'||ADD_MONTHS(SYSDATE,1));
  
  /*to get date and time of following day*/ 
   DBMS_OUTPUT.put_line
 ('The output of NEXT_DAY:'||NEXT_DAY(SYSDATE,'MONDAY'));
   
   /*to truncate date */ 
   DBMS_OUTPUT.put_line ('The output of TRUNC:'||TRUNC(SYSDATE));
   /*to get the last day of month */
   DBMS_OUTPUT.put_line 
('The output of LAST_DAY:'||LAST_DAY (SYSDATE));
   
END;
/

Output of the above code:

PL SQL Functions in Interval

Arithmetic Operations In Datetime And Interval

PL/SQL allows you to create DateTime and interval expressions.

The list of operators that can be applied are:

• If the first operand is DateTime and the second operand is an interval, and we want to apply the (+) operator on them, the result value is of DateTime type.
• If the first operand is DateTime and the second operand is an interval, and we want to apply the (-) operator on them, the result value is of DateTime type.
• If the first operand is interval and the second operand is DateTime, and we want to apply the (+) operator on them, the result value is of DateTime type.
• If the first operand is DateTime and the second operand is DateTime, and we want to apply the (-) operator on them, the result value is of interval type.
• If the first operand is interval and the second operand is an interval, and we want to apply the (+) operator on them, the result value is of interval type.
• If the first operand is interval and the second operand is an interval, and we want to apply the (-) operator on them, the result value is of interval type.
• If the first operand is interval and the second operand is numeric, and we want to apply the (*) operator on them, the result value is of interval type.
• If the first operand is numeric and the second operand is an interval, and we want to apply the (*) operator on them, the result value is of interval type.
• If the first operand is interval and the second operand is numeric, and we want to apply the (/) operator on them, the result value is of interval type.

Code Implementation with some arithmetic operations in Datetime and Interval.

DECLARE
   c_dt TIMESTAMP;
   r_dt TIMESTAMP;
   s_dt TIMESTAMP;
   
BEGIN
   c_dt := SYSTIMESTAMP;
   r_dt:= c_dt + TO_DSINTERVAL ('1600 5:20:1');
   DBMS_OUTPUT.PUT_LINE ('Addition of datetime and Interval:' ||r_dt);
   s_dt:= c_dt - TO_DSINTERVAL ('1600 5:20:1');
   DBMS_OUTPUT.PUT_LINE ('Subtraction of datetime and Interval:' || s_dt);
END;

The output of the above code:

Explanation of the above code:

• In the code, (‘1600 5:20:1’) means 1600 days, 5 hours, 20 minutes and 1 second.
• In the first output, the first operand is DateTime, and the second operand is an interval. On adding them we got a date as 24-DEC with time in AM.
• In the second output, the first operand is DateTime and the second operand is an interval. On subtracting first from the second we got a date as 20-MAR with time in PM.

Frequently Asked Questions And Answers

Q #1) What is the current timestamp?

Answer: The current timestamp or CURRENT_TIMESTAMP describes a timestamp which is dependent on the reading of the time of the day clock while executing a SQL statement in the server.

Q #2) What does Sysdate return in Oracle?

Answer: The Sysdate () function fetches the current date and time configured in the operating system where the database is located. The data type of value which is returned by it is DATE.

Q #3) Which PL/SQL function will give the current system date and time?

Answer: The PL/SQL function that gives the current system date and time is SYSDATE ().

Q #4) What is DUAL SQL?

Answer: DUAL is a database table created by Oracle by default along with a data dictionary. It contains one row and one column. DUAL is owned by SYS but can be used by all the users.

Q #5) How do you declare a date variable in PL SQL?

Answer: We can declare a date variable in PL/SQL with the syntax given below:

DECLARE stdt DATE := to_date ('06/06/2006', 'DD/MM/YYYY');

Q #6) What is the date format in Oracle?

Answer: The standard date format in Oracle for input and output is ‘DD/MON/YY’. This is configured by the value in the parameter NLS_DATE_FORMAT.

Conclusion

In this PL SQL Datetime Format tutorial, we have discussed in detail some basic concepts of PL/SQL Date and Time that are essential for using them in programming.

We have covered the following topics listed below:

• Date and Time.
• Functions around Datetime, Timestamp, and Interval.
• Arithmetic operations on Datetime and Interval.
• Fields values in Datetime and Interval.

<<PREV Tutorial | NEXT Tutorial>>