[MySQL] 時間函數操作.

若是你的資料庫時間是使用UNIX 時間戳記來記錄.
要在MySql 下就需要用到 FROM_UNIXTIME 此函數來轉換.
再搭配 year() , month() ,quarter(), week() .

一、年度查詢
查詢 本年度的數據
SELECT *
FROM Statistics
WHERE year( FROM_UNIXTIME( `CreateTime` ) ) = year( curdate() )


二、查詢季度數據
查詢數據附帶季度數
SELECT Id, quarter( FROM_UNIXTIME( `CreateTime` ) )
FROM `Statistics`

其他的同前面部分：查詢 本季度的數據
SELECT *
FROM Statistics
WHERE quarter( FROM_UNIXTIME( CreateTime ) ) = quarter( curdate( ))



三、查詢月度數據
本月統計(MySQL)

select * from Statistics where month( FROM_UNIXTIME( CreateTime ) ) =

month(curdate()) and year( FROM_UNIXTIME( CreateTime )) = year(curdate())

本週統計(MySQL)

select * from Statistics where month(FROM_UNIXTIME( CreateTime )) =

month(curdate()) and week(FROM_UNIXTIME( CreateTime )) = week(curdate())

四、查詢統計數據
日統計資料.

select sale, date_format(`SaleDate`, '%m/%d') as dt, `SaleDate` from `Sales` where 1 and SaleDate >= '2011-05-01' group by dt order by `SaleDate`;

週統計資料.

select sale, date_format(date_add(`SaleDate`,interval 6-date_format(`SaleDate`,'%w') day),'%m/%d') as dt, date_format(`SaleDate`,'%X %V') as dt2, `SaleDate` from `Sales` where 1 and SaleDate >= '2011-05-01' group by dt2 order by `SaleDate`;

月統計資料.

select sale, date_format(`RankingDate`,'%Y-%m') as dt, `SaleDate` from `Sales` where 1 and SaleDate >= '2011-05-01' group by dt order by `SaleDate`;