Mysql日期和时间函数--应用实例

1、select DATE_FORMAT( NOW( ) , '%Y') as tyear,DATE_FORMAT( NOW( ) , '%m') as tmonth,
        DATE_FORMAT( NOW( ) , '%d') as tday,TIME_FORMAT(now() , '%H') as thour,DATE_FORMAT( NOW( ) , '%Y%m%d') as tymd,
        DATE_FORMAT( DATE_ADD(NOW( ), Interval -1 day), '%Y%m%d') as yymd,DATE_FORMAT( NOW( ) , '%Y%m%d%H') as now

 

2、"select n.name as hostname,
        u.`name` as owner ,
        u.`manager_username` as manager,
        u.`email` as mail,
        p.`name` as propertyname,
        s.`name` as sitename      
        from node as n
        inner join property as p
        inner join site as s
        inner join `groupNode_node` as g_n
        inner join `groupNode` as gn
        inner join user as u
        where gn.`gn_id` = g_n.`gn_id` and
        gn.`user_id` = u.`user_id` and
        g_n.`node_id`=n.`node_id` and
        s.`site_id` = n.`site_id` and
        p.`prop_id` = n.`prop_id` and  n.name = '".($hostname)."'"

 

3、"SELECT *
FROM traffic_colo_hourly
WHERE date <= DATE_FORMAT(NOW(),'%Y%m%d%H') and
      date > DATE_FORMAT( DATE_ADD(NOW(), Interval -7 day),'%Y%m%d%H') "