舞影凌风

SQL语句用的不多的一些小技巧

  mysql

1. varchar 转 int型
SELECT cast('1' as SIGNED INTEGER) FROM image_trace_log LIMIT 10;

select * from gyzd_yysinfo order by cast(yysid as SIGNED INTEGER)
2. 时间戳格式化
FROM_UNIXTIME(uptime, '%Y-%m-%d %H:%i:%S')

注意第一个参数必须精确到秒，即时间戳为10位, 如果是13位得除1000

FROM_UNIXTIME(uptime/1000, '%Y-%m-%d %H:%i:%S')
3. 查询今天、昨天、近7天、近30天、本月、上一月的SQL语句
select * from `article` where date_sub(curdate(), INTERVAL 7 DAY) <= date(`add_time`);

mysql> select to_days('1997-10-07'), to_days('97-10-07');  
    -> 729669, 729669

参考:http://www.jb51.net/article/51597.htm