To the point guide to PL/SQL

In PL/SQL there are two classes of date and time its data types are:

• Datetime data types
• Interval data types

The data types of Datetime are:

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

The data types of Interval are:

• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

Datetime and Interval Data Types Field Values are:

The values datetime and interval data type fields and their possible values are:

The Functions and Datetime Data Types:

The Datetime data types are:

• DATE: To store the information of date and time in both number and as well as character datatypes, information on second, minute, hour, month, year, and century.
• TIMESTAMP: It is an extension of the datatype DATE which is used to store the day, month and year of the datatype DATE, with seconds, minute and hour values.
• TIMESTAMP WITH TIME ZONE: TIMESTAMP which includes a time zone offset in its value. This datatype is useful for getting information of date and evaluating it across geographic regions.
• TIMESTAMP WITH LOCAL TIME ZONE: TIMESTAMP which includes a zone time offset in its value.

Below table provides the functions of Datetime:

NOTE: here a represents a timestamp.

Functions with its Description:

• ADD_MONTHS(a, b): Add y months to ‘a’. If b is negative, ‘b’ months are subtracted from a.
• LAST_DAY(a): To get the month last day.
• MONTHS_BETWEEN(a, b): To get the number of months between ‘a’ and ‘b’.
• NEW_TIME: To get the day/time value from a time zone specified.
• NEXT_DAY(a, day): To get the datetime of the next day following ‘a’; day is specified as a literal string, for example MONDAY.
• ROUND(a [, unit]): To round ‘a’. ‘a’ is rounded to the starting of the nearest day by default.
• SYSDATE(): To get the current datetime for the os set.
• TRUNC(a[, unit]): To truncate ‘a’. ‘a’ is truncated to the starting of the day by default.

Timestamp functions:

NOTE: Here 'a' represents a timestamp.

• SYS_EXTRACT_UTC(a): To get the UTC from a datetime value with time zone region name.
• SYSTIMESTAMP(): To get the current time and system date and time zone on user local database.
• LOCALTIMESTAMP(): To get a TIMESTAMP that contains the local time in the session time zone.
• CURRENT_TIMESTAMP(): To get a TIMESTAMP WITH TIME ZONE that contains the current session time and the time zone.
• FROM_TZ(a, time_zone): To convert the TIMESTAMP ‘a’ and time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE.
• EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM a): To extract a value from ‘a’ interval value.
• TO_TIMESTAMP_TZ(a, [format]): To convert a string to a timestamp.
• TO_TIMESTAMP(x, [format]): To convert the string ‘a’ to a TIMESTAMP.

Example:

 Ex: EXTRACT(YEAR FROM DATE '2015-04-02')Result: 2015 Ex: SELECT LOCALTIMESTAMP FROM DUAL;Result: 4/02/2015 6:36:05.248000 PM Ex: TO_TIMESTAMP('2015/APR/13 11:10:19', 'YYYY/MON/DD HH:MI:SS')Result:'13-APR-15 11.10.19.000000000 AM' as a timestamp value. Ex: SELECT SYSTIMESTAMP FROM dual;Result: 04/02/2015 6:35:24 PM 

The Functions and Data Types of Interval are:

The Interval data types:

1. INTERVAL YEAR TO MONTH: To store a period of time by using datetime fields YEAR and MONTH.
2. INTERVAL DAY TO SECOND: To stores a period of time in terms of seconds, minutes, hours and days.
   • NUMTODSINTERVAL (a, interval_unit): To convert a number to a literal of INTERVAL DAY TO SECOND.
   • NUMTOYMINTERVAL(a, interval_unit): To converts the number ‘a’ to an INTERVAL YEAR TO MONTH.
   • TO_YMINTERVAL(a): To convert the string ‘a’ to an INTERVAL YEAR TO MONTH.
   • TO_DSINTERVAL(a): To convert the string ‘a’ to an INTERVAL DAY TO SECOND.

In next part of this quick introduction guide to PL/SQL we will explore OO concepts. OO is acronym to Object Oriented.