mysql、oracle日期函数总结

### ORACLE 中两日期相减得到天数(取整)，MySQL中日期不能直接相减 
### 用MYSQL写为：UNIX_TIMESTAMP(sysdate())-UNIX_TIMESTAMP(a.operate_tm) 取两日期间隔秒数 换算成天数
### mysql INSERT 、DELETE时不支持别名


## mysql添加 rownum 伪列 等价于oracle rownum 伪列 
select @x:=ifnull(@x,0)+1 as rownum ,e.* from test.emp e,(SELECT  @x :=0 )t
#MySQL左连接替换oracle MINUS    
/*SELECT member_id, name FROM a 
MINUS 
SELECT member_id, name FROM b ;

SELECT DISTINCT a.member_id, a.name 
FROM a LEFT JOIN b USING (member_id, name) 
WHERE b.member_id IS NULL */

SELECT UNIX_TIMESTAMP(STR_TO_DATE('2017-01-15 10:19:50','%Y-%m-%d %H:%i:%s'));#取秒数
SELECT TRUNCATE(1.55,1);#SELECT TRUNC(1.55,1) from dual; 截取保留1位小数
SELECT TRUNCATE(5/4,0);#SELECT TRUNC(1.55,1) from dual; 截取整数
select ABS(-0.22);#ABS取绝对值


SELECT ROUND( 2.335,2) FROM DUAL;#四舍五入保留2位小数 同oracle


## 等价于 oracle select trim(TO_CHAR(12345, '999999990.99')) from dual;
SELECT TRIM(REPLACE(FORMAT(0.125,2),',','')); 


SELECT REPLACE(FORMAT(0.125,2),',','') -- 0.13 保留2位小数(四舍五入)转为字符类型


#oracle MySQL中 substr函数语法相同
select substr('0123456789123456789012345', 13, 5) FROM dual; #'34567'


#取上月最后一天
SELECT LAST_DAY(DATE_ADD(CURDATE(),INTERVAL -1 MONTH));
SELECT DATE_ADD(STR_TO_DATE('2012-01-01','%Y-%m-%d'),INTERVAL -1 DAY);


## 两日期相隔天数取整天
SELECT DATEDIFF(NOW(),STR_TO_DATE('2017-03-10 15:19:09','%Y-%m-%d %H:%i:%s'));


SELECT EXTRACT(YEAR_MONTH FROM NOW()); #按年月格式截取时间返回字符格式 '201703'


select DATE_FORMAT('20120101','%Y%m');-- 字符格式
select CAST(DATE_FORMAT(NOW(),'%Y%m') AS SIGNED)+1;-- 'yyyymm'格式日期转数字
select CAST(DATE_FORMAT(DATE_ADD(DATE_FORMAT(NOW(),'%Y%m%d'),INTERVAL -1 MONTH),'%Y%m') AS SIGNED);-- 'yyyymm'格式日期加减n月后转数字


SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y%m%d') ; #'20170326'
SELECT DATE_ADD(NOW(),INTERVAL -1 DAY); #2017-03-26 17:29:04
SELECT DATE_FORMAT(NOW(),'%Y%m%d')+1 ;  #'20170328'


SELECT day(NOW()) ; #取日期所在天数


select CURTIME(); #取当前时间 17:31:42


select LAST_DAY(STR_to_date('2012-02','%Y-%m')); #取日期所在月份最后一天日期 2012-02-29


##取上月第一天 返回 %y-%m-%d 字符类型 
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract( day from now())-1 day),interval 1 month);


##取上月第一天日期 返回日期类型 等价于oracle  select trunc(add_months(sysdate,-1),'mm') from dual;
select DATE_ADD((DATE_ADD(CURDATE(),INTERVAL -1 MONTH)),interval -day(DATE_ADD(CURDATE(),INTERVAL -1 MONTH))+1 day);


##取当月第一天日期 等价于oracle SELECT trunc(sysdate,'mm') FROM DUAL; 
select DATE_ADD(curdate(),interval -day(curdate())+1 day);


SELECT CURDATE();#2016-12-29 取当前日期，不带时间
SELECT ADDDATE(CURDATE(),INTERVAL -1 DAY);#2016-12-28 


#取当日期所在月份最后一天最后一秒 2012-05-31 23:59:59(日期类型)
SELECT STR_TO_DATE(CONCAT_WS('',LAST_DAY(STR_TO_DATE(CONCAT_WS('','201205','01'),'%Y%m%d')),' 23:59:59'),'%Y-%m-%d %H:%i:%s');


SELECT DATE( NOW()); -- 2017-02-10
SELECT CURDATE(); # 取当前日期，不带时间 '2017-02-10'
SELECT NOW(); # 取当前日期，带时间 '2017-02-10 15:19:09'
SELECT SYSDATE(); # 取当前日期，带时间 '2017-02-10 15:19:09'


#取20170216161310格式的日期字符
SELECT TRIM(REPLACE(REPLACE(REPLACE(CAST(NOW() AS char),'-',''),':',''),' ',''));