1. date常规的操作(请注意,常规不代表oracle提供date操作的全部)
1.1. date转换为字符串
我们总是想看到日期和时间是什么样的(希望按照我们指定的格式显示), 因此总是需要把它to_char
正如上面的例子, 除了oracle提供的Date Format, 我们可以通过双引号添加自己希望看到的间隔符,
当间隔符是oracle可以自动识别的符号的时, 可以省去双引号, 如
但是这里还是建议分割符都用双引号, 提高可读性,兼容性和易维护性.
这里要提醒大家的是, 自定义的分割符不要太长, 如果太长, oracle会报错
(ORA-01801: date format is too long for internal buffer), 有兴趣可以试试
1.2. date的默认格式
最近总是看到有人会直接用日期字符串插入到date字段中的sql, 我的建议是最好不要,
因为这里面涉及到一个oracle隐式转换的问题,
oracle的答复是允许隐式转换, 但并不推荐使用
这里, date的隐式转换是依赖于默认的日期格式nls_date_format(可能还要考虑nls_language),
下面我们来看下面的代码
上面三次插入数据, 只有最后一次才成功, 因为它的格式符合隐式转换要求的格式, 但是大家觉得这样好吗?
不同的oracle环境, 这些参数都是不一样的, 如果用隐式转换, 显然带来的是隐患, 更直接一点就是错误.
不过, 这里我要说一下, 是可以通过alter session set NLS_LANGUAGE='&date_format'来改变当前session的日期格式,
或是把oracle的默认日期格式改掉, 但是大家觉得这样好吗?
假如你没有权利修改oracle的参数或是不方便来修改参数呢?还要坚持用隐式转换吗?
1.3. to_char(date,'date_format'), oracle提供的其他date_format我这里就不赘述了, 这个是to_char函数使用的基础.
如果觉得忘却了, 可以参见oracle doc(%ora10g doc%/server.102/b14200.pdf, %ora9i doc%/server.920/a96540.pdf),
学会看oracle doc是很重要的
2. date 其他操作
2.1 关于日期的比较
日期是可以直接做比较的, 比如 sysdate>to_date('20040101','yyyymmdd')
2.2 日期的加减
日期在加减的时候, 我们可以把日期看成是数字, 整数部分代表年月日, 小数部分代表时分秒
2.3 日期时间的特定加减
现在有朋友提出需要单独的增加几年, 或几月, 或几个小时, 或几分钟, 几秒
那么最好通过 datetime + interval 'n' year/month/hour/munite/second 来实现
也许会有朋友说, 加年可以通过 add_months(datetime, n*12), 加月 add_months(datetime, n),
加小时 datetime+n/24 等等, 我个人的看法是可以, 但是不好, 显然用interval的可读性和易维护性要比后面的方法要好的多,
而且在不出现除法的可能性下最好不要出现诸如 n/24, n/(24*60)之类的增加小时或分的操作
interval 除了可以单独增加某个时间单位的间隔外, 还可以是组合的日期
如: interval 'expr' day/hour/munite/second to day/hour/munite/second
interval 'expr' year/month to year/month
1.1. date转换为字符串
我们总是想看到日期和时间是什么样的(希望按照我们指定的格式显示), 因此总是需要把它to_char
代码:
SQL> select to_char(sysdate,'yyyy"(year)"mm"(month)"dd"(day) "hh24"(hour)"mi"(minute)"ss"(second)"') as mydate from dual;
MYDATE
-------------------------------------------------------
2006(year)01(month)04(day) 10(hour)58(minute)27(second)
正如上面的例子, 除了oracle提供的Date Format, 我们可以通过双引号添加自己希望看到的间隔符,
当间隔符是oracle可以自动识别的符号的时, 可以省去双引号, 如
代码:
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') as mydate from dual;
MYDATE
-------------------
2006/01/04 11:03:
但是这里还是建议分割符都用双引号, 提高可读性,兼容性和易维护性.
这里要提醒大家的是, 自定义的分割符不要太长, 如果太长, oracle会报错
(ORA-01801: date format is too long for internal buffer), 有兴趣可以试试
1.2. date的默认格式
最近总是看到有人会直接用日期字符串插入到date字段中的sql, 我的建议是最好不要,
因为这里面涉及到一个oracle隐式转换的问题,
oracle的答复是允许隐式转换, 但并不推荐使用
这里, date的隐式转换是依赖于默认的日期格式nls_date_format(可能还要考虑nls_language),
下面我们来看下面的代码
代码:
--首先, 我们创建一个表
create table d (d date);
--然后我们看一下当前的nls_date_format和nls_language
SQL> select * from v$nls_parameters where parameter in ('NLS_DATE_FORMAT','NLS_LANGUAGE');
PARAMETER VALUE
------------------ --------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_DATE_FORMAT DD-MON-RR
--好了, 我们来利用这些, 来试试日期的隐式转换
--习惯性的日期格式'yyyy-mm-dd'
SQL> insert into d values('2006-01-01');
insert into d values('2006-01-01')
ORA-01861: literal does not match format string
--不考虑language的情况下来插入数据
SQL> insert into d values('01-jan-06');
insert into d values('01-jan-06')
ORA-01843: not a valid month
--NLS_LANGUAGE 和 NLS_DATE_FORMAT 都考虑的情况下
SQL> insert into d values('01-1月-06');
1 row inserted
SQL> commit;
上面三次插入数据, 只有最后一次才成功, 因为它的格式符合隐式转换要求的格式, 但是大家觉得这样好吗?
不同的oracle环境, 这些参数都是不一样的, 如果用隐式转换, 显然带来的是隐患, 更直接一点就是错误.
不过, 这里我要说一下, 是可以通过alter session set NLS_LANGUAGE='&date_format'来改变当前session的日期格式,
或是把oracle的默认日期格式改掉, 但是大家觉得这样好吗?
假如你没有权利修改oracle的参数或是不方便来修改参数呢?还要坚持用隐式转换吗?
1.3. to_char(date,'date_format'), oracle提供的其他date_format我这里就不赘述了, 这个是to_char函数使用的基础.
如果觉得忘却了, 可以参见oracle doc(%ora10g doc%/server.102/b14200.pdf, %ora9i doc%/server.920/a96540.pdf),
学会看oracle doc是很重要的
2. date 其他操作
2.1 关于日期的比较
日期是可以直接做比较的, 比如 sysdate>to_date('20040101','yyyymmdd')
2.2 日期的加减
日期在加减的时候, 我们可以把日期看成是数字, 整数部分代表年月日, 小数部分代表时分秒
代码:
如: 当前时间加2天 sysdate+2,
前3天半的时间 sysdate-3.5
两个日期相差多长时间 datetime1-datetime2 , 这里要注意, 得到的是一个Interval(看着是数字) , 不再
2.3 日期时间的特定加减
现在有朋友提出需要单独的增加几年, 或几月, 或几个小时, 或几分钟, 几秒
那么最好通过 datetime + interval 'n' year/month/hour/munite/second 来实现
也许会有朋友说, 加年可以通过 add_months(datetime, n*12), 加月 add_months(datetime, n),
加小时 datetime+n/24 等等, 我个人的看法是可以, 但是不好, 显然用interval的可读性和易维护性要比后面的方法要好的多,
而且在不出现除法的可能性下最好不要出现诸如 n/24, n/(24*60)之类的增加小时或分的操作
代码:
--创建表
SQL> create table test1(d date, c char);
Table created
--插入10条日期数据, 数据间隔为1个小时
SQL> insert into test1 select trunc(sysdate)+rownum/24,'h' from dual connect by level<=10;
10 rows inserted
--用interval如何实现
SQL> insert into test1 select trunc(sysdate)+ (interval '1' hour)*rownum, 'i' from dual connect by level<=10;
10 rows inserted
SQL> commit;
interval 除了可以单独增加某个时间单位的间隔外, 还可以是组合的日期
如: interval 'expr' day/hour/munite/second to day/hour/munite/second
interval 'expr' year/month to year/month
代码:
-- interval hour to second 示例
SQL> select to_char(sysdate, 'hh:mi:ss') as curr_date, to_char(sysdate+interval '1:20:30' hour to second, 'hh:mi:ss') as next_date from dual;
CURR_DATE NEXT_DATE
--------- ---------
03:45:30 05:06: