SQL/PLSQL:日期函数总结

日期函数

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

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

select current_date from dual; 

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

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

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

3）date  date：相差的天数

计算90号部门的雇员在公司工作的总周数 

select last_name,(sysdate - hire_date)/7 weeks 

  from employees where department_id=90; 

例1:给当前日期分别加减3天 

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

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

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

  from dual;

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

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;

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;

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

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;

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;