日期:2014-05-16  浏览次数:20597 次

UNIX时间戳计算,转换, select case when用法,sql字符截取

//SELECT CASE WHEN LENGTH(title)>8 THEN LEFT(title,8)+'...' ELSE title END AS title,hits FROM `article_1` ORDER BY hits DESC LIMIT 0,10;----当标题长度大于8时,取左边的8个字,后面加"..." 否则显示标题.

??? //SELECT left(title,8) AS title,hits,linkurl FROM `article_1` WHERE date(`addtime`)=curdate()-1 ORDER BY hits DESC LIMIT 0,10;---左边的8个字作为标题. 查询昨天的.


??? //SELECT * FROM `article_1` WHERE DATE_FORMAT(FROM_UNIXTIME(`addtime`),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')-1; --Uinx 时间戳 查询昨天的(查询出来跟上面的一条好像有点不同,具体忘了.)

??? //SELECT * FROM `article_1` WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(`addtime`);----查询一周的.

??? //SELECT * FROM `article_1` WHERE `addtime` BETWEEN UNIX_TIMESTAMP(now()) - 86400*7 AND UNIX_TIMESTAMP(now());----一周.

??? //SELECT * FROM `article_1` WHERE DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(`addtime`);---查询一个月的.
????????????
??? //SELECT * FROM `article_1` WHERE `addtime` BETWEEN UNIX_TIMESTAMP(now()) - 86400 AND UNIX_TIMESTAMP(now());----一天.

?

select date(from_unixtime(UNIX_TIMESTAMP(now())*1000/1000)) ----时间截转换成日期