图片 24

Oracle 11g Release 1 (11.1) 单行函数——日期函数

一. 数值函数

  图片 1

1.  abs(x) 返回x的绝对值

SELECT ABS(-0.8),ABS(0.8);

图片 2

2.ceil(x) 返回大于x的最大整数

SELECT CEIL(-0.8),CEIL(0.8);

图片 3

3.floor(x) 返回小于x的最大整数

SELECT FLOOR(-0.8),FLOOR(0.8);

图片 4

4.mod(x,y) 返回x/y的模

SELECT MOD(15,10),MOD(1,11),MOD(NULL,10)

图片 5

5. rand() 返回0到1内的随机值

SELECT RAND(),RAND();

图片 6

    还可是指定范围的随机数,如0~100的值

SELECT CEIL(100*RAND());

图片 7

6.round(x,y) 返回参数x的四舍五入的有y位小数的值,如果不写y,默认为0

SELECT ROUND(1.1),ROUND(1.1,2),ROUND(1.0,3)

图片 8

7. truncate(x,y) 返回数字x截断为y位小数的结果,看看与round的区别

SELECT ROUND(1.235,2),TRUNCATE(1.235,2);

图片 9

Mysql常用函数

二. 日期与时间函数

图片 10

1. curdate()  返回当前日期,只有年月日

SELECT CURDATE();

图片 11

2. curtime(): 返回当前时分秒

SELECT CURTime();

图片 12

3.now() 返回年月日时分秒

SELECT NOW();

图片 13

4. UNIX_TIMESTAMP(date) 返回unix 时间截

  SELECT UNIX_TIMESTAMP(NOW())

图片 14

5. FROM_UNIXTIME (unixtime) 和UNIX_TIMESTAMP互转

SELECT FROM_UNIXTIME(1530265708)

图片 15

6. week(date)和year(date),返回一年的第几周,各年份

SELECT WEEK(NOW()),YEAR(NOW());

图片 16

7.hour(time)和 minute(time) 返回小时和分钟

  SELECT HOUR(CURTIME()),MINUTE(CURTIME());

图片 17

8. monthname(date) 返回时间英文月份

 SELECT  MONTHNAME(NOW());

图片 18

9. date_format(date,fmt)按指定的格式显示日期

图片 19

图片 20

         下面的例子将当前时间显示为 “月,日,年” 格式

SELECT  DATE_FORMAT(NOW(),'%M,%D,%Y')

图片 21

 10. date_add(date,interval expr type)   
返回与所给日期date相差interval时间段的日期

  图片 22

 

   下面例子第1列返回当前日期时间,
2列返回距当前日期31天后的日期时间,3列返回距当前日期一年两个月后的日期时间。 (也可以用负数表示后退的日期时间)

SELECT NOW() AS current,
    DATE_ADD(NOW(),INTERVAL 31 DAY) AS after31dyas,
    DATE_ADD(NOW(),INTERVAL '1_2' YEAR_MONTH)  AS after_oneyear_twomonth;

图片 23

11. datediff(date1,date2) 用来计算两个日期之间相差的天数

 SELECT DATEDIFF('2018-08-08',NOW())    

图片 24

 

1.Concat(s1,s2…sn)函数:把传入的参数链接为一个字符串
2.Insert(str,x,y,instr)函数:将字符串str从第x位置开始,y个字符长的子串替换为字符串instr。
3.LOWER(str)UPPER(str)函数:把字符串转换成小写或大写
4.LEFT(str,x)和RIGHT(str,x)函数:分别返回字符串最左边的X个字符串和最右边的x个字符。如果第二个参数是NULL,那么将不返回任何字符串。
5.LPAD(str,n,pad)和RPAD(str,n,pad)函数:用字符串pad对str最左边和最右边进行填充,直到长度为n个字符长度。
6.LTRIM(str)和RTRIM(str)函数:去掉字符串str左侧和右侧空格。
7.PEPEAT(str,x)函数:返回str重复x次的结果。
8.REPLACE(str,a,b)函数:用字符串b替换字符串str中所有出现的字符串a。
9.STRCMP(s1,s2)函数:比较字符串s1和s2的ASCII码值得大小。
10.TRIM(str)函数:去掉目标字符串的开头和结尾的空格。
11.SUBSTRING(str,x,y)函数:返回从字符串str中的第x位置起y个字符长度的字串。
数值函数
1.ABS(X)函数:返回x的绝对值。
2.CELL(x)函数:返回大于x的最小整数。
3.FLOOR(x)函数:返回小于x的最大整数。
4.MOD(x,y)函数:返回x/y的模。
5.RAND()函数:返回0~1内的随机数。
6.ROUND(x,y)函数:返回参数x的四舍五入的有y位小数的值。
7.TRUNCATE(x,y)函数:返回数字x截断为y位小数的结果。
日期和时间函数
1.CURDATE()函数:返回当前日期,只包含年月日。
2.CURTIME()函数:返回当前时间,只包含时分秒。
3.NOW()函数:返回当前的日期和时间,年月日时分秒全都包含。
4.UNIX_TIMESTAMP(date)函数:返回当前date的UNIX时间戳。
5.FROM_UNIXTIME(unixtime)函数:返回UNIXTIME时间戳的日期值。
6.WEEK(DATE)和YEAR(DATE)函数:前者返回所给的日期是一年中的第几周,后者返回所给的日期是哪一年。
7.HOUR(time)和MINUTE(time)函数:前者返回所给时间的小时,后者返回所给时间的分钟。
8.MONTHNAME(date)函数:返回date的英文月份名称。
9.DATE_FORMAT(date,fmt)函数:按字符串fmt格式化日期date值,此函数能够按指定的格式显示日期。
10.DATE_ADD(date INTERVAL expr
type)函数:返回与所给日期date相差INTERVAL时间段的日期。备注:INTERVAL是关键字
expr是一个表达式,对应后面的type间隔类型
11.DATEDIFF(date1,date2)函数:用来计算两个日期之间相差的天数。
流程函数
1.IF(value,t,f) 如果value为真,返回t;否则返回f;
2.IFNULL(value1,value2) 如果value1不为空,返回value1;否则返回value2
3.CASE WHEN[value1]THEN[result1]…ELSE[default]END
如果value1是真,返回result1,否则返回default
4.CASE[expr]WHEN[value1]THEN[result1]…ELSE[default]END
如果expr等于value1,返回result1;否则返回default
其他常用函数
1.DATABASE() 返回当前数据库名
2.VERSION() 返回当前数据库版本
3.USER() 返回当前登录用户名
4.INET_ATON(IP) 返回IP地址的数字表示
5.PASSWORD(str) 返回字符串str的加密版本
6.MD5() 返回字符串str的MD5值

 

本文内容

  • 日期函数
  • 附录
  • 修改记录

 

日期函数

日期函数操作日期值(DATE 类型的字段),时间戳值(TIMESTAMP、TIMESTAMP
WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE
类型的字段)和间隔值(INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO
MONTH)。

大多数日期函数,如 ADD_MONTHS、CURRENT_DATE、LAST_DAY、NEW_TIME 和
NEXT_DAY,是为了 Oracle DATE
类型设计。若你提供一个时间戳值作为它们的参数,则 Oracle
数据库在内部把输入类型转换成一个 DATE 值,并返回。当返回一个 number
值时,MONTHS_BETWEEN 函数会抛出异常;ROUND 和 TRUNC
函数不接受时间戳或间隔值。

剩下的日期函数是为了接受三种日期类型(如 date、timestamp 和
interval)设计的,并返回这些类型的一个值。

返回系统日期信息的所有日期函数,如
SYSDATE、SYSTIMESTAMP、CURRENT_TIMESTAMP 等等,对于每个SQL
语句,不管这些语句被引用多少次,都会重新计算一次。

ADD_MONTHS

CURRENT_DATE

CURRENT_TIMESTAMP

DBTIMEZONE

EXTRACT (datetime)

FROM_TZ

LAST_DAY

LOCALTIMESTAMP

MONTHS_BETWEEN

NEW_TIME

NEXT_DAY

NUMTODSINTERVAL

NUMTOYMINTERVAL

ROUND (date)

SESSIONTIMEZONE

SYS_EXTRACT_UTC

SYSDATE

SYSTIMESTAMP

TO_CHAR (datetime)

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_DSINTERVAL

TO_YMINTERVAL

TRUNC (date)

TZ_OFFSET

 

ADD_MONTHS(date,integer)

ADD_MONTHS 返回日期 date 加整数月 integer
后的日期。月是由会话参数 NLS_CALENDAR 定义的。

date 参数可以是日期值,或是任何可以隐式转换成 DATE
类型的值。integer
参数可以是整数,或是任何可以隐式转换成整数的值。不管参数 date
的类型如何,返回类型总是 DATE

若 date 是月的最后一天,或结果月的天数比 date
少,则结果的天是结果月的最后一天。否则,与 date 的天相同。

示例 1:演示 employees 表 hire_date 字段加一个月。

SQL> SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') "Current month",

  2         TO_CHAR(ADD_MONTHS(hire_date, 1), 'YYYY-MM-DD') "Next month",

  3         TO_CHAR(ADD_MONTHS(TO_DATE('2012-2-29', 'YYYY-MM-DD'), 1),

  4                 'YYYY-MM-DD') "Fewer Days"

  5    FROM employees

  6   WHERE last_name = 'Baer';



Current month Next month Fewer Days

------------- ---------- ----------

1994-06-07    1994-07-07 2012-03-31



SQL> 

 

CURRENT_DATE

CURRENT_DATE 返回会话时区的当前日期,值为 DATE
类型阳历(Gregorian)日期值。

示例 2:演示 CURRENT_DATE。

SQL> 

SQL> ALTER SESSION SET TIME_ZONE = '-5:0';



Session altered

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';



Session altered

SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;



SESSIONTIMEZONE      CURRENT_DATE

-------------------- --------------------

-05:00               2012-7-31 8:22:24

SQL> ALTER SESSION SET TIME_ZONE = '-8:0';



Session altered

SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;



SESSIONTIMEZONE      CURRENT_DATE

-------------------- --------------------

-08:00               2012-7-31 5:22:24



SQL> 

 

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP(precision)

CURRENT_TIMESTAMP 返回会话时区的当前日期和时间,值为 TIMESTAMP
WITH TIME ZONE
类型。时区偏移反应 SQL 会话的当前的本地时间。若省略
precision,则默认为 6。该函数与 LOCALTIMESTAMP
的不同是,CURRENT_TIMESTAMP 返回 TIMESTAMP WITH TIME ZONE
值,而 LOCALTIMESTAMP 则返回 TIMESTAMP 值。

可选的参数 precision 指定了返回时间值的小数秒精度。

示例 3:演示 CURRENT_TIMESTAMP 对会话时区敏感。

SQL> ALTER SESSION SET TIME_ZONE = '-5:0';



Session altered

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';



Session altered

SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;



SESSIONTIMEZONE      CURRENT_TIMESTAMP

-------------------- ----------------------------------------

-05:00               31-7月 -12 09.17.58.046000 上午 -05:00



SQL> 

SQL> ALTER SESSION SET TIME_ZONE = '-8:0';



Session altered

SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;



SESSIONTIMEZONE      CURRENT_TIMESTAMP

-------------------- ----------------------------------------

-08:00               31-7月 -12 06.21.11.500000 上午 -08:00



SQL> 

当使用带格式掩码的
CURRENT_TIMESTAMP,注意格式掩码匹配函数的返回值。例如,创建下表:

CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);

下面 INSERT 语句会失败,因为掩码没有包含 CURRENT_TIMESTAP
函数返回类型的 TIME ZONE 部分:

INSERT INTO current_test

VALUES

  (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

下面语句使用了正确的格式掩码:

INSERT INTO current_test

VALUES

  (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));

 

DBTIMEZONE

DBTIMEZONE 返回数据库时区值。返回类型是时区偏移(字符类型,格式为
‘[+|-]TZH:TZM’),或时区区域名称,这依赖于用户在最近 CREATE
DATABASE
ALTER DATABASE 语句指定数据库时区值。

示例 4:演示 DBTIMEZONE。假设数据库时区设置为 UTC 时区。

SQL> SELECT DBTIMEZONE FROM DUAL;

 

DBTIME

------

+00:00

 

SQL>

 

EXTRACT (datetime)

EXTRACT 从一个日期或时间间隔表达式提取并返回指定日期值。

示例 5:演示从指定日期提取年、月、日,以及时、分、秒。

SQL> select EXTRACT(YEAR FROM DATE '2012-07-31') "EXTRACT YEAR",

  2         EXTRACT(MONTH FROM DATE '2012-07-31') "EXTRACT MONTH",

  3         EXTRACT(DAY FROM DATE '2012-07-31') "EXTRACT DAY",

  4         EXTRACT(HOUR FROM TIMESTAMP '2012-07-31 08:20:40') "EXTRACT HOUR",

  5         EXTRACT(MINUTE FROM TIMESTAMP '2012-07-31 08:20:40') "EXTRACT MINUTE",

  6         EXTRACT(SECOND FROM TIMESTAMP '2012-07-31 08:20:40') "EXTRACT SECOND"

  7    FROM DUAL;

 

EXTRACT YEAR EXTRACT MONTH EXTRACT DAY EXTRACT HOUR EXTRACT MINUTE EXTRACT SECOND

------------ ------------- ----------- ------------ -------------- --------------

        2012             7          31            8             20             40

 

 

SQL>

示例 6:演示每个月入职的员工数量。其实,这个演示用 Oracle OE Schema
下的 orders 订单表较好,可惜我没有这个表。

SQL> SELECT EXTRACT(month FROM hire_date) "Month",

  2         COUNT(hire_date) "No. of Hire"

  3    FROM employees

  4   GROUP BY EXTRACT(month FROM hire_date)

  5   ORDER BY "No. of Hire" DESC;



     Month No. of Hire

---------- -----------

         3          17

         1          14

         2          13

         6          11

         8           9

         4           7

        12           7

         7           7

         5           6

        10           6

         9           5

        11           5



12 rows selected



SQL> 

 

FROM_TZ(timestamp_value,time_zone_value)

FROM_TZ 把时间戳值和时区值转换成 TIMESTAMP WITH TIME ZONE
值。

time_zone_value 是格式为 ‘TZH:TZM’ 的字符串,或返回 TZR
格式字符串的字符表达式,TZD 格式可选。

示例 7:演示返回一个 TIMESTAMP WITH TIME ZONE  的时间戳值。

SQL> SELECT FROM_TZ(TIMESTAMP '2012-07-31 08:00:00', '3:00') FROM DUAL;



FROM_TZ(TIMESTAMP'2012-07-3108

--------------------------------------------------------------------------------

31-7月 -12 08.00.00.000000000 上午 +03:00



SQL> 

 

LAST_DAY(date)

LAST_DAY 返回月的最后一天的日期。月的最后一天是由会话参数
NLS_CALENDAR 定义的。返回类型总是 DATE,而不是参数 date
的数据类型。

示例 8:演示当前月剩多少天。

SQL> SELECT SYSDATE,

  2         LAST_DAY(SYSDATE) "Last",

  3         LAST_DAY(SYSDATE) - SYSDATE "Days Left"

  4    FROM DUAL;



SYSDATE     Last         Days Left

----------- ----------- ----------

2012-8-1 22 2012-8-31 2         30



SQL> 

 

LOCALTIMESTAMP
LOCALTIMESTAMP(timestamp_precision)

LOCALTIMESTAMP 返回会话时区中当前的日期和时间,类型为
TIMESTAMPLOCALTIMESTAMP 返回 TIMESTAMP 值,而
CURRENT_TIMESTAMP 返回 TIMESTAMP WITH TIME ZONE 值。

参数 timestamp_precision 可选,指定返回小数秒精度的时间值。

示例 9:演示 LOCALTIMESTAMP 和 CURRENT_TIMESTAMP 之间的区别。

SQL> ALTER SESSION SET TIME_ZONE = '-5:00';



Session altered

SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;



CURRENT_TIMESTAMP                        LOCALTIMESTAMP

---------------------------------------- ----------------------------------------

01-8月 -12 10.06.21.203000 上午 -05:00   01-8月 -12 10.06.21.203000 上午



SQL> 

SQL> ALTER SESSION SET TIME_ZONE = '-8:00';



Session altered

SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;



CURRENT_TIMESTAMP                        LOCALTIMESTAMP

---------------------------------------- ----------------------------------------

01-8月 -12 07.06.27.281000 上午 -08:00   01-8月 -12 07.06.27.281000 上午



SQL> 

当使用带格式掩码的 LOCALTIMESTAMP
时,注意格式掩码匹配函数的返回值。例如,创建下表:

CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE);

下面 INSERT 语句会失败,因为掩码没有包含 LOCALTIMESTAMP
函数返回类型的 TIME ZONE 部分:

INSERT INTO local_test

VALUES

  (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));

下面语句使用了正确的格式掩码:

INSERT INTO local_test

VALUES

  (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

 

MONTHS_BETWEEN(date1,date2)

MONTHS_BETWEEN 返回日期 date1 和 date2
之间有多少月。月是由会话参数 NLS_CALENDAR 定义。若 date1 比 date2
晚,则结果为正值。若 date1 比 date2 早,则结果为负值。若 date1 和 date2
日期相同,或都是月的最后一天,则结果总是整数。否则,Oracle
数据库基于每月31天计算小数部分,考虑 date1 和 date2 的时间差。

示例 10:演示计算两个日期之间有多少月。

SQL> SELECT MONTHS_BETWEEN(TO_DATE('02-02-1995', 'MM-DD-YYYY'),

  2                        TO_DATE('01-01-1995', 'MM-DD-YYYY')) "Months"

  3    FROM DUAL;



    Months

----------

1.03225806



SQL> 

 

NEW_TIME(date,timezone1,timezone2)

NEW_TIME 日期和时间的时区转换。使用该函数前,你必须设置回话参数
NLS_DATE_FORMAT 为24小时制,返回类型始终为 DATE,而不是参数 date
的类型。

备注:

This function takes as input only a limited number of time zones. You
can have access to a much greater number of time zones by combining the
FROM_TZ function and the datetime expression.

参数 timezone1 和 timezone2 可以是下面任意文本:

  • AST, ADT: 大西洋标准或夏令时(Atlantic Standard or Daylight Time)
  • BST, BDT: 白令海标准或夏令时(Bering Standard or Daylight Time)
  • CST, CDT: 中央标准或夏令时(Central Standard or Daylight Time)
  • EST, EDT: 东部标准或夏令时(Eastern Standard or Daylight Time)
  • GMT: 格林威治时间(Greenwich Mean Time)
  • HST, HDT: 阿拉斯加 – 夏威夷标准时间或夏令时间(Alaska-Hawaii
    Standard Time or Daylight Time)
  • MST, MDT: 山地标准或夏令时(Mountain Standard or Daylight Time)
  • NST: 纽芬兰标准时间(Newfoundland Standard Time)
  • PST, PDT: 太平洋标准或夏令时(Pacific Standard or Daylight Time)
  • YST, YDT: 育空地区标准或夏令时(Yukon Standard or Daylight Time)

示例 11:演示返回等价于太平洋标准时间(Pacific Standard time
)的大西洋标准时间(Atlantic Standard time)。

SQL> ALTER SESSION SET NLS_DATE_FORMAT =

  2     'YYYY-MM-DD HH24:MI:SS';



Session altered

SQL> SELECT NEW_TIME(TO_DATE('2012-8-3 01:23:45', 'YYYY-MM-DD HH24:MI:SS'),

  2                  'AST',

  3                  'PST') "New Date and Time"

  4    FROM DUAL;



New Date and Time

-----------------

2012-8-2 21:23:45



SQL> 

 

NEXT_DAY(date,char)

NEXT_DAY 返回指定日期后,第一个指定星期的日期。返回类型总是
DATE。参数 char
必须是会话日期语言的周,周的全名或缩写。返回值具有与参数相同的时、分、秒。

示例 12:演示 2012 年 2 月 2 日下个星期二的日期。

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE =American;



Session altered



SQL> SELECT NEXT_DAY('02-FEB-2012', 'TUESDAY') "NEXT DAY" FROM DUAL;



NEXT DAY

-----------

2012-2-7



SQL>