In this part, we will talk about the Date and Time in PL/SQL. There are two classes of date and time related information types in PL/SQL −
- Datetime information types
- Stretch information types
The Datetime information types are −
- DATE
- TIMESTAMP
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
The Interval information types are −
- Stretch YEAR TO MONTH
- Stretch DAY TO SECOND
Field Values for Datetime and Interval Data Types
Both datetime and stretch information types comprise of fields. The estimations of these fields decide the estimation of the information type. The accompanying table records the fields and their potential qualities for datetimes and stretches.
Field Name | Valid Datetime Values | Valid Interval Values |
---|---|---|
YEAR | -4712 to 9999 (excluding year 0) | Any nonzero integer |
MONTH | 01 to 12 | 0 to 11 |
DAY | 01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale) | Any nonzero integer |
HOUR | 00 to 23 | 0 to 23 |
MINUTE | 00 to 59 | 0 to 59 |
SECOND |
00 to 59.9(n), where 9(n) is the precision of time fractional seconds The 9(n) portion is not applicable for DATE. |
0 to 59.9(n), where 9(n) is the precision of interval fractional seconds |
TIMEZONE_HOUR |
-12 to 14 (range accommodates daylight savings time changes) Not applicable for DATE or TIMESTAMP. |
Not applicable |
TIMEZONE_MINUTE |
00 to 59 Not applicable for DATE or TIMESTAMP. |
Not applicable |
TIMEZONE_REGION | Not applicable for DATE or TIMESTAMP. | Not applicable |
TIMEZONE_ABBR | Not applicable for DATE or TIMESTAMP. | Not applicable |
The Datetime Data Types and Functions
Following are the Datetime information types −
DATE
It stores date and time data in both character and number datatypes. It is made of data on century, year, month, date, hour, moment, and second. It is determined as −
TIMESTAMP
It is an expansion of the DATE information type. It stores the year, month, and day of the DATE datatype, alongside hour, moment, and second qualities. It is helpful for putting away exact time esteems.
TIMESTAMP WITH TIME ZONE
It is a variation of TIMESTAMP that incorporates a time region area name or a time region counterbalance in its worth. The time region counterbalance is the distinction (in hours and minutes) between neighborhood time and UTC. This information type is valuable for gathering and assessing date data across geographic locales.
TIMESTAMP WITH LOCAL TIME ZONE
It is another variation of TIMESTAMP that incorporates a time region counterbalance in its worth.
Following table gives the Datetime capacities (where, x has the datetime esteem) −
S.No | Function Name & Description |
---|---|
1 |
ADD_MONTHS(x, y); Adds y months to x. |
2 |
LAST_DAY(x); Returns the last day of the month. |
3 |
MONTHS_BETWEEN(x, y); Returns the number of months between x and y. |
4 |
NEXT_DAY(x, day); Returns the datetime of the next day after x. |
5 |
NEW_TIME; Returns the time/day value from a time zone specified by the user. |
6 |
ROUND(x [, unit]); Rounds x. |
7 |
SYSDATE(); Returns the current datetime. |
8 |
TRUNC(x [, unit]); Truncates x. |
Timestamp capacities (where, x has a timestamp esteem) −
S.No | Function Name & Description |
---|---|
1 |
CURRENT_TIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the current session time along with the session time zone. |
2 |
EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) Extracts and returns a year, month, day, hour, minute, second, or time zone from x. |
3 |
FROM_TZ(x, time_zone); Converts the TIMESTAMP x and the time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE. |
4 |
LOCALTIMESTAMP(); Returns a TIMESTAMP containing the local time in the session time zone. |
5 |
SYSTIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the current database time along with the database time zone. |
6 |
SYS_EXTRACT_UTC(x); Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC. |
7 |
TO_TIMESTAMP(x, [format]); Converts the string x to a TIMESTAMP. |
8 |
TO_TIMESTAMP_TZ(x, [format]); Converts the string x to a TIMESTAMP WITH TIMEZONE. |
Examples
The accompanying code scraps represent the utilization of the above capacities −
Model 1
SELECT SYSDATE FROM DUAL;
Yield −
08/31/2012 5:25:34 PM
Model 2
SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;
Yield −
31-08-2012 05:26:14
Model 3
SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;
Yield −
01/31/2013 5:26:31 PM
Model 4
SELECT LOCALTIMESTAMP FROM DUAL;
Yield −
8/31/2012 5:26:55.347000 PM
The Interval Data Types and Functions
Following are the Interval information types −
- IINTERVAL YEAR TO MONTH − It stores a timeframe utilizing the YEAR and MONTH datetime fields.
- Span DAY TO SECOND − It stores a timeframe as far as days, hours, minutes, and seconds.
Interval Functions
S.No | Function Name & Description |
---|---|
1 |
NUMTODSINTERVAL(x, interval_unit); Converts the number x to an INTERVAL DAY TO SECOND. |
2 |
NUMTOYMINTERVAL(x, interval_unit); Converts the number x to an INTERVAL YEAR TO MONTH. |
3 |
TO_DSINTERVAL(x); Converts the string x to an INTERVAL DAY TO SECOND. |
4 |
TO_YMINTERVAL(x); Converts the string x to an INTERVAL YEAR TO MONTH. |