本文简单讲解我们在编写sql时会使用到的有关日期的sql写法: 首先要介绍常用的日期类的函数,其次在根据函数来演示常见的日期操作的写法。 以下函数或者关键字加0(+0)后会将日期时间、日期、时间、字符串转换为数字! 目录 一、常用函数: - 1 - 1、获取当前日期和时间 - 1 - 2、 获取当前日期 - 1 - 3、 获取当前时间 - 1 - 4、 返回指定日期/时间表达式的日期部分或将文本转为日期格式 - 2 - 5、 返回日期时间中的年、月、日、时、分、秒。 - 2 - 6、 返回日期时间对应的季节、一年的第几天、月份名称、星期几的索引和星期名称。 - 2 - 7、 根据日期时间返回时间戳 - 2 - 8、 根据时间戳返回日期时间 - 3 - 9、 返回日期的天数(从0开始计算) - 3 - 10、 格式化日期(日期时间转换为字符串) - 3 - 11、 将字符串转换为日期时间 - 3 - 12、 返回两个日期时间的时间差 - 4 - 13、 返回两个日期的相差天数 - 4 - 14、 返回两个日期时间的间隔时间 - 4 - 15、 对日期时间进行加减运算 - 4 - 二、 操作篇: - 5 - 使用now()函数可以获取当前系统的日期和时间 例:select now(); --2022-08-17 14:13:20 select now()+0; --20220817141320 使用current_date关键字或者curdate()、current_date()函数可以获取当前系统的日期 例:select curdate(); --2022-08-17 select curdate()+0; --20220817 select current_date; --2022-08-17 select current_date(); --2022-08-17 使用current_time关键字或者curtime()、current_time()函数可以获取当前系统的时间 例:select curtime(); --14:13:20 select curtime()+0; --141320 select current_time; --14:13:20 select current_time(); --14:13:20 使用date()函数可以获取日期部分 例:select date('2022-7-8'); --2022-07-08 select date('2022-7-8')+0; --20220708 select date(now()); --2022-07-08 *这里返回的是日期格式! ①year()函数返回年份1000~9999 例:select year('2022-8-17 11:30:48'); --2022 ②month()函数返回月份1~12 例:select month('2022-8-17 11:30:48'); --8 ③day()/dayofmonth()函数返回第几日1~31 例:select day('2022-8-17 11:30:48'); --17 ④hour()函数返回小时0~23 例:select hour('2022-8-17 11:30:48'); --11 ⑤minute()函数返回分0~59 例:select minute('2022-8-17 11:30:48'); --30 ⑥second()函数返回秒0~59 例:select second('2022-8-17 11:30:48'); --48 ①quarter()函数返回季节1~4 (春、夏、秋、东) 例:select quarter('2022-8-22 16:07:51'); --3 ②dayofyear()函数返回一年的第几天1~366 例:select dayofyear('2022-8-22 16:07:51'); --234 ③monthname()函数返回月份对应的名称(英文) 例:select monthname('2022-8-22 16:07:51'); --August (8月) ④weekday()函数返回星期索引(0=星期一,1=星期二, ……6= 星期天) 例:select weekday('2022-8-22 16:07:51'); --0 (星期一) ⑤dayofweek()函数返回星期索引(1=星期天,2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。 例:select dayofweek('2022-8-22 16:07:51'); --2 (星期一) ⑥dayname()函数返回星期的名称(英文) 例:select dayname('2022-8-22 16:07:51'); --Monday (星期一) 函数:unix_timestamp([date]) 函数说明:返回一个unix时间戳(从'1970-01-01 00:00:00'开始的秒数,date默认值为当前时间) 参数说明:date 日期时间(此参数不填则为系统时间) 例:select unix_timestamp(); --1660895587 select unix_timestamp('2022-8-19 15:53:01'); --1660895581 函数:from_unixtime(unix_timestamp) 函数说明:以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符串或数字) 参数说明:unix_timestamp 时间戳 例:select from_unixtime(1660895581); --2022-08-19 15:53:01 select from_unixtime(1660895581)+0; --20220819155301 函数:to_days(datetime) 函数说明:返回0到指定日期的天数 参数说明:datetime 日期/日期时间 例:select to_days(now()); --738754 select to_days('2022-8-22'); --738754 函数:date_format(date,format) 函数说明: 根据格式串format 格式化?期或?期和时间值date,返回结果串(一般情况用户获取日期的年月日和时间) 参数说明: date 日期/日期时间 format 格式串 在format格式串中可用标志符: %M 月名字(january……december) %b 缩写的月份名字(jan……dec) %Y 年,数字,4位 %y 年,数字,2位 %d 月份中的天数,数字(00,01,...31) %e 月份中的天数,数字(0,1,...31) %m 月,数字(01,02,...12) %c 月,数字(1,2,...12) %h 十二时制的小时(00,01,...12) %k 二十四时制的小时(0,1,...23) %i 分钟, 数字(00,01,...59) %r 时间,12 小时(hh:mm:ss [ap]m) %s 秒(00……59) %p am或pm %j 一年中的天数(001,002,...366) %w 一个星期中的天数(0=sunday(星期天) ...6=saturday(星期六) ) %u 一年中的周数(1,2,...53) 例: select date_format('2022-3-11 12:15:51','%Y-%m-%d'); --2022-03-11 select date_format('2022-3-11 12:15:51','%r@@@%M###%w'); --12:15:51 PM@@@March###5 select date_format(now(),'%k:%i:%s'); --9:21:50 函数:str_to_date(str,format) 函数说明:将指定的时间格式的字符串按照格式转换为日期时间类型的值。str要与format的格式保持一致,否则会报错。 参数说明: str 时间格式的字符串 format 格式串 在format格式串中可用标志符: %Y 年,数字,4位 %s 秒(00……59) %p am或pm %d 月份中的天数,数字(00,01,...31) %e 月份中的天数,数字(0,1,...31) %m 月,数字(01,02,...12) %c 月,数字(1,2,...12) %h 十二时制的小时(00,01,...12) %k 二十四时制的小时(0,1,...23) %i 分钟, 数字(00,01,...59) %r 时间,12 小时(hh:mm:ss [ap]m) 例: select str_to_date('2022-8-21','%Y-%m-%d'); --2022-08-21 select str_to_date('2022-8-1 1:20:51','%Y-%c-%e %r'); --2022-08-01 01:20:51 函数:timestampdiff(type,expr1,expr2) 函数说明:返回起始日expr1和结束日expr2之间的时间差整数。时间差的单位由type指定 参数说明: type 时间差单位 expr1 起始日期/日期时间 expr2 结束日期/日期时间 type中的时间差单位如下: second 秒 day 天 minute 分 month 月 hour 时 year 年 例: select timestampdiff(hour,'2022-8-18 12:45:12','2022-8-18 16:12:51'); --3 select timestampdiff(day,'2022-8-18','2022-8-20'); --2 select timestampdiff(day,'2021-8-18','2022-8-20'); --367 select timestampdiff(day,'2022-8-20 17:45:51','2022-8-22 16:35:51'); --1 *这里为什么不是2?因为起始日期是从17:45:51开始计算的,结束日期的时分秒没超过这个时间就不足一天 函数:datediff(date1 ,date2) 函数说明:计算两个日期间隔的天数,即#date1 - date2 参数说明:date1 日期1 date2 日期2 例:select datediff('2022-8-31','2022-7-11'); --51 select datediff('2022-8-31 2:10:10','2022-7-11 4:10:10'); --51 函数说明:timediff(time1,time2) 函数说明:计算两个时间的间隔时间,即time1-time2。注意的是两个时间的格式必须一致! 参数说明:time1 时间1 time2 时间2 例:select timediff('10:15:21','6:02:12'); --04:13:09 函数:① adddate(date,interval expr type) -加 ② date_add(date,interval expr type) -加 ③ subdate(date,interval expr type) -减 ④ date_sub(date,interval expr type) -减 以上函数说明:其中,date是一个日期或日期时间的值;expr是对date进行加减法的一个表达式字符串或一个数字;type指明表达式expr应该如何被解释,是减去1天还是一年等。 type和expr的对应关系: type值 含义 expr格式 second 秒 seconds minute 分钟 minutes hour 时 hours day 天 days month 月 months year 年 years Minute_second 分钟和秒 “minutes:seconds” Hour_minute 小时和分钟 “hours:minutes” day_hour 天和小时 “days-hours” year_month 年和月 “years-months” hour_second 小时,分钟和秒 “hours:minutes:seconds” day_minute 天,小时和分钟 “days hours:minutes” day_second 天,小时,分钟和秒 “days hours:minutes:seconds” 例: select adddate('2022-8-19 15:22:12',interval 2 hour); --2022-08-19 17:22:12 select adddate('2022-8-19 15:22:12',interval '10-4' year_month); --2032-12-19 15:22:12 select date_add('2022-8-19 15:22:12',interval '2 2:12' day_minute); --2022-08-21 17:34:12 select date_sub('2022-8-19',interval 6 day); --2022-08-13 select subdate('2022-8-19 15:22:12',interval '6-14' day_hour); --2022-08-13 01:22:12 1、一、常用函数:
1、获取当前日期和时间
获取当前日期
获取当前时间
返回指定日期/时间表达式的日期部分或将文本转为日期格式
返回日期时间中的年、月、日、时、分、秒。
返回日期时间对应的季节、一年的第几天、月份名称、星期几的索引和星期名称。
根据日期时间返回时间戳
根据时间戳返回日期时间
返回日期的天数(从0开始计算)
格式化日期(日期时间转换为字符串)
将字符串转换为日期时间
返回两个日期时间的时间差
返回两个日期的相差天数
返回两个日期时间的间隔时间
对日期时间进行加减运算
操作篇: