ElasticSearch SQL 日期函数

ElasticSearch SQL 日期函数

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

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

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

https://www.cbedai.net/u014646662

1、日期计算

1y	INTERVAL 1 YEAR
2M	INTERVAL 2 MONTH
3w	INTERVAL 21 DAY
4d	INTERVAL 4 DAY
5h	INTERVAL 5 HOUR
6m	INTERVAL 6 MINUTE
7s	INTERVAL 7 SECOND

当前日期

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