Oracle时间函数-易微帮

1. 时区

先说下时区，oracle时区分两种：数据库时区和会话时区。
查看数据库的时区：select dbtimezone from dual;
设置数据库时区：创建时指定：create database db1... set time_zone='+6:00'; 或后期修改：alter database set time_zone='+6:00';
查看当前会话的时区：select sessiontimezone from dual;
设置当前会话时区：alter session set time_zone='+06:00';

dbtimezone和sessiontimezone的英文释义：

DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

SESSIONTIMEZONE returns the time zone of the current session. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement.

Note:The default client session time zone is an offset even if the client operating system uses a named time zone. If you want the default session time zone to use a named time zone, then set the ORA_SDTZ variable in the client environment to an Oracle time zone region name. Refer to Oracle Database Globalization Support Guide for more information on this variable.

下面的查询示例的会话，设置了date的格式：

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';

各种时间格式可以查看官网：

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Format-Models.html#GUID-24E16D8D-25E4-4BD3-A38D-CE1399F2897C

2. SYSDATE

返回数据库服务器所在操作系统的当前日期和时间，不受时区影响。英文释义：

SYSDATE returns the current date and time set for the operating system on which the database server resides. The data type of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter. The function requires no arguments. In distributed SQL statements, this function returns the date and time set for the operating system of your local database. You cannot use this function in the condition of a CHECK constraint.

Note:The FIXED_DATE initialization parameter enables you to set a constant date and time that SYSDATE will always return instead of the current date and time. This parameter is useful primarily for testing. Refer to Oracle Database Reference for more information on the FIXED_DATE initialization parameter.

查询如下：

SQL> select sysdate from dual;

SYSDATE
-------------------
2024-04-25 09:45:42

3. CURRENT_DATE

返回当前会话时区中的当前日期，受时区影响。英文释义：

CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of data type DATE.

查询如下，注意看时区的影响：

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL> select sysdate,current_date from dual;

SYSDATE             CURRENT_DATE
------------------- -------------------
2024-04-25 09:51:45 2024-04-25 09:51:45

SQL> alter session set time_zone='+06:00';

Session altered.

SQL> select sysdate,current_date from dual;

SYSDATE             CURRENT_DATE
------------------- -------------------
2024-04-25 09:52:20 2024-04-25 07:52:20

4. CURRENT_TIMESTAMP

语法：current_timestamp(precision)

返回会话时区的当前日期和时间，如果省略精度，则默认值为6。返回类型是TIMESTAMP WITH TIME ZONE，受时区影响。英文释义：

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of data type TIMESTAMP WITH TIME ZONE. The time zone offset reflects the current local time of the SQL session. If you omit precision, then the default is 6. The difference between this function and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.

In the optional argument, precision specifies the fractional second precision of the time value returned.

查询如下：

SQL> select sysdate,current_date,current_timestamp from dual;

SYSDATE             CURRENT_DATE        CURRENT_TIMESTAMP
------------------- ------------------- ---------------------------------------------------------------------------
2024-04-25 10:01:56 2024-04-25 08:01:56 25-APR-24 08.01.56.603271 AM +06:00

5. LOCALTIMESTAMP

语法：localtimestamp(timestamp_precision)

LOCALTIMESTAMP以TIMESTAMP数据类型的值返回会话时区的当前日期和时间。这个函数和CURRENT_TIMESTAMP的区别在于LOCALTIMESTAMP返回一个TIMESTAMP值，而CURRENT_TIMESTAMP返回一个TIMESTAMP WITH TIME ZONE值。英文释义：

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of data type TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

The optional argument timestamp_precision specifies the fractional second precision of the time value returned.

查询如下：

SQL> select sysdate,current_date,current_timestamp,localtimestamp from dual;

SYSDATE             CURRENT_DATE        CURRENT_TIMESTAMP                                                           LOCALTIMESTAMP
------------------- ------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2024-04-25 10:13:03 2024-04-25 08:13:03 25-APR-24 08.13.03.637959 AM +06:00                                         25-APR-24 08.13.03.637959 AM

6. SYSTIMESTAMP

返回数据库所在系统的系统日期，包括小数秒数和时区。返回类型是TIMESTAMP WITH TIME ZONE。英文释义：

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

查询如下：

SQL> select sysdate,current_date,current_timestamp,localtimestamp,systimestamp from dual;

SYSDATE             CURRENT_DATE
------------------- -------------------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
SYSTIMESTAMP
---------------------------------------------------------------------------
2024-04-25 14:30:32 2024-04-25 12:30:32
25-APR-24 12.30.32.437528 PM +06:00
25-APR-24 12.30.32.437528 PM
25-APR-24 02.30.32.437524 PM +08:00

7. ADD_MONTHS

语法：add_months(date, integer)

返回日期日期加上整数月份。date参数可以是一个datetime值或任何可以隐式转换为date的值。integer参数可以是整数或任何可以隐式转换为整数的值，正数往后，负数往前。如果date是该月的最后一天，或者结果月份的天数少于date的day值，则结果为结果月份的最后一天。否则，结果具有与date相同的day值。英文释义：

ADD_MONTHS returns the date date plus integer months. A month is defined by the session parameter NLS_CALENDAR. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the data type of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

查询如下：

SQL> select add_months('2024-03-31',-1) from dual;

ADD_MONTHS('2024-03
-------------------
2024-02-29 00:00:00

SQL> select add_months('2024-03-31',-2) from dual;

ADD_MONTHS('2024-03
-------------------
2024-01-31 00:00:00

8. MONTHS_BETWEEN

语法：months_between(date1,date2)

返回日期date1和日期date2之间的月数，也许有小数。英文释义：

MONTHS_BETWEEN returns number of months between dates date1 and date2. The month and the last day of the month are defined by the parameter NLS_CALENDAR. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

查询如下：

select months_between('2024-04-25', '2024-04-23') from dual;

MONTHS_BETWEEN('2024-04-25','2024-04-23')
-----------------------------------------
                               .064516129

SQL> select months_between('2024-03-25', '2024-04-23') from dual;

MONTHS_BETWEEN('2024-03-25','2024-04-23')
-----------------------------------------
                               -.93548387

SQL> select months_between('2024-04-25', '2024-04-25') from dual;

MONTHS_BETWEEN('2024-04-25','2024-04-25')
-----------------------------------------
                                        0

求两个日期的差

date可以直接相减，得到的就是差值，不过有可能有小数，所以可能需要结合trunc函数等一起使用。

查询如下：

SQL> select to_date('2024-03-25 12:23:32','yyyy-mm-dd hh24:mi:ss') - to_date('2024-03-16 11:22:11','yyyy-mm-dd hh24:mi:ss') as daydiff from dual;

   DAYDIFF
----------
9.04260417

SQL> select trunc(to_date('2024-03-25 12:23:32','yyyy-mm-dd hh24:mi:ss')) - trunc(to_date('2024-03-16 11:22:11','yyyy-mm-dd hh24:mi:ss')) as daydiff from dual;

   DAYDIFF
----------
         9

SQL> select to_date('2024-03-25 12:23:32','yyyy-mm-dd hh24:mi:ss') - 2 as daydiff from dual;

DAYDIFF
-------------------
2024-03-23 12:23:32

9. EXTRACT (datetime)

语法：extract(x from expr)

x的可选值有：YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/TIMEZONE_HOUR/TIMEZONE_MINUTE/TIMEZONE_REGION/TIMEZONE_ABBR

从日期时间或间隔表达式中提取并返回指定日期时间字段的值。expr可以是任何计算为与请求字段兼容的日期时间或间隔数据类型的表达式。英文释义：

EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression. The expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:

If YEAR or MONTH is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH.

If DAY is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND.

If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.

If TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

查询如下：

SQL> select sysdate from dual;

SYSDATE
-------------------
2024-04-26 09:20:46

SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2024

SQL> select extract(month from sysdate) from dual;

EXTRACT(MONTHFROMSYSDATE)
-------------------------
                        4

SQL> select extract(day from sysdate) from dual;

EXTRACT(DAYFROMSYSDATE)
-----------------------
                     26

10. TRUNC (date)

语法：trunc(date, fmt)

返回日期，其中一天的时间部分被截断为格式模型fmt指定的单位。不指定fmt的话，默认值是'DD'，即将一天的时间格式化为 00:00:00。英文释义：

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. This function is not sensitive to the NLS_CALENDAR session parameter. It operates according to the rules of the Gregorian calendar. The value returned is always of data type DATE, even if you specify a different datetime data type for date. If you omit fmt, then the default format model 'DD' is used and the value returned is date truncated to the day with a time of midnight. Refer to "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.

查询如下：

SQL> select trunc(to_date('2024-03-25 12:23:32','yyyy-mm-dd hh24:mi:ss')) from dual;

TRUNC(TO_DATE('2024
-------------------
2024-03-25 00:00:00

SQL> select trunc(to_date('2024-03-25 12:23:32','yyyy-mm-dd hh24:mi:ss'), 'DD') from dual;

TRUNC(TO_DATE('2024
-------------------
2024-03-25 00:00:00

SQL> select trunc(to_date('2024-03-25 12:23:32','yyyy-mm-dd hh24:mi:ss'), 'YEAR') from dual;

TRUNC(TO_DATE('2024
-------------------
2024-01-01 00:00:00

11. ROUND (date)

语法：round(date, fmt)

返回日期四舍五入到格式模型fmt指定的单位。如果省略fmt，则日期四舍五入到最接近的一天。这个和trunc的区别就是trunc是直接抹除，而round是四舍五入。英文释义：

ROUND returns date rounded to the unit specified by the format model fmt. This function is not sensitive to the NLS_CALENDAR session parameter. It operates according to the rules of the Gregorian calendar. The value returned is always of data type DATE, even if you specify a different datetime data type for date. If you omit fmt, then date is rounded to the nearest day. The date expression must resolve to a DATE value.

查询如下：

SQL> select round(to_date('2024-04-23 11:59:59', 'yyyy-mm-dd hh24:mi:ss')) as d from dual;

D
-------------------
2024-04-23 00:00:00

SQL> select round(to_date('2024-04-23 12:00:00', 'yyyy-mm-dd hh24:mi:ss')) as d from dual;

D
-------------------
2024-04-24 00:00:00

12. TO_CHAR (datetime)

语法：to_char(date, fmt, 'nlsparam')

将date转化为fmt的字符串格式。英文释义：

TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of VARCHAR2 data type in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:

DATE values are converted to values in the default date format.

TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.

TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.

Interval values are converted to the numeric representation of the interval literal.

查询如下：

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') as chardate from dual;

CHARDATE
-------------------
2024-04-26 09:57:16

SQL> select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') as chardate from dual;

CHARDATE
-------------------
2024/04/26 09:59:36

13. TO_DATE

语法：to_date(char, fmt, 'nlsparam')

将字符串转化为时间格式。英文释义：

TO_DATE converts char to a value of DATE data type.

查询如下：

SQL> select to_date('2024-04-26 12:23:32', 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_DATE('2024-04-26
-------------------
2024-04-26 12:23:32

14. TO_TIMESTAMP

语法：to_timestamp(char, fmt, 'nlsparam')

将字符串转化为timestamp时间格式。英文释义：

TO_TIMESTAMP converts char to a value of TIMESTAMP data type.

查询如下：

SQL> select to_timestamp('2024-04-26 12:23:32', 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_TIMESTAMP('2024-04-2612:23:32','YYYY-MM-DDHH24:MI:SS')
---------------------------------------------------------------------------
26-APR-24 12.23.32.000000000 PM

15. TO_TIMESTAMP_TZ

语法：to_timestamp_tz(char, fmt, 'nlsparam')

将字符串转化为timestamp_tz时间格式。英文释义：

TO_TIMESTAMP_TZ converts char to a value of TIMESTAMP WITH TIME ZONE data type.

查询如下：

SQL> select to_timestamp_tz('2024-04-26 12:23:32', 'yyyy-mm-dd hh24:mi:ss') from dual;

TO_TIMESTAMP_TZ('2024-04-2612:23:32','YYYY-MM-DDHH24:MI:SS')
---------------------------------------------------------------------------
26-APR-24 12.23.32.000000000 PM +08:00

16. LAST_DAY

语法：last_day(date)

返回包含date的月份的最后一天的日期。英文释义：

LAST_DAY returns the date of the last day of the month that contains date. The last day of the month is defined by the session parameter NLS_CALENDAR. The return type is always DATE, regardless of the data type of date.

查询如下：

SQL> select last_day('2024-04-26') as ld from dual;

LD
-------------------
2024-04-30 00:00:00

SQL> select last_day('2024-03-11') as ld from dual;

LD
-------------------
2024-03-31 00:00:00

17. NEXT_DAY

语法：next_day(date, char)

返回第一个以char命名的工作日的日期，该日期晚于日期date。参数char必须是会话日期语言中的星期几，可以是全名，也可以是缩写。英文释义：

NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE, regardless of the data type of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.

查询如下：

SQL> select next_day('2024-04-26','TUESDAY') as nexttue from dual;

NEXTTUE
-------------------
2024-04-30 00:00:00

SQL> select next_day('2024-04-26','TUE') as nexttue from dual;

NEXTTUE
-------------------
2024-04-30 00:00:00

18. NEW_TIME

语法：new_time(date,timezone1,timezone2)

将时间进行时区的转换。英文释义：

NEW_TIME returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time. The return type is always DATE, regardless of the data type of date.

查询如下：

SQL> select to_date('2024-10-01 12:00:00','yyyy-MM-dd hh24:mi:ss'),new_time(to_date('2024-10-01 12:00:00','yyyy-MM-dd hh24:mi:ss'),'EST','HST') from dual;

TO_DATE('2024-10-01 NEW_TIME(TO_DATE('2
------------------- -------------------
2024-10-01 12:00:00 2024-10-01 07:00:00

参考文献：

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Single-Row-Functions.html#GUID-5652DBC2-41C7-4F07-BEDD-DAF620E35F3C

Oracle时间函数

1. 时区

2. SYSDATE

3. CURRENT_DATE

4. CURRENT_TIMESTAMP

5. LOCALTIMESTAMP

6. SYSTIMESTAMP

7. ADD_MONTHS

8. MONTHS_BETWEEN

求两个日期的差

9. EXTRACT (datetime)

10. TRUNC (date)

11. ROUND (date)

12. TO_CHAR (datetime)

13. TO_DATE

14. TO_TIMESTAMP

15. TO_TIMESTAMP_TZ

16. LAST_DAY

17. NEXT_DAY

18. NEW_TIME

网站公告

今日签到

热门文章

最新发布