DATE and TIME values in PL/SQL have an entire distinctive to have a proper utilization set of functions and operators. It is important to come across the date tools so that various queries deal with DATE and TIME information.
PL SQL Date time Format
PL/SQL has a date/time data type that allows us to hold and calculate dates, intervals, and times. The variable that is of type date or time contains a value called Date Time. The variable that keeps the interval data type is called the interval. Each of these data types has fields that set the value.
The Date-Time data types are as follows:
- DATE
- TIMESTAMP
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
1. DATE
The fixed-length date times 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 character values in the default format which is determined by Oracle initialization parameter NLS_DATE_FORMAT and also they 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 point to tomorrow.
2. 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. Here, the precision is not a mandatory parameter and points to the count of the number of digits that 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. Syntax: TIMESTAMP[(precision)]
3. 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 between hours and minutes within 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 that 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. 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.
4. 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 that 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 a 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.
Following are The Interval data types:
- INTERVAL DAY TO SECOND
- INTERVAL YEAR TO MONTH
1. INTERVAL YEAR TO SECOND
The Interval year to a second data type is used to store and compute days, hours, minutes, and second’s 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.
2. INTERVAL YEAR TO MONTH
This data type is used to store and compute years and months intervals. Syntax: INTERVAL YEAR [(precision)] TO MONTH
Here, 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.
PL SQL Functions in Datetime
Below are the functions and purpose of each value. Here, m and n contain the values of the DateTime.
- LAST_DAY (m) = Fetches the last day of the month.
- ADD_MONTHS (m,n) = Sums up m and n months.
- MONTHS_BETWEEN (m,n) = Fetches the count of the number of months in between m and n.
- NEXT_DAY (m, day) = Fetches the DateTime of the following day after m.
- NEXT_TIME = Fetches the time/day from the user’s requested time zone.
- ROUND (m[,unit]) = Rounds m.
- SYSDATE () = Fetches the present datetime.
- TRUNC (m[,unit]) = Truncates m.
PL SQL Functions in Timestamp
Following are the functions and their respective purposes. Here, m contains the value of the timestamp.
- CURRENT_TIMESTAMP () = Fetches a TIMESTAMP WITH TIMEZONE having the present session and session time zone.
- FROM_TZ (m, time_zone) = Converts the m TIMESTAMP and mentions time_zone to TIMESTAMP WITH TIMEZONE.
- LOCALTIMESTAMP () = Fetches a TIMESTAMP having the local time in the session time zone.
- SYSTEMTIMESTAMP () = Fetches a TIMESTAMP WITH TIMEZONE having the present database time and database time zone.
- SYS_EXTRACT_UTC (m) = Converts the m TIMESTAMP WITH TIMEZONE to TIMESTAMP having date and time in UTC.
- TO_TIMESTAMP (m,[format]) = Converts the string m to a TIMESTAMP.
- TO_TIMESTAMP_TZ (m,[format]) = Converts the string m to a TIMESTAMP WITH TIMEZONE.
Conclusion
We have discussed few basic concepts of PL/SQL Date and Time that are essential for using them in programming. Oracle PLSQL Online Training Course at Gologica furnishes you with the total abilities expected to make, execute and oversee strong database applications utilizing the Oracle database instruments. Upgrade your skills by applying the world’s Best Online Learning Platform. Happy Learning!!
