MySQL:日期时间函数-日期时间计算和转换
目录
MySQL Str to Date (字符串转换为日期)函数:str_to_date(str, format)
MySQL (日期、天数)转换函数:to_days(date), from_days(days)
MySQL (时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)
MySQL 拼凑日期、时间函数:makdedate(year,dayofyear), maketime(hour,minute,second)
FROM_UNIXTIME(unix_timestamp,format),DATE_FORMAT(date,format)中format
DATE_ADD(date,INTERVAL expr unit) 与 DATE_SUB(date,INTERVAL expr unit)
MySQL 日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)
MySQL 日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)
MySQL timediff(time1,time2):两个日期相减 time1 - time2,返回 time 差值。
MySQL 日期与时间方面的函数
函数名称 | 函数功能说明 |
---|---|
ADDDATE() | 添加日期 |
ADDTIME() | 添加时间 |
CONVERT_TZ() | 转换不同时区 |
CURDATE() | 返回当前日期 |
CURRENT_DATE() 与 CURRENT_DATE | 等同于 CURDATE() |
CURRENT_TIME() 与 CURRENT_TIME | 等同于 CURTIME() |
CURRENT_TIMESTAMP() 与 CURRENT_TIMESTAMP | 等同于 NOW() |
CURTIME() | 返回当前时间 |
DATE_ADD() | 添加两个日期 |
DATE_FORMAT() | 按指定方式格式化日期 |
DATE_SUB() | 求解两个日期的间隔 |
DATE() | 提取日期或日期时间表达式中的日期部分 |
DATEDIFF() | 求解两个日期的间隔 |
DAY() | 等同于 DAYOFMONTH() |
DAYNAME() | 返回星期中某天的名称 |
DAYOFMONTH() | 返回一月中某天的序号(1-31) |
DAYOFWEEK() | 返回参数所定影的一周中某天的索引值 |
DAYOFYEAR() | 返回一年中某天的序号(1-366) |
EXTRACT | 提取日期中的相应部分 |
FROM_DAYS() | 将一个天数序号转变为日期值 |
FROM_UNIXTIME() | 将日期格式化为 UNIX 的时间戳 |
HOUR() | 提取时间 |
LAST_DAY | 根据参数,返回月中最后一天 |
LOCALTIME() 和 LOCALTIME | 等同于 NOW() |
LOCALTIMESTAMP 和 LOCALTIMESTAMP() | 等同于 NOW() |
MAKEDATE() | 基于给定参数年份和所在年中的天数序号,返回一个日期 |
MAKETIME | MAKETIME() |
MICROSECOND() | 返回参数所对应的毫秒数 |
MINUTE() | 返回参数对应的分钟数 |
MONTH() | 返回传入日期所对应的月序数 |
MONTHNAME() | 返回月的名称 |
NOW() | 返回当前日期与时间 |
PERIOD_ADD() | 为年-月组合日期添加一个时段 |
PERIOD_DIFF() | 返回两个时段之间的月份差值 |
QUARTER() | 返回日期参数所对应的季度序号 |
SEC_TO_TIME() | 将描述转变成 'HH:MM:SS' 的格式 |
SECOND() | 返回秒序号(0-59) |
STR_TO_DATE() | 将字符串转变为日期 |
SUBDATE() | 三个参数的版本相当于 DATE_SUB() |
SUBTIME() | 计算时间差值 |
SYSDATE() | 返回函数执行时的时间 |
TIME_FORMAT() | 提取参数中的时间部分 |
TIME_TO_SEC() | 将参数转化为秒数 |
TIME() | 提取传入表达式的时间部分 |
TIMEDIFF() | 计算时间差值 |
TIMESTAMP() | 单个参数时,函数返回日期或日期时间表达式;有2个参数时,将参数加和 |
TIMESTAMPADD() | 为日期时间表达式添加一个间隔 INTERVAL |
TIMESTAMPDIFF() | 从日期时间表达式中减去一个间隔 INTERVAL |
TO_DAYS() | 返回转换成天数的日期参数 |
UNIX_TIMESTAMP() | 返回一个 UNIX 时间戳 |
UTC_DATE() | 返回当前的 UTC 日期 |
UTC_TIME() | 返回当前的 UTC 时间 |
UTC_TIMESTAMP() | 返回当前的 UTC 时间与日期 |
WEEK() | 返回周序号 |
WEEKDAY() | 返回某天在星期中的索引值 |
WEEKOFYEAR() | 返回日期所对应的星期在一年当中的序号(1-53) |
YEAR() | 返回年份 |
YEARWEEK() | 返回年份及星期序号 |
获得当前日期
select now(),
current_timestamp(),
current_timestamp,
CURDATE(),
CURTIME();
时间转换
MySQL Str to Date (字符串转换为日期)函数:str_to_date(str, format)
select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30
MySQL (日期、天数)转换函数:to_days(date), from_days(days)
select to_days('0000-00-00'); -- 0
select to_days('2008-08-08'); -- 733627
MySQL (时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)
select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'
MySQL 拼凑日期、时间函数:makdedate(year,dayofyear), maketime(hour,minute,second)
select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'
MySQL (Unix 时间戳、日期)转换函数
unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)
下面是示例:
select unix_timestamp(); -- 1218290027
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800
select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
select from_unixtime(1218169800); -- '2008-08-08 12:30:00'
FROM_UNIXTIME(unix_timestamp,format),DATE_FORMAT(date,format)中format
下表中列出了一些可用于 format 字符串的标识符。格式标识符第一个字符必须是%
字符。
格式标识符 | 说明 |
---|---|
%a | 一星期中每天名称的缩写(Sun...Sat) |
%b | 月份的缩写(Jan...Dec) |
%c | 月份的数字表现形式(0...12) |
%D | 带有英语后缀的一个月中的每一天的名称(0th、1st、2nd、3rd) |
%d | 用数字形式表现的每月中的每一天(00...31) |
%e | 用数字形式表现的每月中的每一天(0...31) |
%f | 毫秒(000000...999999) |
%H | 24时制显示的小时(00...23) |
%h | 12时制显示的小时(01...12) |
%I | 12时制显示的小时(01...12) |
%i | 以数字形式表现的分钟数(00...59) |
%j | 一年中的每一天(001...366) |
%k | 24时制小时的另一种表现格式(0...23) |
%l | 12时制小时的另一种表现格式(1...12) |
%M | 用完整英文名称表示的月份(January...December) |
%m | 用数字表现的月份(00...12) |
%p | 上午(AM)或下午(PM) |
%r | 12时制的时间值(hh:mm:ss,后跟 AM 或 PM) |
%S | 秒(00...59) |
%s | 秒(00...59) |
%T | 24时制的小时(hh:mm:ss) |
%U | 星期(00...53),其中星期天是每星期的开始日 |
%u | 星期(00...53),其中星期一是每星期的开始日 |
%V | 星期(01...53),其中星期天是每星期的开始日,和 %X 一起使用 |
%v | 星期(01...53),其中星期一是每星期的开始日,和 %x 一起使用 |
%W | 一星期中各日名称(Sunday...Saturday) |
%w | 一星期中各日名称(0代表星期日,6代表星期六,以此类推) |
%X | 某星期所处年份。其中,星期天是每星期的开始日,采用4位数字形式表现,和 %V 一起使用 |
%x | 某星期所处年份。其中,星期一是每星期的开始日,采用4位数字形式表现,和 %V 一起使用 |
%Y | 4位数字表示的年份 |
%y | 2位数字表示的年份 |
%% | 符号% 的字面值 |
%x(x为斜体) | 字符x的字面值,x指以上未列出的任何字符 |
MySQL 日期时间计算函数
MySQL 为日期增加一个时间间隔:date_add()
set @dt = now();
select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
MySQL 为日期减去一个时间间隔:date_sub()
DATE_ADD(date,INTERVAL expr unit) 与 DATE_SUB(date,INTERVAL expr unit)
执行日期计算的两种函数。date
是一个用来指定开始日期的 DATETIME 或 DATE 值。expr
是一种以字符串形式呈现的表达式,用来指定从开始日期增加或减少的间隔值。如果是负的间隔值,则 expr
值的第一个字符是-
号。unit
是一个单位关键字,用来指定expr表达式应该采取的单位。
INTERVAL 关键字与单位说明符都不区分大小写。
下表列出了每个单位数值所对应的 expr 参数的期望格式。
单位所能取的值 | 期望的expr格式 |
---|---|
MICROSECOND | 毫秒 |
SECOND | 秒 |
MINUTE | 分 |
HOUR | 小时 |
DAY | 日 |
WEEK | 周 |
MONTH | 月 |
QUARTER | 季度 |
YEAR | 年 |
SECOND_MICROSECOND | '秒.毫秒' |
MINUTE_MICROSECOND | '分.毫秒' |
MINUTE_SECOND | '分:秒' |
HOUR_MICROSECOND | '小时.毫秒' |
HOUR_SECOND | '小时:分:秒' |
HOUR_MINUTE | '小时:分' |
DAY_MICROSECOND | '日.毫秒' |
DAY_SECOND | '日 小时:分:秒' |
DAY_MINUTE | '日 小时:分' |
DAY_HOUR | '日 小时' |
YEAR_MONTH | '年-月' |
MySQL 日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)
MySQL 日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)
-- datediff(date1,date2):两个日期相减 date1 - date2,返回天数。
select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7
MySQL timediff(time1,time2):两个日期相减 time1 - time2,返回 time 差值。
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08
注意:timediff(time1,time2) 函数的两个参数类型必须相同。
MySQL 时间戳(timestamp)转换、增、减函数:
timestamp(date) -- date to timestamp timestamp(dt,time) -- dt + time timestampadd(unit,interval,datetime_expr) -- timestampdiff(unit,datetime_expr1,datetime_expr2) --
请看示例部分:
select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01
select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00
-- timestampadd() 函数类似于 date_add()。
select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12
select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7
MySQL timestampdiff() 函数就比 datediff() 功能强多了,datediff() 只能计算两个日期(date)之间相差的天数。
MySQL 时区(timezone)转换函数
-- convert_tz(dt,from_tz,to_tz)
select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00
时区转换也可以通过 date_add, date_sub, timestampadd 来实现。
select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00
参考链接:https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html
相关文章
- mysql索引总结(4)-MySQL索引失效的几种情况
- mysql索引总结(3)-MySQL聚簇索引和非聚簇索引
- MySQL exercise 文档
- MySQL第六讲 MySQL分库分表方案
- mysql+redis
- mysql基础
- MySQL 日期和时间戳互相转换
- mysql日期/时间转换为字符串
- Mysql的安全配置向导命令mysql_secure_installation
- MySQL中日期和时间戳互相转换的函数和方法
- MySQL中间件之ProxySQL(6):管理后端节点
- Mysql:从一个USE DB堵塞故障展开的探讨
- mysql高级之索引
- MySQL存储过程详解 mysql 存储过程
- mysql—MySQL数据库中10位时间戳转换为标准时间后,如何对标准时间进行加减X天处理
- mysql—MySQL数据库中10位或13位时间戳和标准时间相互转换
- ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist
- (5.4)mysql高可用系列——MySQL异步复制(实践)
- 【0.1】mysql版本升级(5.6升级到5.7),mysql5.6安装
- Mysql启动时提示:Another MySQL daemon already running with the same unix socket.
- 【C#】使用MySql.Data.dll连接MySQL数据库
- MySQL 安装mysql数据库
- python封装一个工具类 ,对MySQL数据库增删改查,可多字段动态插入mysql数据库中