ElasticSearch SQL 日期函数

秒速五厘米 2022-04-25 03:02 764阅读 0赞

# ElasticSearch SQL 日期函数 #

1、日期计算  
2、日期函数  
3、日期字符串转换(获取毫秒值、日期转字符串、字符串转日期)

对人工智能感兴趣的同学，可以点击以下链接：

[现在人工智能非常火爆，很多朋友都想学，但是一般的教程都是为博硕生准备的，太难看懂了。最近发现了一个非常适合小白入门的教程，不仅通俗易懂而且还很风趣幽默。所以忍不住分享一下给大家。点这里可以跳转到教程。][Link 1]

[https://www.cbedai.net/u014646662][Link 1]

## 1、日期计算 ##

<table style="width:600px;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> <code>1y</code> </td> 
 <td style="text-align:center;vertical-align:middle;"> <code>INTERVAL 1 YEAR</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>2M</code> </td> 
 <td style="vertical-align:top;"> <code>INTERVAL 2 MONTH</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>3w</code> </td> 
 <td style="vertical-align:top;"> <code>INTERVAL 21 DAY</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>4d</code> </td> 
 <td style="vertical-align:top;"> <code>INTERVAL 4 DAY</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>5h</code> </td> 
 <td style="vertical-align:top;"> <code>INTERVAL 5 HOUR</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>6m</code> </td> 
 <td style="vertical-align:top;"> <code>INTERVAL 6 MINUTE</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>7s</code> </td> 
 <td style="vertical-align:top;"> <code>INTERVAL 7 SECOND</code> </td> 
 </tr> 
 </tbody> 
</table>

### 当前日期 ###

select current_date;
          current_date      
    ------------------------
    2019-04-30T00:00:00.000Z

### 1.1 当前日期加一年 ###

select current_date + INTERVAL 1 YEAR;
    current_date + INTERVAL 1 YEAR
    ------------------------------
    2020-04-30T00:00:00.000Z

### 1.2 当前日期加一年三个月 ###

select current_date + INTERVAL '1-3' YEAR TO MONTH ; 
    current_date + INTERVAL '1-3' YEAR TO MONTH
    -------------------------------------------
    2020-07-30T00:00:00.000Z

### 当前时间 ###

select current_timestamp ;
       current_timestamp    
    ------------------------
    2019-04-30T10:54:01.951Z

### 1.3 当前时间加1小时 ###

select current_timestamp + interval 1 hour as "加一小时";
              加一小时          
    ------------------------
    2019-04-30T11:56:20.138Z

### 1.4 当前时间加一天两小时5分钟 ###

select current_timestamp + interval '1 2:5' day to minutes as "一天两小时5分钟";
            一天两小时5分钟        
    ------------------------
    2019-05-01T13:04:30.018Z

## 2、日期函数 ##

### 2.1 当前日期 CURRENT\_DATE / CURDATE ###

CURRENT_DATE
    CURRENT_DATE()
    CURDATE()

这三个没感觉有什么区别，硬说有什么区别，那就是CURRENT\_DATE是关键字，另两个是函数

select curdate() ;
           curdate()        
    ------------------------
    2019-04-30T00:00:00.000Z

### 2.2 当前时间CURRENT\_TIMESTAMP / NOW ###

select now();
             now()          
    ------------------------
    2019-04-30T12:23:37.741Z
    
    select CURRENT_TIMESTAMP();
      CURRENT_TIMESTAMP()   
    ------------------------
    2019-04-30T12:24:06.691Z

### 2.3 查看指定日期的天 DAY\_OF\_MONTH / DOM / DAY ###

select day_of_month(current_date) as result; 
        result     
    ---------------
    30    
    
    select dom(curdate()) as result;
        result     
    ---------------
    30 
    
    select dom(cast('2000-12-12T12:12:01' as timestamp)) as result;
        result     
    ---------------
    12

### 2.4 查看一周的第几天DAY\_OF\_WEEK  / DAYOFWEEK / DOW ###

周日为一周开始的第一天

select day_of_week(cast('2019-04-28T12:12:01' as timestamp)) as result;
        result     
    ---------------
    1              
    
    select dayofweek(current_timestamp() + interval 1 day) as result;
        result     
    ---------------
    4
    
    select dow(current_timestamp()) as result;
        result     
    ---------------
    3

### 2.5 查看一年当中第几天DAY\_OF\_YEAR / DOY ###

select doy(cast('2019-01-01' as datetime)) as result;
        result     
    ---------------
    1 
    
    select day_of_year(cast('2019-12-31' as datetime)) as result;
        result     
    ---------------
    365

### 2.6 查看星期几DAY\_NAME / DAYNAME ###

select day_name(cast('2019-01-01' as datetime)) as result;
        result     
    ---------------
    Tuesday

### 2.7 查看一天之中的时HOUR\_OF\_DAY / HOUR ###

select hour(cast('2019-01-01T23:09:00' as datetime)) as result;
        result     
    ---------------
    23

### 2.8 查看一周的第几天ISO\_DAY\_OF\_WEEK / ISODAYOFWEEK / ISODOW / IDOW ###

周一是第一天，与dow不一样，dow是从周日开始为第一天

select idow(current_timestamp()) as result;
        result     
    ---------------
    2

### 2.9 查看一年之中的第几周ISO\_WEEK\_OF\_YEAR / ISOWEEKOFYEAR / ISOWEEK / IWOY / IW ###

一月一日为第一周的第一天，即第一周是一月一日到一月七日

select iw(current_timestamp()) as result;
        result     
    ---------------
    18             
    
    select iw(cast('2019-01-01' as datetime)) as result;
        result     
    ---------------
    1              
    
    select iw(cast('2019-12-31' as datetime)) as result;
        result     
    ---------------
    53

从一月一日到到第一个周六为第一周，第一个周日为第二周

select iw(cast('2019-01-07' as datetime)) as result;
        result     
    ---------------
    1              
    
    select week(cast('2019-01-07' as datetime)) as result;
        result     
    ---------------
    2

### 2.10 产看分钟  ###

一天之中分钟

select MINUTE_OF_DAY (current_timestamp())                    ;
    MINUTE_OF_DAY (current_timestamp())
    -----------------------------------
    719

该小时的分钟

select MINUTE_OF_HOUR(current_timestamp());
    MINUTE_OF_HOUR(current_timestamp())
    -----------------------------------
    59                                 
    
    select MINUTE(current_timestamp())             ;
    MINUTE(current_timestamp())
    ---------------------------
    59

### 2.11 获取月份  ###

获取月份数：MONTH\_OF\_YEAR / MONTH

获取月份名：MONTH\_NAME /  MONTHNAME

select MONTH_OF_YEAR(current_timestamp());
    MONTH_OF_YEAR(current_timestamp())
    ----------------------------------
    4                                 
    
    select MONTH(current_timestamp())               ;
    MONTH(current_timestamp())
    --------------------------
    4                         
    
    select MONTH_NAME(current_timestamp());
    MONTH_NAME(current_timestamp())
    -------------------------------
    April                          
    
    select MONTHNAME(current_timestamp())              ;
    MONTHNAME(current_timestamp())
    ------------------------------
    April

### 2.12 获取秒 ###

SECOND\_OF\_MINUTE / SECOND

select SECOND_OF_MINUTE(current_timestamp());
    SECOND_OF_MINUTE(current_timestamp())
    -------------------------------------
    38                                   
    
    select SECOND(current_timestamp())           ;
    SECOND(current_timestamp())
    ---------------------------
    38

### 2.13 今天 TODAY ###

select today();
            today()         
    ------------------------
    2019-04-30T00:00:00.000Z

### 2.14 获取年 ###

select year(today());
     year(today()) 
    ---------------
    2019

### 2.15 获取季度 ###

select quarter(today()) as "季度";
          季度       
    ---------------
    2

### 2.16 执行日期时间函数  EXTRACT ###

SELECT EXTRACT(DAY_OF_YEAR FROM today()) as ex;
          ex       
    ---------------
    120            
    
    SELECT EXTRACT(day FROM today()) as ex;
          ex       
    ---------------
    30             
    
    SELECT EXTRACT(year FROM today()) as ex;
          ex       
    ---------------
    2019

## 3、日期字符串转换 ##

3.1 字符串转日期

select cast('2000-10-10' as datetime);
    cast('2000-10-10' as datetime)
    ------------------------------
    2000-10-10T00:00:00.000Z 
    
    select cast('2000-10-10T10:10:10' as timestamp);
    cast('2000-10-10T10:10:10' as timestamp)
    ----------------------------------------
    2000-10-10T10:10:10.000Z

3.2日期转字符串

select cast(CURRENT_TIMESTAMP as String);
    cast(CURRENT_TIMESTAMP as String)
    ---------------------------------
    2019-04-30T12:38:23.900Z

3.3 日起转数字（时间戳  毫秒值）

select cast(CURRENT_DATE as bigint);
    cast(CURRENT_DATE as bigint)
    ----------------------------
    1556582400000               
    
    select cast(CURRENT_TIMESTAMP as bigint);
    cast(CURRENT_TIMESTAMP as bigint)
    ---------------------------------
    1556627787089

[Link 1]: https://www.cbedai.net/u014646662