【数据库笔记】Oracle & MySQL 日期时间数据类型、日期函数和转换函数の对比(续更)
文章目录
1 Oracle & MySQL 日期时间数据类型
1.1 Oracle 日期时间数据类型
数据类型 | 字节数 | 取值范围 | 日期格式 | 零值 |
---|---|---|---|---|
DATE | 7 (包含 世纪、年、月、日、时、分、秒。占用7个字节bai,上面每个部分1个字节) |
-4712-01-01 00:00:00 ~ 9999-12-31 23:59:59 | yyyyMMdd hh24:mi:ss | 0000-00-00 00:00:00 |
TIMESTAMP | 7/11 (如果精度为0,则用7字节存储,与date类型功能相同,如果精度大于0则用11字节存储) |
-4712-01-01 00:00:00 ~ 9999-12-31 23:59:59.999999 | yyyyMMdd hh24:mi:ss.ff6 (不仅可以保存日期和时间,还能保存小数秒,小数位数可以指定为0-9,默认为6位) |
0000-00-00 00:00:00.000000 |
1.2 MySQL 日期时间数据类型
数据类型 | 字节数 | 取值范围 | 日期格式 | 零值 |
---|---|---|---|---|
YEAR | 1 | 1901~2155 | YYYY | 0000 |
DATE | 3 | 1000-01-01~9999-12-3 | YYYY-MM-DD | 0000-00-00 |
TIME | 3 | -838:59:59~838:59:59 | HH:MM:SS | 00:00:00 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 |
TIMESTAMP | 4 | 1970-01-01 00:00:01~2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 0000-00-00 00:00:00 |
Reference:https://blog.****.net/weixin_42315600/article/details/88412976
2 Oracle 日期函数和转换函数
2.1 日期函数
日期函数用于处理 date类型 的数据,两个日期 相减 返回日期之间相差的天数。日期不允许做加法运算,无意义。
- 常见代表符号:yyyy 年,mm 月,dd 日,hh 小时,mi 分钟,ss 秒,day 星期几
- 默认情况:日期格式是dd-mon-yy(日-月-年)
表:Oracle 常见的日期函数
序号 | 函数 | 意义 | 示例 | 结果 |
---|---|---|---|---|
1 | sysdate | 该函数返回系统时间 | select sysdate from dual; | 2013-01-06 |
2 | months_between(m,n) | 日期m和日期n相差多少月数 | select months_between(to_date(‘2018-10-9’,‘yyyy-mm-dd’),to_date(‘2017-10-09’,‘yyyy-mm-dd’)) m from dual; | 12 |
select months_between(to_date(‘2017-10-9’,‘yyyy-mm-dd’),to_date(‘2018-10-09’,‘yyyy-mm-dd’)) m from dual; | -12 | |||
3 | add_months(d,n) | 在日期d上增加n个月数 | select add_months(sysdate,2) from dual; | 2012-5-16 下午 02:30:47 |
select add_months(sysdate,-2) from dual; | 2011-11-16 下午 02:32:39 | |||
4 | next_day(d, ‘星期一’) | 指定日期d下一个星期一对应的日期 | select next_day(sysdate, 'MONDAY ') FROM DUAL; | 2018/2/17 |
select next_day(sysdate,‘MON’) FROM DUAL; | 2018/2/17 | |||
5 | last_day(d) | 返回指定日期d所在月份的最后一天 | select last_day(SYSDATE) FROM dual; | 2018/2/28 |
6 | extract(month from d) | 从日期d上截取年、月、日、时、分、秒 | select extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from dual; | |
7 | round(d,time) | 日期的四舍五入 | select sysdate,round(sysdate,‘year’) from dual; | 2017-09-13 16:11:13,2018-01-01 00:00:00 |
8 | trunc(d,time) | 日期的截断 | select sysdate,trunc(sysdate) from dual | 2013-01-06,2013-01-06 |
补充说明
-
序号1:格式化输出
①select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) now from dual;=》 2020-10-09 01:50:54
②alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’; select sysdate from dual;=》 2020-10-09 01:50:54 -
序号7:ROUND(d[,fmt])
① 日期
【功能】返回一个以fmt为格式的四舍五入日期值
【参数】d是日期,fmt是格式 模型。默认fmt为DDD,即月中的某一天。
如果fmt为“YEAR”,舍入到某年的1月1日,即前半年舍去,后半年作为下 一年
如果fmt为“MONTH”,舍入到某月的1日,即前月舍去,后半月作为下一 月
默认为“DDD”,即月中的某一天,最靠近的天,前半天舍去,后半天作为第二天
如果fmt为“DAY”,舍入到最近的周的周日,即上半周舍去,下半周作为下 一周周日
【返回】日期
② 数字
--日期
sql>select sysdate,round(sysdate),round(sysdate,'ddd'),
round(sysdate,'day'),round(sysdate,'month'),round(sysdate,'year') from dual;
>2017-09-13 16:11:13 , 2017-09-14 00:00:00 , 2017-09-14 00:00:00 , 2017-09-17 00:00:00 ,
>2017-09-01 00:00:00 , 2018-01-01 00:00:00
--数字
select round(1234.5678,4) from dual;--1234.5678
select round(1234.5678,3) from dual;--1234.568
select round(1234.5678,2) from dual;--1234.57
select round(1234.5678,1) from dual;--1234.6
select round(1234.5678,0)from dual;--1235
select round(1234.5678,-1) from dual;--1230
select round(1234.5678,-2) from dual;--1200
select round(1234.5678,-3) from dual;--1000
select round(45.923,-1) from dual;--50
Reference:https://blog.****.net/weixin_44563573/article/details/89525979
-
序号8:trunc()
① 日期
② 数字
TRUNC(number,num_digits)
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
--日期
select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.
select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天
select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日
select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天
select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天
select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35
select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确
--数字
select trunc(123.458) from dual --123
select trunc(123.458,0) from dual --123
select trunc(123.458,1) from dual --123.4
select trunc(123.458,-1) from dual --120
select trunc(123.458,-4) from dual --0
select trunc(123.458,4) from dual --123.458
select trunc(123) from dual --123
select trunc(123,1) from dual --123
select trunc(123,-1) from dual --120
-
关于Oracle中的dual表
dual是Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select语句块中。以下几点我们需要明确:
- dual不是缩写词而是完整的单词。dual名词意思是对数,做形容词时是指二重的,二元的。
- Oracle中的dual表是一个单行单列的虚拟表。
- dual表是Oracle与数据字典一起自动创建的一个表,这个表只有1列DUMMY,数据类型为VERCHAR2(1),dual表中只有一个数据’X’, Oracle有内部逻辑保证dual表中永远只有一条数据。
- dual表主要用来选择系统变量或求一个表达式的值。
- Oracle的SELECT语法的限制为 SELECT * | [column1 [AS alias1], column2 [AS alias2]] FROM table。所以没有表名就没有办法查询,而时间日期并不存放在任何表中,于是这个dual虚拟表的概念就被引入了。最常见的一个简单的例子:SELECT sysdate FROM daul。
Reference:
https://blog.****.net/weixin_44563573/article/details/89525979
https://blog.****.net/tayanxunhua/article/details/9258029
https://www.cnblogs.com/zhangxiaoxia/p/10270840.html
https://blog.****.net/u012187452/article/details/79120771
2.2 转换函数
转换函数用于 将数据类型从一种转为另外一种 。在某些情况下,Oracle Server允许值的数据类型和实际的不一样,这时oracle server会隐含的转化数据类型,但是它并不适应所有的情况,为了提高程序的可靠性,我们应该使用转换函数进行转换。
表:Oracle 常见的转换函数
序号 | 函数 | 意义 | 示例 | 结果 | 备注 |
---|---|---|---|---|---|
1 | to_char(date,‘format’) | 将日期转换成字符串 | select to_char(sysdate, ‘yyyy/mm/dd’) from dual; | ‘2003/07/09’ | 1、必须包含在单引号中而且大小写敏感; 2、可以包含任意的有效的日期格式; 3、日期之间用逗号隔开。 |
2 | to_date(string,‘format’) | 将字符串转换成日期 | select to_date(‘2015-03-18 13:13:13’,‘yyyy-mm-dd hh24:mi:ss’) from dual; | 2015-03-18 13:13:13 | |
3 | to_number(char,‘format’) | 将字符串转换为数值型 | select to_number(’RMB234234.4350′,’L999999.0000′) from dual; | 234234.435 | 使用to_number(),一定要确保所转换字段是可转换为数字的。 如“2015-10-08”不可以,会报“invalid number”的错。 |
补充说明
-
序号1:to_char(date,‘format’)
① 日期
yy:两位数字的年份2004–>04
yyyy:四位数字的年份 2004年
mm:两位数字的月份 8月–>08
dd:两位数字的天 30号–>30
hh24: 8点–>20
hh12:8点–>08
mi、ss:显示分钟/秒
② 数字
9:显示数字,并忽略前面0
0:显示数字,如位数不足,则用0补齐
.:(小数点)在指定位置显示小数点
,:(千位符)在指定位置显示逗号
$:(美元符)在数字前加美元
L:(本地货币符)在数字前面加本地货币符号
C:(国际货币符)在数字前面加国际货币符号
显示薪水的时候,把本地货币单位加在前面
--日期
select to_char(sysdate, 'yyyy/mm/dd') from dual;--'2003/07/09'
select to_char(sysdate, 'Month DD, YYYY') from dual;--'July 09, 2003'
select to_char(sysdate, 'FMMonth DD, YYYY') from dual;--'July 9, 2003'
select to_char(sysdate, 'MON DDth, YYYY') from dual;--'JUL 09TH, 2003'
select to_char(sysdate, 'FMMON DDth, YYYY') from dual;--'JUL 9TH, 2003'
select to_char(sysdate, 'FMMon ddth, YYYY') from dual;--'Jul 9th, 2003'
--数字
select to_char(1210.73, '9999.9') from dual;--'1210.7'
select to_char(1210.73, '9,999.99') from dual;--'1,210.73'
select to_char(1210.73, '$9,999.00') from dual;--'$1,210.73'
select to_char(21, '000099') from dual;--'000021'
Reference:https://blog.****.net/weixin_44563573/article/details/89525979
3 MySQL 日期函数和转换函数
Mysql作为一款开元的免费关系型数据库,用户基础非常庞大,本文列出了MYSQL常用日期函数与日期转换格式化函数。
3.1 日期函数
表:MySQL 常见的时间日期函数(date)
序号 | 函数 | 意义 | 示例 | 结果 | 备注 |
---|---|---|---|---|---|
1 | DAYOFWEEK(date) | 返回日期date是星期几 | SELECT DAYOFWEEK(‘2020-10-09’); SELECT DAYOFWEEK(‘2020-10-09 00:00:00’) |
7 | 星期天=1,星期一=2, … 星期六=7 |
2 | WEEKDAY(date) | 返回date是在一周中的序号 | SELECT WEEKDAY(‘2020-10-09’) ; SELECT WEEKDAY(‘2020-10-09 00:00:00’) |
4 | 星期天=6,星期一=0, … 星期六=5 |
3 | DAYOFMONTH(date) | 返回date是当月的第几天 | SELECT DAYOFMONTH(‘2020-10-09’) ; SELECT DAYOFMONTH(‘2020-10-09 00:00:00’) |
9 | 1号就返回1,… ,31号就返回31 |
4 | DAYOFYEAR(date) | 返回date是当年的第几天 | SELECT DAYOFYEAR(‘2020-10-09’) ; SELECT DAYOFYEAR(‘2020-10-09 00:00:00’) |
283 | 01.01返回1,… ,12.31就返回365 |
5 | DAYNAME(date) | 返回date是周几的英文全称名字 | SELECT DAYNAME(‘2020-10-09’) ; SELECT DAYNAME(‘2020-10-09 00:00:00’) |
Friday | MONTHNAME(date):返回date的是当年第几月的英文名字; QUARTER(date):返回date的是当年的第几个季度,返回1,2,3,4 |
6 | WEEK(date,index) | 返回date在一年当中的第几周 | SELECT WEEK(‘2020-10-09’),WEEK(‘2020-10-09 00:00:00’,0),WEEK(‘2020-10-09 00:00:00’,1) | 40,40,41 | YEAR(date):返回date的4位数年份; YEARWEEK(date):获取日期的年和周; WEEKOFYEAR(date):获取当日是当年的第几周 |
7 | TO_DAYS(date) | 返回西元0年至日期date是总共多少天 | SELECT TO_DAYS(‘2020-10-09’) ,TO_DAYS(‘20201009’) ,TO_DAYS(‘201009’) | 738072,738072,738072 | |
8 | FROM_DAYS(date) | 返回西元0年至今多少天的DATE值 | SELECT FROM_DAYS(738072) | 2020-10-09 | |
9 | CURDATE() | 返回系统当前日期 | SELECT CURDATE(),CURRENT_DATE() | 2020-10-09,2020-10-09 | |
10 | UNIX_TIMESTAMP(date) | 返回时间戳 | SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(‘2016-01-16’) ,UNIX_TIMESTAMP(‘2016-01-16 23:59:59’) | 1602247989,1452873600,1452959999 | 时间戳是使用数字签名技术产生的数据,签名的对象包括了原始文件信息、签名参数、签名时间等信息。 |
11 | FROM_UNIXTIME(unix_timestamp,format) | 把时间戳转化成日期时间 | SELECT FROM_UNIXTIME(1452959999),FROM_UNIXTIME(1452959999,’%Y-%m-%d %H:%i:%s’) | 2016-01-16 23:59:59,2016-01-16 23:59:59 | |
12 | LAST_DAY(date ) | 获取date当月最后一天的日期 | SELECT LAST_DAY(SYSDATE()),LAST_DAY(‘2020-10-09’),LAST_DAY(‘2020-10-09 00:22:33’) | 2020-10-31,2020-10-31,2020-10-31 | |
13 | MAKEDATE(year ,dayofyear ) | 根据参数(年份,第多少天)获取日期 | SELECT MAKEDATE(2015 ,32) | 2015-02-01 | |
14 | MAKETIME(hour ,minute ,second ) | 根据参数(时,分,秒)获取时间 | SELECT MAKETIME(12 ,23 ,34 ) | 12:23:34 |
表:MySQL 常见的时间日期函数(time)
序号 | 函数 | 意义 | 示例 | 结果 | 备注 |
---|---|---|---|---|---|
1 | HOUR(time) | 返回该date或者time的hour值,值范围(0-23) | SELECT HOUR(‘11:20:18’),HOUR(‘2020-10-09 11:20:18’) | 11 | MINUTE(time):返回该time的minute值,值范围(0-59);SECOND(time):返回该time的minute值,值范围(0-59) |
2 | PERIOD_ADD(month,add) | 返回对month做增减的操作结果 | SELECT PERIOD_ADD(2010,2),PERIOD_ADD(202010,3),PERIOD_ADD(202010,-3) | 202012,202101, 202007 | month的格式为yyMM或者yyyyMM,返回的都是yyyyMM格式的结果,add可以传负值 |
3 | PERIOD_DIFF(monthStart,monthEnd) | 返回monthStart - monthEnd的间隔月数 | SELECT PERIOD_DIFF(2010,2009),PERIOD_DIFF(2009,2010),PERIOD_DIFF(202010,2009) | 1,-1,1 | |
4 | DATE_ADD(date,INTERVAL number type),同 ADDDATE() | 返回时间日期加减 | SELECT DATE_ADD(‘2015-12-31 23:59:59’,INTERVAL 1 SECOND) ; SELECT DATE_ADD(‘2015-12-31 23:59:59’,INTERVAL 1 DAY) ; SELECT DATE_ADD(‘2015-12-31 23:59:59’,INTERVAL ‘1:1’ MINUTE_SECOND) ; SELECT DATE_ADD(‘2016-01-01 00:00:00’,INTERVAL ‘-1 10’ DAY_HOUR); |
-> 2016-01-01 00:00:00-> 2016-01-01 23:59:59-> 2016-01-01 00:01:00-> 2015-12-30 14:00:00 | DATE_ADD()和ADDDATE()返回对date操作的结果;DATE_SUB(date,INTERVAL number type),同 SUBDATE() |
5 | CURTIME() | 返回系统当前时间 | SELECT CURTIME(),CURRENT_TIME() | 20:49:06,20:49:06 | |
6 | NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME() | 返回系统当前日期和时间 | SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP,LOCALTIME(),LOCALTIME | 2020-10-09 20:51:37 | |
7 | SEC_TO_TIME(seconds) | 把秒数转化成时间 | SELECT SEC_TO_TIME(2378) | 00:39:38 | TIME_TO_SEC(time):把时间转化成秒数 |
8 | ADDTIME(time,times) | 把times加到time上 | SELECT ADDTIME(“2015-12-31 23:59:59”,‘01:01:01’) | 2016-01-01 01:01:00 | |
9 | DATEDIFF(date1,date2) | 返回date1-date2 | SELECT DATEDIFF( 2019/10/10, 2020/10/10) | -365 | |
10 | TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2) | 返回datetime_expr2- datetime_expr1 | SELECT TIMESTAMPDIFF( SECOND,2019/10/10, 2020/10/10) | 31536000 |
补充说明
-
序号4:DATE_ADD(date,INTERVAL number type)
①date格式可以是“15-12-31”,可以是“15-12-31 23:59:59”,也可以是“2015-12-31 23:59:59”,如果参数date是date格式,则返回date格式结果,如果参数date是datetime格式,则返回datetime格式结果
②type格式:
秒:SECOND
分钟:MINUTE
时间:HOUR
天: DAY
月: MONTH
年: YEAR
分钟和秒:MINUTE_SECOND
小时和分钟:HOUR_MINUTE
天和小时:DAY_HOUR
年和月:YEAR_MONTH
小时, 分钟:HOUR_SECOND
天, 小时, 分钟:DAY_MINUTE
天, 小时, 分钟, 秒:DAY_SECOND
③其他格式:如果不用函数,也可以考虑用操作符“+”,“-”
SELECT '2016-01-01' - INTERVAL 1 SECOND;
SELECT '2016-01-01' - INTERVAL 1 DAY;
SELECT '2016-12-31 23:59:59' + INTERVAL 1 SECOND;
SELECT '2016-12-31 23:59:59' + INTERVAL '1:1' MINUTE_SECOND;
-> 2015-12-31 23:59:59
-> 2015-12-31
-> 2017-01-01 00:00:00
-> 2017-01-01 00:01:00
-
序号9/10:MySQL 计算时间差函数DATEDIFF & TIMESTAMPDIFF
①DATEDIFF(date1,date2)
date1 和 date2 参数是合法的日期或日期/时间表达式。
注释:只有值的日期部分参与计算。
②TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的
单位由interval参数给出。interval 的法定值同TIMESTAMPADD()函数说明中所列出的相同。
interval可以是:
秒:SECOND
分钟:MINUTE
时间:HOUR
天:DAY
月:MONTH
年:YEAR
SELECT
datediff( from_date, to_date ),
datediff( to_date, from_date ),
TIMESTAMPDIFF( DAY, from_date, to_date ),
TIMESTAMPDIFF( DAY, to_date, from_date ) ,
TIMESTAMPDIFF(SECOND ,from_date,to_date)
FROM
salaries
LIMIT 1
-> 365 365 365 -365 31536000
3.2 转换函数
表:MySQL 常见的转换函数
序号 | 函数 | 意义 | 示例 | 结果 | 备注 |
---|---|---|---|---|---|
1 | DATE_FORMAT(date,format) | 根据参数对date进行格式化 | SELECT DATE_FORMAT(‘2020-10-09 22:23:59’,’%W %M %Y’) | Friday October 2020 | |
2 | STR_TO_DATE(date,format ) | 将字符串转成format格式的日期时间 | SELECT STR_TO_DATE(‘2015-01-01’, ‘%Y-%m-%d’) | 2015-01-01 | |
3 | CONVERT_TZ(date,from_tz ,to_tz ) | 转换时区 | SELECT CONVERT_TZ(‘2004-01-01 12:00:00’,’+00:00’,’+10:00’) | 2004-01-01 22:00:00 |
补充说明
- 序号1: DATE_FORMAT(date,format)
SELECT DATE_FORMAT('2020-10-09 22:23:59','%W %M %Y') ;
SELECT DATE_FORMAT('2020-10-09 22:23:59','%D %y %a %d %m %b %j') ;
SELECT DATE_FORMAT('2020-10-09 22:23:59','%H %k %I %r %T %S %w') ;
SELECT DATE_FORMAT('2020-10-09 22:23:59','%Y-%m-%d %H:%i:%s');
-> Saturday January 2016
-> 9th 20 Fri 09 10 Oct 283
-> 22 22 10 10:23:59 PM 22:23:59 59 5
-> 2020-10-09 22:23:59;
format的格式都列出来:
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 字符% )
Reference:https://www.jb51.net/article/135803.htm
4 总结
温故而知新,与君共勉。
本文地址:https://blog.****.net/qq_36056219/article/details/108977639