Mysql和Oracle时间的区别

zzjnvhmshb

Mysql数据库，Oracle数据库分别取得年，月，日。 年月，年月日。   
Mysql语句：

select substr(a.createtime, 1, 4) as statisticsName from defect1 a;
select substr(a.createtime, 1, 7) as statisticsName from defect1 a;
select substr(a.createtime, 1, 10) as statisticsName from defect1 a;

Oracle语句一：

select cn.statisticsID, cn.statisticsName ,cn.record as statisticsName from
( select to_char(a.solveTime,'yyyy') as statisticsID, count(a.createTime) as record ,to_char(a.createTime,'yyyy') as statisticsName from defect1 a
where 1=1 group by to_char(a.solveTime,'yyyy') , to_char(a.createTime, 'yyyy')) cn  where   record > '1 ' order by statisticsID

select cn.statisticsID, cn.statisticsName ,cn.record as statisticsName from
( select to_char(a.solveTime,'yyyy-mm') as statisticsID, count(a.createTime) as record ,to_char(a.createTime,'yyyy-mm') as statisticsName from defect1 a
where 1=1 group by to_char(a.solveTime,'yyyy-mm') , to_char(a.createTime, 'yyyy-mm')) cn  where   record > '1 ' order by statisticsID

select cn.statisticsID, cn.statisticsName ,cn.record as statisticsName from
( select to_char(a.solveTime,'yyyy-mm-dd') as statisticsID, count(a.createTime) as record ,to_char(a.createTime,'yyyy-mm-dd') as statisticsName from defect1 a
where 1=1 group by to_char(a.solveTime,'yyyy-mm-dd') , to_char(a.createTime, 'yyyy-mm-dd')) cn  where   record > '1 ' order by statisticsID
select substr(to_char(a.solveTime,'yyyy-mm-dd'),1,4) as statisticsID,
count(a.createTime) as record from defect1 a where 1=1 group by substr(to_char(a.solveTime,'yyyy-mm-dd'),1,4)


Oracle语句二：

select cn.statisticsID,statisticsName ,cn.record as statisticsName from
( select substr(to_char(a.createTime,'yyyy-mm-dd'), 1, 4) as statisticsID, count(to_char(a.createTime,'yyyy-mm-dd')) as record,substr(to_char(a.createTime,'yyyy-mm-dd'), 1, 4) as statisticsName from defect1 a
where 1=1 group by substr(to_char(a.solveTime,'yyyy-mm-dd'),1,4),to_char(a.createTime,'yyyy-mm-dd')  ) cn  where   record > '1 ' order by statisticsID

select cn.statisticsID,statisticsName ,cn.record as statisticsName from
( select substr(to_char(a.createTime,'yyyy-mm-dd'), 1, 7) as statisticsID, count(to_char(a.createTime,'yyyy-mm-dd')) as record,substr(to_char(a.createTime,'yyyy-mm-dd'), 1, 7) as statisticsName from defect1 a
where 1=1 group by substr(to_char(a.solveTime,'yyyy-mm-dd'),1,7),to_char(a.createTime,'yyyy-mm-dd')  ) cn  where   record > '1 ' order by statisticsID

select cn.statisticsID,statisticsName ,cn.record as statisticsName from
( select substr(to_char(a.createTime,'yyyy-mm-dd'), 1, 10) as statisticsID, count(to_char(a.createTime,'yyyy-mm-dd')) as record,substr(to_char(a.createTime,'yyyy-mm-dd'), 1, 10) as statisticsName from defect1 a
where 1=1 group by substr(to_char(a.solveTime,'yyyy-mm-dd'),1,10),to_char(a.createTime,'yyyy-mm-dd')  ) cn  where   record > '1 ' order by statisticsID