精华内容
下载资源
问答
  • 2018-09-11 10:34:19

     

    直接贴sql语句

     <if test="checkStartTime!=null and checkStartTime!=''">
            <![CDATA[   and DATE_FORMAT(checkStartTime, '%Y-%m-%d')>=  DATE_FORMAT(#{checkStartTime}, '%Y-%m-%d')   ]]>
            </if>
            <if test="checkEndTime!=null and checkEndTime!=''">
                <![CDATA[  and DATE_FORMAT(checkEndTime, '%Y-%m-%d') <= DATE_FORMAT(#{checkEndTime}, '%Y-%m-%d')    ]]>
        </if>

     

    checkStartTime:开始时间

     checkEndTime:结束时间

     

    经测试,只选择开始时间,或者只选择结束时间都可查询到相应数据。

    更多相关内容
  • mysql时间区间查询

    千次阅读 2019-06-22 12:10:09
    //查询用户消费统计表 public Page<UserConsumptionStatistics> getAllUserConsumptionStatistics(Map, Object> searchParams, Page<UserConsumptionStatistics> page) { StringBuffer sql = new StringBuffer();...
    package com.ecommerce.core.dao.sales;
    
    import com.ecommerce.common.Constants;
    import com.ecommerce.core.entity.sys.SysBaseUser;
    import com.ecommerce.core.vo.erp.WeiXinOrderDate;
    import com.ecommerce.core.vo.sales.UserConsumptionStatistics;
    import com.startrekstar.yd.core.orm.Page;
    import com.startrekstar.yd.core.orm.hibernate.HibernateDao;
    import org.apache.commons.lang.StringUtils;
    import org.hibernate.Query;
    import org.hibernate.SQLQuery;
    import org.hibernate.criterion.Restrictions;
    import org.hibernate.jdbc.Work;
    import org.hibernate.transform.Transformers;
    import org.hibernate.type.IntegerType;
    import org.hibernate.type.LongType;
    import org.hibernate.type.StringType;
    import org.springframework.stereotype.Repository;
    import org.springframework.transaction.annotation.Transactional;
    
    import java.math.BigInteger;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    
    /**
     *
     */
    @Repository
    public class UserConsumptionStatisticsDao extends HibernateDao<UserConsumptionStatistics, String> {
    
    
        //查询用户消费统计表
        public Page<UserConsumptionStatistics> getAllUserConsumptionStatistics(Map<String, Object> searchParams, Page<UserConsumptionStatistics> page) {
            StringBuffer sql = new StringBuffer();
            StringBuffer countsql = new StringBuffer();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    
            sql.append("  select buser.user_name sysBaseUserName,   buser.mobile_no    sysBaseUserMobileNo  ,buser.id  sysBaseUserId, sum(wx.amount)/100 turnoverAmount  , COUNT(wx.order_id) numberOfTransactionOrders , MAX(wx.last_time)  lastOrderTime  ");
            sql.append("  from  wx_order  wx  LEFT JOIN  sys_base_user buser on wx.base_user_id = buser.id  WHERE wx.sale_type = '1'  ");
            countsql.append("  select COUNT(wx.order_id)  from wx_order wx  LEFT JOIN  sys_base_user  buser  on wx.base_user_id  =  buser.id   WHERE wx.sale_type = '1'");
    
            String sysBaseUserName = (String) searchParams.get("LIKES_sysBaseUserName");
            if (StringUtils.isNotEmpty(sysBaseUserName)) {
                     sql.append(" and buser.user_name like '%" + sysBaseUserName + "%'");
                countsql.append(" and buser.user_name like '%" + sysBaseUserName + "%'");
            }
            String sysBaseUserMobileNo = (String) searchParams.get("LIKES_sysBaseUserMobileNo");
            if (StringUtils.isNotEmpty(sysBaseUserMobileNo)) {
                sql.append(" and  buser.mobile_no   like '%" + sysBaseUserMobileNo + "%'");
                countsql.append(" and  buser.mobile_no   like '%" + sysBaseUserMobileNo + "%'");
            }
    
            String startTime = (String) searchParams.get("EQS_startTime");
            if (StringUtils.isNotEmpty(startTime)) {
                startTime = startTime + " 00:00:00";
                sql.append(" and wx.create_time  BETWEEN '" + startTime + "'");
                countsql.append(" and wx.create_time BETWEEN '" + startTime + "'");
    
            } else {
                startTime = sdf.format(new Date()) + "  00:00:00";
                sql.append(" and wx.create_time BETWEEN '" + startTime + "'");
                countsql.append(" and wx.create_time BETWEEN '" + startTime + "'");
    
            }
    
            String endTime = (String) searchParams.get("EQS_endTime");
            if (StringUtils.isNotEmpty(endTime)) {
                endTime = endTime + "  23:59:59";
                sql.append(" and  '" + endTime + "'");
                countsql.append(" and   '" + endTime + "'");
            } else {
                endTime = sdf.format(new Date()) + "  23:59:59";
                sql.append(" and   '" + endTime + "'");
                countsql.append(" and   '" + endTime + "'");
            }
    
            sql.append(" GROUP BY buser.id ");
            countsql.append(" GROUP BY buser.id ");
            System.out.println("sql=="+sql.toString());
            System.out.println("cql=="+countsql.toString());
            SQLQuery query = this.getSession().createSQLQuery(sql.toString());
            query.addScalar("sysBaseUserName", new StringType());
            query.addScalar("sysBaseUserMobileNo", new StringType());
            query.addScalar("sysBaseUserId", new StringType());
            query.addScalar("turnoverAmount", new IntegerType());
            query.addScalar("numberOfTransactionOrders", new IntegerType());
            query.addScalar("lastOrderTime", new LongType());
            query.setResultTransformer(Transformers.aliasToBean(UserConsumptionStatistics.class));
            List<UserConsumptionStatistics> list = query.list();
            page.setResult(list);
            Query q = this.getSession().createSQLQuery(countsql.toString());
    
            BigInteger total = (BigInteger) q.uniqueResult();
            if (total == null) {
                total = new BigInteger("0");
            }
            page.setTotalCount(Long.valueOf(total.toString()));
            return page;
        }
    
    
    }
    
    展开全文
  • MySQL根据时间范围查询 1. 查询今天 SELECT * FROM 表名 WHERE TO_DAYS(时间字段) = TO_DAYS(NOW()); 2. 查询本周 SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(时间字段,'%Y-%m-%d')) = YEARWEEK(NOW()); 3. ...
  • Mysql查询时间区间日期列表

    千次阅读 2021-12-01 15:26:37
    1、查询时间区间日期列表,不会由于数据表数据影响 select a.date from ( select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as date from (select 0 as a union all select ...

    1、查询时间区间日期列表,不会由于数据表数据影响

    select a.date 
    from (
        select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as date
        from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
    ) a
    where a.date between '2020-01-20' and '2021-12-24' ORDER BY a.date asc
    

     tips:如果要查询当前日期后面的数据 curdate()改为截止日期就好

    2、创建视图可以公共使用

    CREATE VIEW v_digits AS
      SELECT 0 AS digit UNION ALL
      SELECT 1 UNION ALL
      SELECT 2 UNION ALL
      SELECT 3 UNION ALL
      SELECT 4 UNION ALL
      SELECT 5 UNION ALL
      SELECT 6 UNION ALL
      SELECT 7 UNION ALL
      SELECT 8 UNION ALL
      SELECT 9;
    
    CREATE VIEW v_numbers AS
      SELECT
        ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
      FROM
        v_digits as ones,
        v_digits as tens,
        v_digits as hundreds,
        v_digits as thousands;
    		
    -- 生成的日期格式为  yyyy-MM-dd		
    CREATE VIEW v_dates AS
      SELECT
        SUBDATE(CURRENT_DATE(), number) AS date
      FROM
        v_numbers
      UNION ALL
      SELECT
        ADDDATE(CURRENT_DATE(), number + 1) AS date
      FROM
        v_numbers;
    
    -- 生成的日期格式为 yyyy-MM
    CREATE VIEW v_months AS
      SELECT
        DATE_FORMAT(SUBDATE(CURRENT_DATE(), INTERVAL number MONTH),'%Y-%m')  AS date
      FROM
        v_numbers
      UNION ALL
      SELECT
        DATE_FORMAT(ADDDATE(CURRENT_DATE(), INTERVAL number+1 MONTH),"%Y-%m") AS date
      FROM
        v_numbers;
    

    3、创建为视图之后,可以通过视图查询时间区间列表日期

    SELECT
      date
    FROM
      v_dates
    WHERE
      date BETWEEN '2020-01-20' AND '2021-01-24'
    ORDER BY
      date asc 
    

    4、查询时间区间按月

    select DATE_FORMAT(str_to_date (a.Date,'%Y-%m-%d'),"%Y-%m") as Date 
    from (
        select '2011-12-24' - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) MONTH as Date
        from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
    ) a
    where a.Date between '2010-01-20' and '2011-12-24' ORDER BY a.Date asc;

    展开全文
  • <select id="selectUsers" resultType="map"> select <include refid="Base_Column_List"/> from sys_user where status != 'DELETED' <if test="name !... and (phone like CONC
    <select id="selectUsers" resultType="map">
            select
            <include refid="Base_Column_List"/>
            from sys_user
            where status != 'DELETED'
            <if test="name != null and name != ''">
                and (phone like CONCAT('%',#{name},'%')
                or account like CONCAT('%',#{name},'%')
                or name like CONCAT('%',#{name},'%'))
            </if>
            <if test="deptId != null and deptId != 0">
                and (dept_id = #{deptId} or dept_id in ( select dept_id from sys_dept where pids like CONCAT('%$[', #{deptId}, '$]%') escape '$' ))
            </if>
            <if test="beginTime != null and beginTime != '' and endTime != null and endTime != ''">
                and (create_time between CONCAT(#{beginTime},' 00:00:00') and CONCAT(#{endTime},' 23:59:59'))
            </if>
        </select>
    
    展开全文
  • 我有一张请假申请表,里面有StartTime(请假开始时间),EndTime(请假结束时间),前端查询数据的时候会传一个区间, 比如说我要查询10.01到10.31的所有请假数据,那么请假时间为9.25到10.3 的也要查出来,10.30到...
  • Mysql时间范围查询不走索引问题

    千次阅读 2022-02-22 17:28:30
    使用Mysql进行数据查询时,如果在SQL语句中出现范围查询,类似如下语句: select * from logs where create_time >= '2020-01-01' ; 此时,虽然在create_time字段上添加了索引,但是否会走索引还需要看数据量的...
  • mysql时间查询优化的方法

    千次阅读 2021-03-16 00:03:53
    mysql时间查询优化的方法发布时间:2020-12-16 09:41:14来源:亿速云阅读:73作者:小新小编给大家分享一下mysql时间查询优化的方法,希望大家阅读完这篇文章后大所收获,下面让我们一起去探讨吧!mysql时间...
  • mysql时间查询优化的方法:1、【register_time】字段是datetime类型,转换为日期再匹配,需要查询出所有行进行过滤;2、可以利用在【register_time】字段上建立索引,查询极快。本教程操作环境:windows7系统、...
  • select * from table where createtime between '2019-01-01 00:00:00' and '...实际数据量17万条,扫描了77万行,数据总量为375万条数据,查询耗时85秒,请问一下怎么才能优化 其中storeid和createtime都创建了索引
  • 主要介绍了Mysql指定日期区间的提取方法,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下
  • MySQL 查询指定时间范围内的数据

    千次阅读 2020-08-31 19:08:46
    时间字段名均不引   当天 SELECT * FROM 表名 WHERE TO_DAYS(时间字段名) = TO_DAYS(NOW());   几天前 -- 昨天。几天前就<=几 SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) <= ...
  • mysql根据时间范围查询问题

    千次阅读 2021-02-07 10:36:16
    mysql中的datetime类型的数据和字符串类型的数据是可以直接进行查询比较的,无需转换,当然了,如果你是处女座的,也可以进行时间格式的转换后在进行比较. mysql日期与字符串转换函数,注意一下索引问题 字符串转日期:...
  • 数据库的日期区间查询方法。

    千次阅读 2021-01-19 00:05:00
    假设要查询2011-05-02到2011-05-30之间的数据,实现方式如下:1、to_date方式:select * from tablename where time>=to_date('2011-05-02','yyyy-mm-dd') and time <=to_date('2011-05-30','yyyy-mm-d...
  • 文档中详述了测试环境,测试工具、测试执行命令,以及最后的测试结果。
  • 1、按天统计,统计区间的所有天数的数据,没有的赋值为0 SELECT DATE_FORMAT( a.date, '%Y-%m-%d' ) AS abscissa_name, IFNULL( b.count, 0 ) AS total FROM ( SELECT @num := @num + 1 AS num, date_format...
  • 今天群里抛出了个乍看之下很...环境:MySQL 5.7.251. 创建测试表我先整了个32万行记录的表test_user,表中仅有两个字段:id和创建时间:CREATE TABLE `test_memory` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT...
  • mysql查询当天时间

    千次阅读 2020-08-12 10:40:38
    查询当天 第一种 tborder.create_time > DATE_FORMAT('new Date()', '%Y-%m-%d') AND tborder.create_time < date_add(DATE_FORMAT(new Date(), '%Y-%m-%d'),interval 1 day)) 说明 DATE_FORMAT()函数是将...
  • 本文主要介绍了MySQL查询两个日期之间的记录的方法。具有很好的参考价值,下面跟着小编一起来看下吧
  • mysql时间范围查询 索引无效

    万次阅读 2019-01-31 15:03:51
    对于range类型,选择性小于17%...mysql时间范围查询 索引无效 5C 死活不走带时间的索引 求大佬帮忙给个解决方案~ 编辑于:2018.12.29 16:59 0 分享 |评论0|收藏0|浏览199 ZoeLulur 声望: 0 4个回答 ...
  • mysql 时间区间 查询 (粘自网易博友)

    千次阅读 2014-02-13 22:14:57
    查询一天: select * from table where to_days(column_time) = to_days(now()); select * from table where date(column_time) = curdate(); 查询一周: select * from table where DATE_SUB(CURDATE...
  • MYSQL根据日期查询

    万次阅读 多人点赞 2017-08-13 15:08:44
    总结一下mysql的根据日期查询数据和在日期区间查询数据. 首先,看数据表和表结构 建表语句: CREATE TABLEdatetest( idint(11) NOT NULL AUTO_INCREMENT, brand_namevarchar(100) DEFAULT NULL COMMENT '品牌名称...
  • 错误提示:invalid comparison: java.util.Date and java.lang.String 通常是因为写sql时判断了是否为""空字符串,删掉该判断即可
  • mysql查询时间段内的数据

    万次阅读 多人点赞 2018-01-21 11:23:20
    参照文章( mysql查询时间段内数据)进行了操作。  先来建表语句: SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for t_user -- ---------------------------- DROP TABLE ...
  • mysql 获取规定时间段内的统计数据 按年统计 SELECT count(*), DATE_FORMAT(order_info.create_time, '%Y-%m-%d') AS count_by_date FROM order_info WHERE DATE_FORMAT(order_info.create_time, '%Y') = '...
  • mysql时间区间过滤出对应值

    千次阅读 2022-03-14 10:03:51
    上面一张表,我要根据当前时间过滤出对应的值: SELECT `CODE`, levelValue FROM leveldate WHERE DATE_FORMAT( NOW(), '%m%e' ) >= firstMonth AND DATE_FORMAT( NOW(), '%m%e' ) <= endMonth ...
  • MySQL区间查询 between and 和 in
  • mysql 日期区间查询问题

    万次阅读 2018-01-19 08:58:30
    在做mysql区间查询的时候,查询出的数据缺少最大时间的数据例如:表中有一个字段datetime类型的字段date,sql语句如下:select * from test where date between '2018-01-17' and '2018-01-18';select * from test ...
  • MySQL按照时间进行查询 查询今天的数据 select * from 表名 where to_days(时间字段名) = to_days(now()); 查询昨天的数据 select * from 表名 where to_days(now()) - to_days(时间字段名) = 1 查询近七天的数据 ...
  • between是左右闭区间,为什么无法获取右边区间的数据呢? 原因: 1.首先emp表格中hiredate的数据类型是datetime类型,当你使用between查找日期范围的时候,会将 左区间‘1980-12-17’转化为‘1980-12-17 00:00:00’...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 38,668
精华内容 15,467
关键字:

mysql时间区间查询

mysql 订阅