sql日期函数

sql日期函数

1.获取日期时间函数

1.1 获取当前日期时间

SELECT NOW(); # 2024-01-06 22:37:45 

1.2 获取当前日期

SELECT CURRENT_DATE(); # 2024-01-06 

1.3 获取当前时间

SELECT CURRENT_TIME(); # 22:39:04

2.datetime数据类型格式化

SELECT DATE_FORMAT('2024-01-08 22:23:01', '%Y%m%d%H%i%s');# 20240108222301
说明：
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位 ★★★
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31) ★★★
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12) ★★★ month
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)★★★
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59) ★★★ minite
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59) ★★★
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天,查询指定日期属于当前年份的第几个周 ★★★★
%u 星期(0……52), 这里星期一是星期的第一天

#格式化时间(datetime到string类型)
#使用%U-->计算当天是一年中的第几周
select date_format('2024-08-30 19:16:17','%Y/%m/%d %H:%i:%s--%U'); #2024/08/30 19:16:17--34

3.字符串数据类型转换成datetime数据类型

要求：# 日期格式与表达式格式一致即可

#解析string类型成datetime类型
select str_to_date('06/01/2024','%m/%d/%Y');#2024-06-01

4.增加和减少时间间隔

#增加一个时间间隔
#将当前日期加两天
select date_add(now(),interval 2 day);#2024-09-01 19:27:11
#将当前日期减两天
select date_add(now(),interval -2 day );#2024-08-28 19:28:04
#将当前日期加两个月
select date_add(now(),interval 2 month );#2024-10-30 19:28:49
select date_add('2024-08-30 19:16:17',interval 2 day);#2024-09-01 19:16:17

#减少一个时间间隔
select date_sub('2024-08-30 19:16:17',interval 2 day);#2024-08-28 19:16:17

5. 日期相差天数（天）

select datediff('2024-01-06','2024-12-28'); -- 9 

6. 相差时间（小时）

select timediff('2024-01-06 08:08:08', '2024-12-28 09:00:00'); -- 08:08:08

7. 返回日期date的星期索引

# 返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。 

SELECT DAYOFWEEK(NOW())-1;

# 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天) 

SELECT WEEKDAY(NOW())+1

;

案例：统计沪深两市T日(当前股票交易日)每分钟达到涨跌停股票的数据

功能描述：统计沪深两市T日(当前股票交易日)每分钟达到涨跌停股票的数据

注意：如果不在股票的交易日内，则统计最近的股票交易日下的数据

返回的数据

{
    "code": 1,
    "data": {
        "upList": [
            {
                "count": 1,//涨停数量
                "time": "202112311412"//当天分时
            },
           {
                "count": 3,//涨停数量
                "time": "202112311413"//当天分时
            },
              //省略......
        ],
        "downList": [
            {
                "count": 2,//跌停数量
                "time": "202112310925"//当天分时
            },
            //省略......
        ]
    }
}

1.分析sql

#1.使用的表 ：stock_rt_info(个股流水表)
#2.业务分析
    #1. 涨停:通过判断涨幅（使用的字段是cur_price，pre_close_price）是否>0.1得到该个股是否到达涨停
    #2. 时间范围：当前交易日的开盘点到当前最新交易点的时间，如果不是交易日，就使用上一个交易日，使用between and
    #3. 分组：通过时间来分组，每分钟一组
#3.返回的字段 count time

select
        (cur_price-pre_close_price)/pre_close_price as ud,
        cur_time as time
from stock_rt_info
where cur_time BETWEEN '2022-01-06 09:30:00' and '2022-01-06 14:25:00'
having ud>=0.1;

然后我们再将该表作为一张表，以时间分组，统计相同时间的个股的数量

#合并
select tmp.time,count(*) as count from (select
                     (cur_price-pre_close_price)/pre_close_price as ud,
                     cur_time as time
             from stock_rt_info
             where cur_time BETWEEN '2022-01-06 09:30:00' and '2022-01-06 14:25:00'
             having ud>=0.1) as tmp
group by tmp.time;

设置返回时间的格式

#设置返回时间的格式
select tmp.time,count(*) as count from (select
                                                (cur_price-pre_close_price)/pre_close_price as ud,
                                                date_format(cur_time,'%Y%m%d%H%i%s') as time
                                        from stock_rt_info
                                        where cur_time BETWEEN '2022-01-06 09:30:00' and '2022-01-06 14:25:00'
                                        having ud>=0.1) as tmp
group by tmp.time;

2.设置接口

  @GetMapping("/stock/updown/count")
    @ApiOperation("沪深两市T日(当前股票交易日)每分钟达到涨跌停股票的数据")
    public R<Map<String,List>>getStockUpdownCount(){
        return stockService.getStockUpdownCount();
    }

返回的数据只有两个属性upList,downList可以作为一个Map集合，然后这两个属性的值为List

3.书写业务代码

 @Override
    public R<Map<String, List>> getStockUpdownCount() {
        //1.获取当前时间的最新交易点
        DateTime dateTime = DateTimeUtil.getLastDate4Stock(DateTime.now());
        Date time =dateTime.toDate();
        //获取开盘点
        Date openDate = DateTimeUtil.getOpenDate(dateTime).toDate();
        //设置mock data
        openDate=DateTime.parse("2022-01-06 09:30:00",DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
        time=DateTime.parse("2022-01-06 14:25:00",DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")).toDate();
        //2.调用接口获取每分钟内涨跌停的个股数量
        //获取涨停
        List<Map>upList=stockRtInfoMapper.getStockUpdownCount(openDate,time,1);
        //获取跌停
        List<Map>downList=stockRtInfoMapper.getStockUpdownCount(openDate,time,0);
        //3.封装数据
        Map<String,List>data=new HashMap<>();
        data.put("upList",upList);
        data.put("downList",downList);
        //4.返回数据
        return R.ok(data);
    }

4.mapper接口

   /**
     * 获取每分钟内个股的涨跌停数量
     * @param openDate 开盘时间
     * @param time 当前时间的最新交易点
     * @param flag 1为涨停 0为跌停
     */
    List<Map> getStockUpdownCount(@Param("openDate") Date openDate, @Param("time") Date time, @Param("flag") int flag);

5.mapperXml文件

<!--  这里不能使用java.util.Map  -->
    <select id="getStockUpdownCount" resultType="map">
        select
        date_format(tmp.time,'%Y%m%d%H%i') as time ,
        count(*) as count
        from (select
        (sri.cur_price-sri.pre_close_price)/sri.pre_close_price as ud,
        sri.cur_time as time
        from stock_rt_info sri
        where sri.cur_time BETWEEN #{openDate} and #{time}
        having ud
        <if test="flag==1">
            >=0.1
        </if>
        <if test="flag==0">
            &lt;=-0.1
        </if>
        )
        as tmp
        group by tmp.time
        order by tmp.time asc
    </select>

6.结果