Table of contents
1.
Introduction
1.1.
Field Values: Datetime and Interval
1.2.
The Datetime data types and functions
1.3.
The Interval data types and their functions
2.
 
3.
 
4.
 
5.
 
6.
FAQs
7.
Key Takeaways
Last Updated: Mar 27, 2024

PL/SQL Date and Time

Author Prashant Singh
2 upvotes
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

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.

 

 

 

 

 

Also see, DCL Commands in SQL and Tcl Commands in SQL

FAQs

  1. What is the current timestamp?
    The current timestamp or CURRENT_TIMESTAMP describes a timestamp dependent on the reading of the time of the day clock while executing a SQL statement in the server.
     
  2. What does Sysdate return in Oracle?
    The SYSDATE() function gives the current date and time. It returns the value of the Date data type.
     
  3. What is the complete form of PL/SQL?
    It stands for Procedural Language extensions to SQL.

Key Takeaways

In this blog, we learned about the data types in PL/SQL, i.e., Datetime data type and interval data type. We study each of these data types and the functions with their descriptions. We learned how to use these functions with examples to understand them better.

Visit here for the top 100 SQL problems asked in various product and service-based companies like Google, Microsoft, Infosys, IBM, etc.

You can visit Coding Ninjas Studio to practice programming problems for your complete interview preparation. Check it out to get hands-on experience with frequently asked interview questions and land your dream job.

Live masterclass