编程笔记

lifelong learning & practice makes perfect

es|查询在某个时间段过生日的人

发表于 更新于
本文字数： 6k 阅读时长 ≈ 5 分钟

需求

业务需求,查询在指定时间段过生日的人.如 2-28 到 9-15 之间过生日的人

解决方案

业务存储有生日的毫秒级时间戳 ‘birthday’

mysql

1
2
3
-- 有局限,不支持跨年查询 比如 12-05 ~ 01-29
select * from user where 
DATE_FORMAT(FROM_UNIXTIME(birthday/1000),'%m-%d') BETWEEN '02-28' AND '09-15';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查询最近七天过生日的人,支持跨年
SELECT * 
FROM  user 
WHERE
DATE_ADD(FROM_UNIXTIME(birthday/1000), 
            INTERVAL YEAR(CURDATE())-YEAR(FROM_UNIXTIME(birthday/1000))
                        + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(FROM_UNIXTIME(birthday/1000)),1,0) YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY);

-- 查询 12-04 到 下一年1-4日过生日的
SELECT * 
FROM  user 
WHERE
DATE_ADD(FROM_UNIXTIME(birthday/1000), 
            INTERVAL YEAR(FROM_UNIXTIME(1701662559000/1000))-YEAR(FROM_UNIXTIME(birthday/1000)) + IF(DAYOFYEAR(FROM_UNIXTIME(1701662559000/1000)) > DAYOFYEAR(FROM_UNIXTIME(birthday/1000)),1,0) YEAR)  
BETWEEN FROM_UNIXTIME(1701662559000/1000) AND FROM_UNIXTIME(1704340959000/1000);

es

方案一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 需要过滤没有生日字段的记录(字段值>0 且有该字段) 同样不支持跨年
GET user/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "exists": {"field": "birthday"}
        },
        {
          "script": {
            "script": {
              "source": 
              """
              def date = Instant.ofEpochMilli(doc['birthday'].value).atZone(ZoneId.of("Asia/Shanghai"));
              def month = date.getMonthValue();
              def day = date.getDayOfMonth();
              return (month == 2  && day >= 28) || (month > 2 && month < 9) || (month == 9 && day <= 15);
              """
            }
          }
        },
        {
          "range": {
            "birthday": {
            "gt": 0
            }
          }
        }
      ]
    }
  }
}

方案二

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- 指定范围 start,end 
-- 按时间戳比较,支持跨年 获取生日所在的月份日期 如 02-15,将其转为比较起始日期所在年份对应的日期称为a
-- 再拿 a 和 start, end比较
GET user/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "exists": {"field": "birthday"}
        },
        {
          "script": {
            "script": {
              "source": 
              """
                def start=1701662559000L;
                def end=1704340959000L;
                def startTime = Instant.ofEpochMilli(start).atZone(ZoneId.of("Asia/Shanghai"));
                def endTime= Instant.ofEpochMilli(end).atZone(ZoneId.of("Asia/Shanghai")); 
                def birthdayTime = Instant.ofEpochMilli(doc['birthday'].value).atZone(ZoneId.of("Asia/Shanghai"));
                def birthdayDate = birthdayTime.toLocalDate();
                def startTimeDate=startTime.toLocalDate();
                def endTimeDate=endTime.toLocalDate();          
                def a=LocalDate.of(startTimeDate.getYear(), birthdayDate.getMonthValue(),birthdayDate.getDayOfMonth())
                    .atStartOfDay().atZone(ZoneId.of("Asia/Shanghai"));
                def b=LocalDate.of(endTimeDate.getYear(), birthdayDate.getMonthValue(),birthdayDate.getDayOfMonth())
                    .atStartOfDay().atZone(ZoneId.of("Asia/Shanghai"));        
                return (a.isAfter(startTime) && a.isBefore(endTime)) || (b.isAfter(startTime) && b.isBefore(endTime));
              """
            }
          }
        },
        {
          "range": {
            "birthday": {
            "gt": 0
            }
          }
        }
      ]
    }
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
// script 对应的java代码
long start=1706517776000L;
long end=1714809776000L;
ZonedDateTime startTime = Instant.ofEpochMilli(start).atZone(ZoneId.of("Asia/Shanghai"));
ZonedDateTime endTime= Instant.ofEpochMilli(end).atZone(ZoneId.of("Asia/Shanghai"));
ZonedDateTime birthdayTime = Instant.ofEpochMilli(228096000000L).atZone(ZoneId.of("Asia/Shanghai"));
LocalDate birthdayDate = birthdayTime.toLocalDate();
LocalDate  startTimeDate=startTime.toLocalDate();
LocalDate  endTimeDate=endTime.toLocalDate();
ZonedDateTime a=LocalDate.of(startTimeDate.getYear(), birthdayDate.getMonthValue(),birthdayDate.getDayOfMonth())
    .atStartOfDay().atZone(ZoneId.of("Asia/Shanghai"));
ZonedDateTime b=LocalDate.of(endTimeDate.getYear(), birthdayDate.getMonthValue(),birthdayDate.getDayOfMonth())
    .atStartOfDay().atZone(ZoneId.of("Asia/Shanghai"));
return (a.isAfter(startTime) && a.isBefore(endTime))||(b.isAfter(startTime) && b.isBefore(endTime));

使用go的package(“github.com/olivere/elastic/v7”)实现上述查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
boolQuery := elastic.NewBoolQuery().Filter(
   elastic.NewRangeQuery("birthday").Gt(0),
   elastic.NewExistsQuery("birthday"),
   elastic.NewScriptQuery(elastic.NewScript(fmt.Sprintf(
               `def start=%dL;
                def end=%dL;
                def startTime = Instant.ofEpochMilli(start);
                def endTime= Instant.ofEpochMilli(end); 
                def birthdayTime = Instant.ofEpochMilli(1702662559000L);
                def birthdayDate = birthdayTime.atZone(ZoneId.of("Asia/Shanghai")).toLocalDate();
                def startTimeDate=startTime.atZone(ZoneId.of("Asia/Shanghai")).toLocalDate();
                def endTimeDate=endTime.atZone(ZoneId.of("Asia/Shanghai")).toLocalDate();          
                def a=LocalDate.of(startTimeDate.getYear(), birthdayDate.getMonthValue(),birthdayDate.getDayOfMonth())
                    .atStartOfDay().atZone(ZoneId.of("Asia/Shanghai")).toInstant();
                def b=LocalDate.of(endTimeDate.getYear(), birthdayDate.getMonthValue(),birthdayDate.getDayOfMonth())
                    .atStartOfDay().atZone(ZoneId.of("Asia/Shanghai")).toInstant();        
                return (a.isAfter(startTime) && a.isBefore(endTime))||(b.isAfter(startTime) && b.isBefore(endTime));`,
                birthdayStart, birthdayEnd)),
  ))

 // 构建搜索请求
 searchResult, err := esClient.Search().Index("user").Query(boolQuery).Do(context.Background())
 // 处理搜索结果
 if err != nil {
  fmt.Printf("Error executing search: %v\n", err)
  return
 }
 // 遍历搜索结果
 for _, hit := range searchResult.Hits.Hits {
  // 处理每个文档的 hit
  fmt.Printf("Document ID: %s\n", hit.Id)
 }

参考

  • stackoverflow

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    -- If you want to exclude today's birthdays just change > to >=
    SELECT *
    FROM  persons
    WHERE  DATE_ADD(birthday,
                    INTERVAL YEAR(CURDATE())-YEAR(birthday)
                             + IF(DAYOFYEAR(CURDATE()) >= DAYOFYEAR(birthday),1,0)
                    YEAR)  
                BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

    -- Same as above query with another way to exclude today's birthdays
    SELECT *
    FROM  persons
    WHERE  DATE_ADD(birthday,
                    INTERVAL YEAR(CURDATE())-YEAR(birthday)
                             + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                    YEAR)
                BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
         AND DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) YEAR) <> CURDATE();

    -- Same as above query with another way to exclude today's birthdays
    SELECT *
    FROM  persons
    WHERE  DATE_ADD(birthday,
                    INTERVAL YEAR(CURDATE())-YEAR(birthday)
                             + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                    YEAR)
                BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
         AND (MONTH(birthday) <> MONTH(CURDATE()) OR DAY(birthday) <> DAY(CURDATE()));

  • v2ex,取最近 10 天内要过生日的用户,sql 该怎么写

欢迎关注我的其它发布渠道