SQL record-plsql date and time

PL/SQL Date and time

PL/SQL provides two date and time-related data types:

• Date Time (Datetime) data type

• Interval data type

The datetime data types are:

• DATE

• TIMESTAMP

• TIMESTAMP with Time ZONE

• TIMESTAMP with LOCAL time ZONE

The interval data type is:

• INTERVAL year to MONTH

• INTERVAL Day to SECOND

Date Time field value and interval data type

These two date time and interval data types include fields. The values of these fields determine the value of the data type. The following table lists the time and interval fields and their possible values.

The The

field name	valid datetime value	valid interval value
year	-4712 to 9999 (excluding 0 years)	any non-0 integer
MONTH	01-12	0- 11
day	01-31 (rules by month and year values, by locale of the calendar)	any non-0 integer
HOUR	00-23	0-23
MINUTE	00-59	0-59
SECOND	xx to 59.9 (n), where 9 (n) is the time fractional second precision in 9 (n) of the part is not applicable to date.	0 to 59.9 (N), where 9 (n) is interval fractional seconds precision
timezone_hour	12-14 (adaptation to Daylight saving time change) DATE or & nbsp Timestamp not applicable.	Not available
timezone_minute	00-59 DATE or  timestamp not applicable.	Not available
timezone_region	DATE or  timestamp does not apply.	Not available
timezone_abbr	DATE or  timestamp does not apply.	Not available

Date-time data types and functions

Here is the datetime data type:

• Date-it is stored in both the character and numeric data types of day and time information. It is information by the last century, year, month, day, time, minute, and second. It is specified as:

• TIMESTAMP -it is an extension of the date data type. It stores the date data type for the year, month, and day, as well as the hour, minute, and second values. It is useful for storing accurate time values.

• TIMESTAMP with time zone-it is a variant of the timestamp, which includes a timezone zone name or its value offset in the time zone. The time zone offset is the difference between the local time and UTC (in hours and minutes). This data type is useful for collecting and calculating up-to-date information across geographic regions.

• TIMESTAMP with LOCAL time zone -it is another variant of the timestamp (TIMESTAMP), which includes its value offset in a time zone.

The following table lists the DateTime functions (where x has a datetime value):

S.N	Function Name & Description
1	Add_months (x, y); Increase y months to X
2	Last_day (x); Return to 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 date and time of the second day after X
5	New_time; Returns the value of a time/day from a specified time zone by a user
6	ROUND (x [, Unit]); x rounded;
7	Sysdate (); Returns the current date time.
8	TRUNC (x [, Unit]); Truncate X

Timestamp function (where x is a timestamp value):

S.N	Function Name & Description
1	Current_timestamp (); Returns the timestamp in the time zone that contains the current session time and the session time zone (TIMESTAMP with time zone)
2	EXTRACT ({year | MONTH | Day | HOUR | MINUTE | SECOND} | {Timezone_hour | Timezone_minute} | {timezone_region |} TIMEZONE_ABBR) from X) Extracts from X and returns the year, month, day, hour, minute, second, or time;
3	From_tz (x, Time_zone); Specify with timezone a timestamp timestamp x and time zone conversion with Time_zone
4	Localtimestamp (); Returns the local time timestamp contained in the session time zone
5	Systimestamp (); Returns the timestamp in the time zone that contains the current database and the timezone of the database time zone (TIMESTAMP with time zone)
6	SYS_EXTRACT_UTC (x); Timestamp with TIMEZONE x converted to include date and time UTC timestamp
7	To_timestamp (x, [format]); Converts the string x to a timestamp
8	To_timestamp_tz (x, [format]); String x convert to with timezone timestamp

Example:

The following code snippet illustrates the use of the functions described above:

SELECT from DUAL;  

Output:

08/31/2014 5:25:34 PM

SELECT to_char(current_date,' dd-mm-yyyy HH:MI:SS ')from DUAL;   

Output:

31-08-2014 05:26:14

SELECT add_months(sysdate,5)from DUAL;        

Output:

01/31/2014 5:26:31 PM

SELECT from DUAL;  

Output:

8/31/2014 5:26:55.347000 PM

Interval data types and functions

The following are the interval data types:

• INTERVAL years to month-it stores a period of time using the year and month datetime fields

• INTERVAL Day to SECOND-it stores days, hours, minutes and seconds in a period of time

Interval function:

S.N	Function Name & Description
1	Numtodsinterval (x, Interval_unit); Integer x converted to an interval (day to SECOND)
2	Numtoyminterval (x, Interval_unit); Conversion of X to interval (year to MONTH)
3	To_dsinterval (x); Converts a string x time interval (day to SECOND) through seconds
4	To_yminterval (x); Converts a string x time interval (year to MONTH) to an integer

SQL record-plsql date and time