1、获取当前时刻时间
获取当前时刻的时间就是获取程序运行的那一刻与时间相关的数据,比如年月日、时分秒等信息。
1.1 返回当前时刻的日期和时间
返回当前时刻的日期和时间在ESql中用的是now()
函数,直接在select
后面写上now()
函数即可,具体代码如下:
select now()
运行上面代码就会得到程序运行这一刻所处的年月日、时分秒的信息,比如:2019-12-25 22:47:37。
1.2 获取当前时刻的日期
前面的now()
函数获取的是当前时刻的日期和时间,有的时候可能只需要当前时刻的日期部分,并不需要时间部分,这个时候在在SQL中将now()
函数换成curdate()
函数,就是获取当前时刻的日期部分,具体代码如下:
select curdate()
通过运行上面的代码,得到了当前时刻的日期部分:2019-12-25curdate()
函数是直接获取当前时刻的日期部分,也可以先通过now()
函数获取当前时刻的日期时间,然后再通过date()
函数将日期时间转化为日期部分,具体代码如下:
select date(now())
通过运行上面的代码,会得到与curdate()
函数相同的结果。
也可以只获取日期中的年,使用的是year()
函数,具体代码如下:
select year(now())
通过运行上面的代码,最后得到的结果为2019。
也可以只获取日期中的月,使用的是month()
函数,具体代码如下:
select month(now())
通过运行上面的代码,最后得到的结果为12。
也可以只获取日期中的日,使用的是day()
函数,具体代码如下:
select day(now())
1.3 获取当前时刻的时间
除了有只获取当前时刻的日期的需求外,还有只获取当前时刻的时间需求。如果想只获取当前时刻的时间,只需要把只获取当前时刻日期的curdate()
函数换成curtime()
函数即可,具体代码如下:
select curtime()
通过运行上面的代码,就可以获取当前时刻的时间部分:22:47:37。
也可以先通过now()
函数获取当前时刻的日期时间,然后再通过time()
函数将日期时间转化为时间部分,具体代码如下:
select time(now())
通过运行上面的代码,会得到与curdate()
函数相同的结果。
也可以只获取时间中的小时,使用的是hour()
函数,具体代码如下:
select hour(now())
通过运行上面的代码,最后得到的结果为22。
也可以只获取时间中的分钟,使用的是minute()
函数,具体代码如下:
select minute(now())
通过运行上面的代码,最后得到的结果为47。
也可以只获取时间中的秒,使用的是second()
函数,具体代码如下:
select second(now())
1.4 获取当前时刻的周数
上面讲了如何获取当前时刻的日期时间、日期、时间这三部分。这一节再看下如何获取当前时刻所属的周数。一般会将全年分为52周(365天/7),有的时候也可能是53周,如果想看下当前时刻是全年中的第几周,可以使用weekofyear()
函数,具体代码如下:
select weekofyear(now())
通过运行上面的代码,最后得到的结果为52。
除了获取当前是全年的第几周以外,还需要获取当天是一周内的周几。在SQL使用的dayofweek()
函数,具体代码如下:
select dayofweek(now())
通过运行上面的代码,最后得到结果为5,2019年12月25日应该是周四哈,为什么结果是5呢,这是因为该函数中一周是从周日开始的,也就是周日对应的是1,周一对应的是2,以此类推,所以周四对应的是5。
2、日期时间格式转换
同一个日期时间会有多种不同的表示方式,有的时候需要在不同格式之间相互转换。在SQL中用的是date_format()
函数,date_format
函数格式如下:
date_format(datetime,format)
datetime
表示要被转换的具体的日期时间,format
表示要转换成的格式,可选的格式如下:
主题 | 格式 | 描述 |
---|---|---|
年 | %Y | 4位数的年 |
月 | %b | 月份对应的英文缩写 |
月 | %M | 月份对应的英文全称 |
月 | %m | 01-12的月 |
月 | %c | 1-12的月份数值 |
日 | %d | 01-31的某月里面的第几天 |
日 | %e | 1-31的某月里面的第几天 |
日 | %D | 用th后缀表示某月中的第几天 |
日 | %j | 001-366的一年中的第几天 |
周 | %a | 星期几对应的英文缩写 |
周 | %W | 星期几对应的英文全称 |
时 | %H | 00-23的小时 |
时 | %h | 01-12的小时 |
分 | %i | 00-59的分钟 |
秒 | %S | 秒(00-59) |
秒 | %f | 微秒 |
时分秒 | %T | 返回当前的时分秒, 24-小时 (hh:mm:ss) |
select date_format("2019-12-25 22:47:37","%Y-%m-%d")
通过运行上面的代码,就会返回4位数的年、01-12的月、01-31的天,三者之间且用-分隔开来,即2019-12-25。
这里需要注意下1和01的区别,本质上都是表示的1,但是展示上会有些不太一样,比如下面代码中,原日期是2019-1-1,但是返回的结果是2019-01-01的。
select date_format("2019-1-1 22:47:37","%Y-%m-%d")
select date_format("2019-12-25 22:47:37","%H:%i:%S")
通过运行上面的代码就会返回00-23的小时、00-59的分、00-59的秒,三者之间用:分隔开来,即22:47:37。
除了date_format()
函数以外,还有另外一个函数extract
,用于返回一个具体日期时间中的单独部分,比如年、月、日、小时、分钟等等。具体形式如下:
extract(unit from datetime)
datetime
表示具体的日期时间,unit
表示要从datetime
中返回的单独的部分。unit
值可以是下列的值:
unit | 说明 |
---|---|
year | 年 |
month | 月 |
day | 日 |
hour | 小时 |
minute | 分钟 |
second | 秒 |
week | 周数,全年第几周 |
select
extract(year from "2019-12-25 22:47:37") as col1
,extract(month from "2019-12-25 22:47:37") as col2
,extract(day from "2019-12-25 22:47:37") as col3
通过运行上面的代码,就会分别获取到datetime中的年月日,具体结果如下:
col1 | col2 | col3 |
---|---|---|
2019 | 12 | 25 |
3、日期时间运算
有的时候也需要对日期之间进行运算,比如要获取今天往前7天对应的日期,或者今天往后13天对应的日期,可以去翻日历,也可以去数数,但是这些方法肯定都不是最直接的方法。所以需要日期之间的运算。
3.1 向后偏移时间
比如要获取今天之后的x天对应的日期,就是相当于在今天日期的基础上加x天,把这叫做向后偏移,这个时候就可以使用date_add()
函数,具体形式如下:
date_add(date,interval num unit)
date表示当前的日期,或者当前的日期时间;interval
是一个固定的参数;num
为上面讲到的x
;unit
表示要加的单位,是往后移动7天,还是7月,还是7年,可选值与extract
函数中unit
的可选值是一样的。
select
"2019-01-01" as col1
,date_add("2019-01-01",interval 7 year) as col2
,date_add("2019-01-01",interval 7 month) as col3
,date_add("2019-01-01",interval 7 day) as col4
通过运行上面的代码,就会返回2019-01-01往后7年、7月、7天对应的日期,具体结果如下:
col1 | col2 | col3 | col4 |
---|---|---|---|
2019-01-01 | 2026-01-01 | 2019-08-01 | 2019-01-08 |
select
"2019-01-01 01:01:01" as col1
,date_add("2019-01-01 01:01:01",interval 7 hour) as col2
,date_add("2019-01-01 01:01:01",interval 7 minute) as col3
,date_add("2019-01-01 01:01:01",interval 7 second) as col4
通过运行上面的代码,就会返回2019-01-01 01:01:01往后7小时、7分钟、7秒对应的日期,具体结果如下:
col1 | col2 | col3 | col4 |
---|---|---|---|
2019-01-01 01:01:01 | 2019-01-01 08:01:01 | 2019-01-01 01:08:01 | 2019-01-01 01:01:08 |
3.2 向前偏移时间
有向后偏移,就会有向前偏移。比如要获取今天之前的若干天,就是相当于是在当前日期的基础上减去x天,这个时候使用的是date_sub()
函数,date_sub
与date_add
的函数形式是一样的。把上面代码中的date_add
换成date_sub
就表示向前偏移。
select
"2019-01-01" as col1
,date_sub("2019-01-01",interval 7 year) as col2
,date_sub("2019-01-01",interval 7 month) as col3
,date_sub("2019-01-01",interval 7 day) as col4
通过运行上面的代码,就会返回2019-01-01往前7年、7月、7天对应的日期,具体结果如下:
col1 | col2 | col3 | col4 |
---|---|---|---|
2019-01-01 | 2012-01-01 | 2018-06-01 | 2018-12-25 |
向前偏移指定的时间,除了使用date_sub
以外,还可以继续使用date_add
,只不过把加的具体num
值换成负数就行,比如7换成-7即可,具体实现代码如下:
select
"2019-01-01" as col1
,date_add("2019-01-01",interval -7 year) as col2
,date_add("2019-01-01",interval -7 month) as col3
,date_add("2019-01-01",interval -7 day) as col4
通过运行上面的结果与使用date_sub
得出来的结果是一致的。
3.3 两日期相差
上面讲完了向前偏移、向后偏移,有的时候还需要获取两日期之差,使用的datediff()
函数,datediff
用于返回两日期之间相差的天数,函数形式如下:
datediff(end_date,start_date)
用end_date
去减start_date
的。
select datediff("2019-01-07","2019-01-01")
通过运行上面的代码,会返回2019-01-07与2019-01-01之间的天数差,结果为6。