(16) PL/SQL date and time

pl/sql provides two date and time-related data types:
1. DateTime data type
date
Span style= "color: #ff0000; Font-size:13px "> TIMESTAMP
TIMESTAMP with time ZONE
TIMESTAMP with LOCAL time ZONE
2. Interval data type
INTERVAL year to MONTH
INTERVAL day to SECOND

 
 

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 t o 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

Ii. 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.
　　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
DateTime function (where x has a datetime value):

s.n	function name & Description
1	add_months (x, y);      Add y months to x
2	last_day (x);    return 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);    return x Date Time next day
5	new_time;    returns the value of a time/day from a specified time zone by the user
6	ROUND (x [, Unit]);    x rounding;
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 sysdate 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 Localtimestamp from DUAL;
Output: 8/31/2014 5:26:55.347000 PM

Three, 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

(16) PL/SQL date and time