[置顶] SQL/PLSQL:日期函数总结

TO_DATE格式(以当前时间: 2017年2月9日11:25:38 为例) 1.年:

yy	两位年	显示值:17
yyy	三位年	显示值:017
yyyy	四位年	显示值:2017

2.月:

mm

两位月

显示值:11

3.日:

dd	当月第几天	显示值:09
d	当周第几天 1~7	周日=1,周六=7
dy	星期几 Mon~Sun
day	星期几 Monday~Sunday
ddd	一年中的第几天

4.时

hh24

24小时制

显示值:11

5.分

mi

60进制

显示值:25

6.秒

ss

60进制

显示值:38

---

日期函数

---

1、sysdate：得到数据库服务器的当前日期和时间 

2、current_date:得到客户端的当前日期和时间 

因为数据库把日期作为数字存储，因此可以对日期进行加减运算,单位是天。 

1 ) date + n : 加减几天，n可以是负的 

2） date + n/24 : 加减几个小时 

3） date - date : 相差的天数

例1:给当前日期分别加减3天 sysdate +/- 3

例2:给当前日期加1个小时

select to_char(sysdate + 1/24,'YYYYMMDD HH24:MI:SS') 

  from dual;

---

MONTHS_BETWEEN(date1, date2) 返回两个日期差几个月。 记住是 前date1 - 后date2例如:查询雇员在公司工作的总月数select last_name,months_between(sysdate,hire_date)   from employees; 

---

ADD_MONTHS(date, n): 
给日期加减N个月。N可以为负数, 加减12个月就是一年啦
例如:
SELECT ADD_months(SYSDATE,-12)--一年前的今天
       ADD_months(SYSDATE,+24) --两年后的今天  FROM dual; 


next_day(date,'char'):  
找到从date开始的下一个星期几的日期。char表示星期几 

NEXT_DAY(date,6)  -->下个周五 (这里6，代表星期5，因为美国日期是从星期天开始的，所以1代表的是星期天，2代表星期一，以此类推)

例如:

查找今天之后的下一个星期一是几月几号？ 注意字符集

select next_day(sysdate,'星期一') from dual; 

ORA01846: 周中的日无效 

select next_day(sysdate,'monday') from dual; 

中文情况下"星期天"不识别, "星期日"才识别

ROUND(date[,'fmt']):  

进位规则:秒=30,分=30,时=12,日=16,月=7; 超过上面分割线就向前一位进1

TRUNC(date [, 'fmt']):  （常用,后面不接参数就是将日期的时分秒去掉,注意和round区分）

       

例如 :

selec  trunc(sysdate,'month'), --按月进行截断，直接截断到给定日期的本月的1号 
    trunc(sysdate,'year')  --按年进行截断，直接截断到给定日期的本年月的1月1号 
 from dual;

   

---

时间 & 时区

---

时区概念：地球分24个时区，东西各12个。一个时区代表1个小时。时区值通常以绝对偏移量格式来表示：带正负号的小时：分钟。
东时区为正的，西时区为负的。时区一旦确定了，日期时间函数的返回值就参照该时区来返回。

1.查看数据库,会话时区:

SELECT DBTIMEZONE,      --查看数据库所在时区       SESSIONTIMEZONE  --查看会话所在时区  FROM DUAL;

注意:

DBA通过指定手工建库时CREATE DATABASE 语句的SET TIME_ZONE 子句

来设置数据库的默认时区。如果省略，那么默认数据库时区是操作系统时区。

如果操作系统的时区格式是oracle不支持的，那么就把数据库的时区设为0时区。官方推荐数据库的时区都使用0时区。

ALTER SESSION语句不能改变数据库时区。

2.设置会话时区为西五区(美国东部时间)

alter session set time_zone = '-05:00'; --绝对偏移量,负为西

3.设置本会话使用数据库的时区

alter session set time_zone = DBTIMEZONE;

4.把时区设置成本地

alter session set time_zone = local;  --不同操作系统,可能不支持

5.时区设置成某地域的时区

alter session set time_zone = 'American/New_York'; --不同操作系统,可能不支持

---

和客户端有关的日期时间函数：3个

current_date   current_timestamp  localtimestamp

---

三个都返回客户端的当前日期和时间，区别在于值的数据类型不一样

sessiontimezone：该函数返回客户端的时区设置

查看系统各个时间函数:

select sessiontimezone ,       current_date ,        current_timestamp ,        localtimestamp   from dual; ---(注意三个时间函数的精度)

---

数据库的时区

返回数据库的时区: 0时区

select dbtimezone from dual;

DBA通过指定手工建库时CREATE DATABASE 语句的SET TIME_ZONE 子句

来设置数据库的默认时区。如果省略，那么默认数据库时区是

操作系统时区。

如果操作系统的时区格式是oracle不支持的，那么就把数据库的时区设为0时区。官方推荐数据库的时区都使用0时区。

ALTER SESSION语句不能改变数据库时区。

TIMESTAMP类型：时间戳类型。3种

-TIMESTAMP数据类型是DATE数据类型的扩展：

1）TIMESTAMP (fractional_seconds_ precision)     

2）TIMESTAMP (fractional_seconds_precision) WITH 

     TIME ZONE     带时区的时间戳

3）TIMESTAMP (fractional_seconds_precision) WITH 

     LOCAL TIME ZONE     带本地时区的时间戳

其中：fractional_seconds_precision小数秒精度取

值范围是0-9。默认是6(微秒 ms)

特别注意：最后一种timestamp类型的列，它的值在保存到表中时，会按照数据库的时区进行自动转换；当从表中把该类型的列值取出来时，有按照客户端的时区做自动转换。

前面两种timestamp类型的列值不会做这样的转换。

例子:

--创建订单表 
CREATE TABLE web_orders 
  ( 
     order_date    TIMESTAMP WITH TIME ZONE,         --订货时间使用 带时区的时间戳
     delivery_time TIMESTAMP WITH local TIME ZONE   --送货时间使用 带本地时区的时间戳
  ); 

--美国客户插入订单 
INSERT INTO web_orders 
VALUES     (current_date, 
            current_timestamp + 2); 
COMMIT; 

--美国客户查询。看到正确的日期（西五区的时间） 
SELECT * FROM   web_orders; 

--在sqlplus中，国内的物流哥们查询，看到正确的时间（东八区的时间） 
SELECT * FROM   web_orders;

---

时间间隔类型

---

INTERVAL数据类型用来存储两个日期值之间的差值。有两类INTERVAL： 

INTERVAL YEAR(year_precision) TO MONTH

INTERVAL DAY(day_precision) TO SECOND(fractional_seconds_precision)

year_precision                    是YEAR字段的精度，取值范围0—9，默认为2.

fractional_seconds_precision 是小数秒的精度，取值范围0—9，默认为6. 

day_precision                    是DAY字段的最大值（取值范围0—9，默认为2. ）

l=eg:  

1、使用年到月的间隔

create table warranty(

  prod_id number,产品编号

  warranty_tiem interval year(3) to month       --担保时间,year(3)-年的部分最少有三位

);

注意间隔类型的字面量写法：ansi语法

insert into warranty values(123,interval '8' month);             --

SQL 错误: ORA01873: 间隔的前导精度太小

insert into warranty values(456,interval '200' year);      --必须指定年的精度有3位

insert into warranty values(456,interval '200' year(3));   --正确写法

注意间隔类型的字面量写法,oracle简单语法：'年-月'

insert into warranty values(789,'200-11');   200年 零 11个月简单写法

2、使用天到秒的间隔

create table lab(

  exp_id number,

  test_time interval day to second  --day不指定精度默认位2 秒精度默认为6

);

实验123的测试时间是90天一次

insert into lab values(123,'90 00:00:00');

insert into lab values(456, interval '06 03:30:16' day to second);

select * from lab;

间隔类型的使用常用于日期的加减运算中

---

其它日期时间函数

---

1.EXTRACT 函数:从给定的日期中抽取出特定的部分

extract(.... from .....)

SELECT  EXTRACT ([YEAR] [MONTH][DAY] 

                  [HOUR] [MINUTE][SECOND]

 FROM [datetime_value_expression] |

   [interval_value_expression]);

例如:

select sysdate,extract(year from sysdate)

  from dual;

查询雇员入职的月份

select hire_date,extract(month from hire_date)

  from employees;

  

---

2、tz_offset()

将命名地区形式的时区转换成时区的绝对偏移量

select tz_offset('Asia/Shanghai'),

       tz_offset('Canada/Yukon')

  from dual;     

要得到合法时区名的列表，可以查询V$TIMEZONE_NAMES动态性能视图。

SELECT * FROM V$TIMEZONE_NAMES;

---

3、to_timestamp(char,'fmt')

将字符串以给定的日期格式模型转换成时间戳

select to_timestamp('20071010 15:25:00','YYYYMMDD HH24:MI:SS')

  from dual;

4、to_yminterval('year-month')

将字符串转成成年到月的间隔

将雇员的入职日期加上1年2个月

select hire_date,

      hire_date + to_yminterval('01-02')

 from employees;

 

5、to_dsinterval('day hh:mi:ss')

将字符串转成成天到秒的间隔 

将雇员的入职日期加上100天10个小时

select to_char(hire_date,'YYYYMMDD HH24:MI:SS'),

      to_char(hire_date + to_dsinterval('100 10:00:00'),

              'YYYYMMDD HH24:MI:SS')

 from employees;

 

---

MONTHS_BETWEEN(date1, date2) 返回两个日期差几个月。 记住是 前 - 后

---

ADD_MONTHS(date, n): 
给日期加减几个月。N是整数可以为负数
`注:加减12个月就是1年!
SELECT ADD_months(SYSDATE,-12) FROM dual;    --一年前的今天
SELECT ADD_months(SYSDATE,+24) FROM dual;   --两年后的今天
next_day(date,'char'):  
找到从date开始的下一个星期几的日期。char表示星期几 
上面等价于:NEXT_DAY(date,'星期一')
例如:

例如:查询雇员在公司工作的总月数select last_name,months_between(sysdate,hire_date) from employees; 

NEXT_DAY(date,6)  -->下个周五 (这里6，代表星期5，因为美国日期是从星期天开始的，所以1代表的是星期天，2代表星期一，以此类推)

查找今天之后的下一个星期一是几月几号？ 注意字符集

select next_day(sysdate,'星期一') from dual; 

ORA01846: 周中的日无效 

select next_day(sysdate,'monday') from dual; 

ROUND(date[,'fmt']):  

进位规则:秒=30,分=30,时=12,日=16,月=7; 超过上面分割线就向前一位进1

TRUNC(date [, 'fmt']):  （常用,后面不接参数就是将日期的时分秒去掉）

       

例如 :

规则：按月进行截断，直接截断到给定日期的本月的1号 

            按年进行截断，直接截断到给定日期的本年月的1月1号  

select sysdate,trunc(sysdate,'month'), trunc(sysdate,'year')  from dual;

   

---

---

时间 & 时区

---

---

时区概念：地球分24个时区，东西各12个。一个时区代表1个小时。时区值通常以绝对偏移量格式来表示：带正负号的小时：分钟。
东时区为正的，西时区为负的。时区一旦确定了，日期时间函数的返回值就参照该时区来返回。

oracle中，时区分为客户端的和服务器的两种，分别设置。

设置客户端的时区使用会话参数:time_zone

1.查看数据库,会话时区:

select dbtimezone from dual;

select sessiontimezone from dual;

2.设置会话时区为西五区(美国东部时间)

alter session set time_zone = '-05:00'; (绝对偏移量,负为西)

3.设置本会话使用数据库的时区

alter session set time_zone = dbtimezone；

4.把时区设置成本地

alter session set time_zone = local;  -- (操作系统,可能不支持)

5.时区设置成某地域的时区

alter session set time_zone = 'American/New_York'; -- (操作系统,可能不支持)

---

和客户端有关的日期时间函数：3个

current_date   current_timestamp  localtimestamp

---

三个都返回客户端的当前日期和时间，区别在于值的数据类型不一样

sessiontimezone：该函数返回客户端的时区设置

查看系统各个时间函数:

select sessiontimezone ,

       current_date , 

       current_timestamp , 

       localtimestamp 

  from dual; ---(注意三个时间函数的精度)

---

数据库的时区

返回数据库的时区: 0时区

select dbtimezone from dual;

DBA通过指定手工建库时CREATE DATABASE 语句的SET TIME_ZONE 子句

来设置数据库的默认时区。如果省略，那么默认数据库时区是

操作系统时区。

如果操作系统的时区格式是oracle不支持的，那么就把数据库的时区设为0时区。官方推荐数据库的时区都使用0时区。

ALTER SESSION语句不能改变数据库时区。

TIMESTAMP类型：时间戳类型。3种

-TIMESTAMP数据类型是DATE数据类型的扩展：

1）TIMESTAMP (fractional_seconds_ precision)     

2）TIMESTAMP (fractional_seconds_precision) WITH 

     TIME ZONE     带时区的时间戳

3）TIMESTAMP (fractional_seconds_precision) WITH 

     LOCAL TIME ZONE     带本地时区的时间戳

其中：fractional_seconds_precision小数秒精度取

值范围是0-9。默认是6(微秒 ms)

特别注意：最后一种timestamp类型的列，它的值在保存到表中时，会按照数据库的时区进行自动转换；当从表中把该类型的列值取出来时，有按照客户端的时区做自动转换。

前面两种timestamp类型的列值不会做这样的转换。

例子:

--创建订单表 
CREATE TABLE web_orders 
  ( 
     order_date    TIMESTAMP WITH TIME ZONE,         --订货时间使用 带时区的时间戳
     delivery_time TIMESTAMP WITH local TIME ZONE   --送货时间使用 带本地时区的时间戳
  ); 

--美国客户插入订单 
INSERT INTO web_orders 
VALUES     (current_date, 
            current_timestamp + 2); 
COMMIT; 

--美国客户查询。看到正确的日期（西五区的时间） 
SELECT * FROM   web_orders; 

--在sqlplus中，国内的物流哥们查询，看到正确的时间（东八区的时间） 
SELECT * FROM   web_orders;

---

时间间隔类型

---

INTERVAL数据类型用来存储两个日期值之间的差值。有两类INTERVAL： 

INTERVAL YEAR(year_precision) TO MONTH

INTERVAL DAY(day_precision) TO SECOND(fractional_seconds_precision)

year_precision                    是YEAR字段的精度，取值范围0—9，默认为2.

fractional_seconds_precision 是小数秒的精度，取值范围0—9，默认为6. 

day_precision                    是DAY字段的最大值（取值范围0—9，默认为2. ）

l=eg:  

1、使用年到月的间隔

create table warranty(

  prod_id number,产品编号

  warranty_tiem interval year(3) to month       --担保时间,year(3)-年的部分最少有三位

);

注意间隔类型的字面量写法：ansi语法

insert into warranty values(123,interval '8' month);             --

SQL 错误: ORA01873: 间隔的前导精度太小

insert into warranty values(456,interval '200' year);      --必须指定年的精度有3位

insert into warranty values(456,interval '200' year(3));   --正确写法

注意间隔类型的字面量写法,oracle简单语法：'年-月'

insert into warranty values(789,'200-11');   200年 零 11个月简单写法

2、使用天到秒的间隔

create table lab(

  exp_id number,

  test_time interval day to second  --day不指定精度默认位2 秒精度默认为6

);

实验123的测试时间是90天一次

insert into lab values(123,'90 00:00:00');

insert into lab values(456, interval '06 03:30:16' day to second);

select * from lab;

间隔类型的使用常用于日期的加减运算中

---

其它日期时间函数

---

1.EXTRACT 函数:从给定的日期中抽取出特定的部分

extract(.... from .....)

SELECT  EXTRACT ([YEAR] [MONTH][DAY] 

                  [HOUR] [MINUTE][SECOND]

 FROM [datetime_value_expression] |

   [interval_value_expression]);

例如:

select sysdate,extract(year from sysdate)

  from dual;

查询雇员入职的月份

select hire_date,extract(month from hire_date)

  from employees;

  

---

2、tz_offset()

将命名地区形式的时区转换成时区的绝对偏移量

select tz_offset('Asia/Shanghai'),

       tz_offset('Canada/Yukon')

  from dual;     

要得到合法时区名的列表，可以查询V$TIMEZONE_NAMES动态性能视图。

SELECT * FROM V$TIMEZONE_NAMES;

---

3、to_timestamp(char,'fmt')

将字符串以给定的日期格式模型转换成时间戳

select to_timestamp('20071010 15:25:00','YYYYMMDD HH24:MI:SS')

  from dual;

4、to_yminterval('year-month')

将字符串转成成年到月的间隔

将雇员的入职日期加上1年2个月

select hire_date,

      hire_date + to_yminterval('01-02')

 from employees;

 

5、to_dsinterval('day hh:mi:ss')

将字符串转成成天到秒的间隔 

将雇员的入职日期加上100天10个小时

select to_char(hire_date,'YYYYMMDD HH24:MI:SS'),

      to_char(hire_date + to_dsinterval('100 10:00:00'),

              'YYYYMMDD HH24:MI:SS')

 from employees;