mysql 时间函数的使用

    
        
        
        
        
    
    
    
        SELECT HOUR ( APP_USER.CREATE_TIME ) AS IND,
                COUNT(*) AS NUM
        FROM TRUEHAN_USERS.APP_USER
                INNER JOIN TRUEHAN_USERS.APP_USER_PROFILE ON APP_USER_PROFILE.USER_ID = APP_USER.USER_ID
        WHERe TO_DAYS( APP_USER.CREATE_TIME ) = TO_DAYS(NOW())
          AND APP_USER_PROFILE.SEX = #{sex}
        GROUP BY IND
        ORDER BY IND
    

    
        select 1 as man, 0 as woman
    

    
    
        
        
        
        
    

    
        SELECT WEEKDAY(APP_USER.CREATE_TIME)     AS IND,
               COUNT(*)                          AS NUM
        FROM TRUEHAN_USERS.APP_USER
                     INNER JOIN TRUEHAN_USERS.APP_USER_PROFILE ON APP_USER_PROFILE.USER_ID = APP_USER.USER_ID
        WHERe YEARWEEK(date_format(APP_USER.CREATE_TIME, '%Y-%m-%d'), 1) = YEARWEEK(now())
          AND APP_USER_PROFILE.SEX = #{sex}
        GROUP BY IND
        ORDER BY IND
    

    
    
        select 1 as man, 0 as woman
    

    
    
        
        
        
        
    

    
        SELECT DAYOFMonTH(APP_USER.CREATE_TIME) AS IND,
               COUNT(*)                         AS NUM
        FROM TRUEHAN_USERS.APP_USER
                     INNER JOIN TRUEHAN_USERS.APP_USER_PROFILE ON APP_USER_PROFILE.USER_ID = APP_USER.USER_ID
        WHERe DATE_FORMAT(APP_USER.CREATE_TIME, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
          AND APP_USER_PROFILE.SEX = #{sex}
        GROUP BY IND
        ORDER BY IND
    

    
    
        select 1 as man, 0 as woman
    

    
    
        
        
        
        
    

    
        SELECT MonTH (APP_USER.CREATE_TIME ) -1 AS IND,
                COUNT(*) AS NUM
        FROM TRUEHAN_USERS.APP_USER
                INNER JOIN TRUEHAN_USERS.APP_USER_PROFILE
        ON APP_USER_PROFILE.USER_ID = APP_USER.USER_ID
        WHERe YEAR (APP_USER.CREATE_TIME) = YEAR (NOW())
          AND APP_USER_PROFILE.SEX = #{sex}
        GROUP BY IND
        ORDER BY IND
    
    
    
        select 1 as man, 0 as woman
    

需要注意的是：mysql YEARWEEK()函数默认周日作为每周的第一天，若想把周一当做第一天
需要设置第二个参数mode为1 YEARWEEK(‘2022-01-18’,1)