mysql时间类型查询


<sql id="selectRegisterSysUserByTimeType">
    <if test="type=='previous_year'">
        and year(insert_date)=year(date_sub(now(),interval 2 year))
    </if>
    <if test="type=='last_year'">
        and year(insert_date)=year(date_sub(now(),interval 1 year))
    </if>
    <if test="type=='half_year_ago'">
        to_days( now( ) ) - to_days( insert_date) > 180
    </if>
    <if test="type=='this_year'">
        and YEAR(insert_date)=year(now())
    </if>
    <if test="type=='quarter_last'">
        and quarter(insert_date)=quarter(date_sub(now(),interval 1 QUARTER))
    </if>
    <if test="type=='quarter_first'">

    </if>
    <if test="type=='quarter_second'">

    </if>
    <if test="type=='quarter_third'">

    </if>
    <if test="type=='quarter_fourth'">

    </if>
    <if test="type=='month_last'">
        and date_format(insert_date,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
    </if>
    <if test="type=='week_last'">
        and YEARWEEK(date_format(insert_date,'%Y-%m-%d'), 1) = YEARWEEK(now(), 1)-1
    </if>
    <if test="type=='before_yesterday'">
        and to_days( now( ) ) - to_days( insert_date) = 2
    </if>
    <if test="type=='yesterday'">
        and to_days( now( ) ) - to_days( insert_date) = 1
    </if>
    <if test="type=='today'">
        and to_days(insert_date) = to_days(now())
    </if>
</sql>

0
0

暂无评论