Plsql 简明教程

PL/SQL - Date & Time

在本章里，我们将讨论 PL/SQL 中的日期和时间。在 PL/SQL 中有两类日期和时间相关数据类型 -

In this chapter, we will discuss the Date and Time in PL/SQL. There are two classes of date and time related data types in PL/SQL −

  1. Datetime data types

  2. Interval data types

Datetime 数据类型是 -

The Datetime data types are −

  1. DATE

  2. TIMESTAMP

  3. TIMESTAMP WITH TIME ZONE

  4. TIMESTAMP WITH LOCAL TIME ZONE

区间数据类型包括 −

The Interval data types are −

  1. INTERVAL YEAR TO MONTH

  2. INTERVAL DAY TO SECOND

Field Values for Datetime and Interval Data Types

datetime 和 interval 数据类型均由 fields 组成。这些字段的值决定了数据类型的值。下表列出了日期时间和区间的字段及其可能的值。

Both datetime and interval data types consist of fields. The values of these fields determine the value of the data type. The following table lists the fields and their possible values for datetimes and intervals.

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 data types −

DATE

它在字符和数字数据类型中都存储日期和时间信息。它由世纪、年份、月份、日期、小时、分钟和秒的信息组成。它指定为 −

It stores date and time information in both character and number datatypes. It is made of information on century, year, month, date, hour, minute, and second. It is specified as −

TIMESTAMP

它是 DATE 数据类型的扩展。它存储 DATE 数据类型的年、月和日，以及小时、分钟和秒值。它适用于存储精确的时间值。

It is an extension of the DATE data type. It stores the year, month, and day of the DATE datatype, along with hour, minute, and second values. It is useful for storing precise time values.

TIMESTAMP WITH TIME ZONE

它是 TIMESTAMP 的变体，在其值中包含时区区域名称或时区偏移。时区偏移是以小时和分钟表示的本地时间与 UTC 时间之间的差异。这种数据类型适用于跨地理区域收集和评估日期信息。

It is a variant of TIMESTAMP that includes a time zone region name or a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC. This data type is useful for collecting and evaluating date information across geographic regions.

TIMESTAMP WITH LOCAL TIME ZONE

它是 TIMESTAMP 的另一个变体，在其值中包含一个时区偏移。

It is another variant of TIMESTAMP that includes a time zone offset in its value.

下表提供了日期时间函数（其中 x 具有日期时间值）−

Following table provides the Datetime functions (where, x has the datetime value) −

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.

时间戳函数（其中 x 具有时间戳值）−

Timestamp functions (where, x has a timestamp value) −

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

Examples

以下代码片段展示了上述函数的使用方法：

The following code snippets illustrate the use of the above functions −

Example 1

Example 1

SELECT SYSDATE FROM DUAL;

Output −

Output −

08/31/2012 5:25:34 PM

Example 2

Example 2

SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;

Output −

Output −

31-08-2012 05:26:14

Example 3

Example 3

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

Output −

Output −

01/31/2013 5:26:31 PM

Example 4

Example 4

SELECT LOCALTIMESTAMP FROM DUAL;

Output −

Output −

8/31/2012 5:26:55.347000 PM

The Interval Data Types and Functions

以下是 Interval 数据类型：

Following are the Interval data types −

  1. IINTERVAL YEAR TO MONTH − It stores a period of time using the YEAR and MONTH datetime fields.

  2. INTERVAL DAY TO SECOND − It stores a period of time in terms of 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.