Introduction
PL/SQL has a date and time datatype that helps to hold and calculate dates, intervals, and times. The variable of type date or time contains a value known as Datetime, while the variable that keeps the interval data type is known as Interval. Both these data types have fields that set the value. We will study them in detail.
The following are the Datetime data types:
- DATE
- TIMESTAMP
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
The following are the Interval data types:
- INTERVAL YEAR TO MONTH
- INTERVAL DAY TO SECOND
Field Values: Datetime and Interval
The following are the field values for Datetime and Interval:
Field Name |
Valid Datetime field values |
Valid Interval values |
SECOND |
00 to 59.9 (m), where m is the time-fractional seconds,here m is 9. |
00 to 59.9 (m), where m is the Interval fractional seconds, here m=9 |
MINUTE |
00 to 59 |
0 to 59 |
HOUR |
00 to 23 |
0 to 23 |
DAY |
01 to 31 (depends on the month and year according to the rules of the calendar) |
Any non-zero integer |
MONTH |
01 to 12 |
0to 11 |
YEAR |
-4712 to 9999 (excluding 0) |
Any non-zero integer |
TIMEZONE_MINUTE |
00 to 59 |
Not Applicable |
TIMEZONE_HOUR |
-12 to 14 ( including daylight saving time changes) |
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
DATE
It stores the date and time information in both number and character data types. It comprises the daytime from midnight in seconds. SYSDATE is a date function that gives the present time and date. We can also apply mathematical operations like addition and subtraction on dates in PL/SQL. For example, SYSDATE-1 points to yesterday while SYSDATE+1 points to tomorrow.
TIMESTAMP
It stores the day, month, and year of the DATE data type and hour, minute, and second values. It is an extension of the data type DATE. It is used to store the precise time values.
TIMESTAMP WITH TIME ZONE
It is an extension of the TIMESTAMP data type that contains the time zone region name(or a time zone offset) in its value. The time zone offset is the difference between local time and UTC. This data type is used to collect and evaluate data information across geographic regions.
TIMESTAMP WITH LOCAL TIME ZONE
It is another extension of TIMESTAMP that includes a time zone offset in its value.
The following are the Datetime functions (where x has the DateTime value):
S.No. |
Function Name |
Description |
1 |
LAST_DAY(x) |
It returns the last day of the month x |
2 |
ADD_MONTHS(x, y) |
It adds y months to x |
3 |
MONTHS_BETWEEN(x, y) |
It returns the count of the number of months between x and y |
4 |
NEXT_DAY(x, day) |
It will return the DateTime of the next day after x |
5 |
NEW_TIME |
It will return the time/day value from a time zone specified by the user. |
6 |
ROUND(x[, unit]) |
It rounds the value of x |
7 |
SYSDATE() |
Returns the current Datetime |
8 |
TRUNC(x [, unit]) |
It truncates x |
The following are the Timestamp functions (where x has the timestamp value):
S.No. |
Function Name |
Description |
1 |
CURRENT_TIMESTAMP() |
It fetches a TIMESTAMP WITH TIMEZONE having the current session and session time zone. |
2 |
LOCALTIMESTAMP() |
It fetches a TIMESTAMP having the local time in the session time zone. |
3 |
SYSTIMESTAMP() |
It fetches a TIMESTAMP WITH TIMEZONE's present database time and time zone. |
4 |
FROM_TZ(x, time_zone) |
It converts the "x" TIMESTAMP and mentions time_zone to TIMESTAMP WITH TIMEZONE. |
5 |
EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) |
It extracts and returns a year, month, day, hour, minute, second, or time zone from x. |
6 |
SYS_EXTRACT_UTC(x) |
It converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC. |
7 |
TO_TIMESTAMP(x, [format]) |
It converts the string x to a TIMESTAMP. |
8 |
TO_TIMESTAMP_TZ(x, [format]) |
It converts the string x to a TIMESTAMP WITH TIMEZONE. |
Examples:
The following are some examples with their outputs.
Example 1
SELECT SYSDATE FROM DUAL; |
Output
It will give the current date and time.
01/23/2022 8:25:34 PM |
Example 2
SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS' ) FROM DUAL; |
Output
It will give the current date and time in the given format
23-01-2022 08:26:12 |
Example 3
SELECT ADD_MONTHS(SYSDATE, 6) FROM DUAL; |
Output
This will increase the number of months by 6 in the current date and time
07/23/2022 08:28:25 |
Example 4
SELECT LOCALTIMESTAMP FROM DUAL; |
Output
01/23/2022 8:28:34.347000 PM |
The Interval data types and their functions
INTERVAL YEAR TO MONTH
The INTERVAL YEAR TO MONTH data type is used to store and compute years and months intervals. Precision is used here; precision counts the number of digits in a year's field. The value precision should be any integer literal from 0 to 4. The default value is set to 2.
INTERVAL DAY TO SECOND
Interval year to a second data type is used to store and compute days, hours, minutes, and seconds intervals.
The following are the functions in Interval in PL/SQL:
S.No. |
Function Name |
Description |
1 |
NUMTOYMINTERVAL (m, interval) |
It transforms the number m to INTERVAL YEAR TO MONTH. |
2 |
NUMTODSINTERVAL (m, interval) |
It transforms the number m to INTERVAL DAY TO SECOND. |
3 |
TO_YMINTERVAL (m) |
It transforms the string m to INTERVAL YEAR TO MONTH. |
4 |
TO_DSINTERVAL (m) |
It transforms the string m to INTERVAL DAY TO SECOND. |