date_format函数与to_date函数,Mysql与Oracle的时间格式化函数使用对比
最近在做mysql的sql语句兼容oracle,其实就是换换函数。这期间大量遇到时间函数的问题,于是写一篇博客归纳总结一下。
date_format与to_date函数的基本使用
- Mysql的date_format函数
DATE_FORMAT(date,format)
//以不同格式显示日期和时间的函数
//date:要进行格式化的字段
//format:日期格式化的格式
DATE_FORMAT(?,'%Y-%m-%d %H:%i:%S')
// 格式化结果: YYYY-mm-dd HH:ii:SS
// (格式的含义是4位数的年份,01-12的月份数值,01-31的日期数值,24小时制,00-59的分钟数值,00-59的秒数值)
- 格式参数表(大小写有不同的含义)
格式 | 说明 |
---|---|
%Y | 年,4 位 |
%y | 年,2 位 |
%M | 月名(January,February…) |
%m | 月,数值(01-12) |
%D | 带有英文前缀的月中的天 |
%d | 天,数值(01-31) |
%H | 小时 (00-23) |
%h | 小时 (01-12) |
%i | 分钟,数值(00-59) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%X | 年,其中的星期日是周的第一天,4 位,与 %V 使用 |
%x | 年,其中的星期一是周的第一天,4 位,与 %v 使用 |
%V | 周 (01-53) 星期日是一周的第一天,与 %X 使用 |
%v | 周 (01-53) 星期一是一周的第一天,与 %x 使用 |
%I | 小时 (1-12) |
%k | 小时 (0-23) |
%U | 周 (00-53) 星期日是一周的第一天 |
%u | 周 (00-53) 星期一是一周的第一天 |
%W | 星期名(Monday,Tuesday…) |
%w | 周的天 (0=星期日, 6=星期六) |
%T | 时间, 24-小时 (hh:mm:ss) |
%r | 时间,12-小时(hh:mm:ss AM 或 PM) |
%b | 缩写月名(Jan,Feb…) |
%a | 缩写星期名(Mon,Tue,Wed…) |
%c | 月,数值(1-12) |
%e | 月的天,数值(1-31) |
%f | 微秒 |
%j | 年的天 (001-366) |
%p | AM 或 PM |
- Oracle的to_date函数
TO_DATE(date,format)
//以不同格式显示日期和时间的函数
//date:要进行格式化的字段
//format:日期格式化的格式
TO_DATE(?,'yyyy-MM-dd HH24:mi:ss')
// (格式的含义是4位数的年份,01-12的月份数值,01-31的日期数值,24小时制,00-59的分钟数值,00-59的秒数值)
- 格式参数表(大小写含义相同)
格式 | 说明 |
---|---|
YYYY | 四位年份 |
YYY,YY,Y | 年份的最后三位,两位,一位 |
MM | 月 (01-12) |
Mon | 月份的英文简写 |
Month | 月份的全名 |
DD | 天(01-31) |
DDD | 年中的第几天 (001-366) |
D | 一周中的星期几 |
DAY | 天的名字,使用空格填充到9个字符 |
DY | 天的简写名 |
HH | 小时,按12小时计 |
HH24 | 24 小时,按24小时计 |
MI | 分 |
SS | 秒 |
W | 该月的第几个星期 |
WW | 年中的第几个星期 |
IW | ISO标准的年中的第几周 |
IYYY | ISO标准的四位年份 |
实际上,mysql与oracle的时间函数只有格式符的不同,所以总结一下常用的格式如下
常用的时间格式
MySQL | Oracle | |
---|---|---|
年4位,01-12月,01-31天 | %Y-%m-%d | YYYY-MM-DD |
年4位,01-12月,01-31天,12小时制,00-59分,00-59秒 | %Y-%m-%d %h:%i:%S | YYYY-MM-DD hh:mi:ss |
年4位,01-12月,01-31天,24小时制,00-59分,00-59秒 | %Y-%m-%d %H:%i:%S | YYYY-MM-DD hh24:mi:ss |
待续… |
注意!
工作中经常见到实体类的时间的数据类型不符合sql查询的结果集字段,即“无法从结果集获取xx列的值”的error,所以在找到正确的时间格式符后,仍然不能掉以轻心,还需要检查一下实体类的字段是什么数据类型的
- 情况一
// 你的实体类中的时间字段
private Date datetime;
这种情况是大多数正常情况,此时在sql查询的结果集中需要时间类型的字段
date_format(datetime,format)
to_date(datetime,format)
- 情况二
private String datetime;
这种情况属于部分特殊情况,此时在sql查询的结果集中需要字符串类型的字段
// mysql无影响
date_format(datetime,format)
// oracle需要使用to_char函数将结果转换成字符串
to_char(to_date(datetime,format))
- 其他情况
此时可能是“文字与格式字符串不匹配”,需要检查字段的值是否能被正常的转换成format的格式。