189 8069 5689

mysql中生日日期怎么表示

这篇文章主要介绍了MySQL中生日日期怎么表示的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇mysql中生日日期怎么表示文章都会有所收获,下面我们一起来看看吧。

公司主营业务:做网站、成都网站建设、移动网站开发等业务。帮助企业客户真正实现互联网宣传,提高企业的竞争能力。创新互联是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化,感谢他们对我们的高要求,感谢他们从不同领域给我们带来的挑战,让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联推出蚌山免费做网站回馈大家。

mysql中生日可用类型:1、DATE类型,可表示的日期格式为“YYYY-MM-DD”,在存储时需要3个字节;2、DATETIME类型,可表示的日期格式为“YYYY-MM-DD HH:MM:SS”,在存储时需要8个字节;3、TIMESTAMP类型,可表示的日期格式为“YYYY-MM-DD HH:MM:SS”,在存储时需要4个字节。

MySQL中适合生日的日期与时间类型

类型名称日期格式日期范围存储需求
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-33 个字节
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:598 个字节
TIMESTAMPYYYY-MM-DD HH:MM:SS1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC4 个字节

DATE 类型

DATE 类型用于仅需要日期值时,没有时间部分,在存储时需要 3 个字节。日期格式为 'YYYY-MM-DD',其中 YYYY 表示年,MM 表示月,DD 表示日。

在给 DATE 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATE 的日期格式即可。如下所示:

  • 以 'YYYY-MM-DD' 或者 'YYYYMMDD' 字符中格式表示的日期,取值范围为 '1000-01-01'~'9999-12-3'。例如,输入 '2015-12-31' 或者 '20151231',插入数据库的日期为2015-12-31。

  • 以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期,在这里YY表示两位的年值。MySQL 解释两位年值的规则:'00~69' 范围的年值转换为 '2000~2069','70~99' 范围的年值转换为 '1970~1999'。例如,输入 '15-12-31',插入数据库的日期为 2015-12-31;输入 '991231',插入数据库的日期为 1999-12-31。

  • 以 YYMMDD 数字格式表示的日期,与前面相似,00~69 范围的年值转换为 2000~2069,80~99 范围的年值转换为 1980~1999。例如,输入 151231,插入数据库的日期为 2015-12-31,输入 991231,插入数据库的日期为 1999-12-31。

  • 使用 CURRENT_DATE 或者 NOW(),插入当前系统日期。

提示:MySQL 允许“不严格”语法:任何标点符号都可以用作日期部分之间的间隔符。例如,'98-11-31'、'98.11.31'、'98/11/31'和'98@11@31' 是等价的,这些值也可以正确地插入数据库。

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值,在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH:MM:SS',其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。

在给 DATETIME 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATETIME 的日期格式即可,如下所示:

  • 以 'YYYY-MM-DD HH:MM:SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期,取值范围为 '1000-01-01 00:00:00'~'9999-12-3 23:59:59'。例如,输入 '2014-12-31 05:05:05' 或者 '20141231050505’,插入数据库的 DATETIME 值都为 2014-12-31 05:05:05。

  • 以 'YY-MM-DD HH:MM:SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期,在这里 YY 表示两位的年值。与前面相同,'00~79' 范围的年值转换为 '2000~2079','80~99' 范围的年值转换为 '1980~1999'。例如,输入 '14-12-31 05:05:05',插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。

  • 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如,输入 20141231050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05;输入 140505050505,插入数据库的 DATETIME 为 2014-12-31 05:05:05。

提示:MySQL 允许“不严格”语法:任何标点符号都可用作日期部分或时间部分之间的间隔符。例如,'98-12-31 11:30:45'、'98.12.31 11+30+35'、'98/12/31 11*30*45' 和 '98@12@31 11^30^45' 是等价的,这些值都可以正确地插入数据库。

TIMESTAMP 类型

TIMESTAMP 的显示格式与 DATETIME 相同,显示宽度固定在 19 个字符,日期格式为 YYYY-MM-DD HH:MM:SS,在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,为 '1970-01-01 00:00:01'UTC~'2038-01-19 03:14:07'UTC。在插入数据时,要保证在合法的取值范围内。

提示:协调世界时(英:Coordinated Universal Time,法:Temps Universel Coordonné)又称为世界统一时间、世界标准时间、国际协调时间。英文(CUT)和法文(TUC)的缩写不同,作为妥协,简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外,还有一个最大的区别是:

  • DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;

  • 而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。

提示:如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值,结果值的时间部分被设置为 '00:00:00',因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值,结果值的时间部分被删除,因此DATE 值未包含时间信息。

时间与日期函数

  • 系统时间函数 now(), current_timestamp, sysdate()

这三个函数都能返回系统时间,看看它们的区别,同时访问这三个函数得到的结果一样,但是让其等待几秒之后,结果就不同了。

mysql中生日日期怎么表示

上图发现sysdate()返回的时间与now(), current_timestamp不同,慢了2秒,原因如下:

current_timestamp 是now的同义词,两者实际上是一样的;

sysdate() 函数返回的是执行到当前函数时的时间,而now()返回的是执行sql语句时的时间;

  • 时间加减函数

① date_add 与 date_sub

date_add(date, interval expr unit) 与 date_sub(date, interval expr unit) 分别表示增加与减少。

select
date_add('2017-12-18 22:51:00', interval 1 second),
date_add('2017-12-18 22:51:00', interval 1 minute),
date_add('2017-12-18 22:51:00', interval 1 hour),
date_add('2017-12-18 22:51:00', interval 1 day),
date_add('2017-12-18 22:51:00', interval 1 month),
date_add('2017-12-18 22:51:00', interval 1 year);

遇到闰月时,date_add怎么处理呢,MySQL如果是闰月返回的日期为2月29,如果不是闰月返回的是2月28。

select date_add('2016-02-29 22:51:00', interval -1 year),
date_add('2016-02-29 22:51:00', interval 4 year);
2015-02-28 22:51:002020-02-29 22:51:00

② datediff 与 timediff

  • datediff(date1,date2):两个日期相减 date1 - date2,返回天数。select datediff('2016-02-29 22:51:00','2016-03-29 22:51:00');

  • timediff(time1,time2):两个日期相减 time1 - time2,返回 time 差值(格式为:838:59:59)。select timediff('2016-02-29 22:51:00','2016-01-29 22:51:00');

注意:timediff(time1,time2) 函数的两个参数类型必须相同。

③ timestamp 增,减,转换函数

  • timestamp(date)  -- date to timestamp

  • timestamp(date, time) -- date + time

  • timestampadd(unit,interval,datetime_expr)   -- 类似date_add,对时间按单位进行增加,减少

select timestampadd(second, 60, '2017-12-18 00:00:00');
select timestampadd(minute, 60, '2017-12-18 00:00:00');
select timestampadd(hour, 60, '2017-12-18 00:00:00');

  • timestampdiff(unit,datetime_expr1,datetime_expr2) --计算两个时间的差,可以计算秒,分,时,天,月,年,周

select timestampdiff(second, '2017-12-18 00:00:00','2017-12-18 12:00:00');
select timestampdiff(hour, '2017-11-18 00:00:00','2017-12-18 12:00:00');
select timestampdiff(day, '2017-10-17 00:00:00','2017-11-18 23:00:00');

④ date_format 函数

date_format 函数主要用途是格式化日期

select date_format(now(),'%Y%m%d'),
date_format(now(),'%Y-%m-%d'),
date_format(now(),'%Y/%m/%d'),
date_format(now(),'%m/%d/%Y');

注意:查询数据时如果对某个日期字段使用这个函数,则优化器不会使用字段的索引,也不能通过索引来查询数据,因此查询效率会很低,如下列查询语句,如果字段birth_date有索引,则该索引因为使用了date_format函数失效:

select *
from employees
where date_format(birth_date,'%Y-%m-%d') = '1961-08-03';

日期的经典编程问题

根据某个用户的出生日期与当前日期,计算他最近的生日(包括已经过去的和将来的)

-- 上一年,当年,下一年生日与当前时间差最小的年就是用户最近的生日
select emp_no,first_name,last_name,birth_date,today,if(last_diff<=cur_diff and last_diff<=next_diff, last,if(cur_diff
from
(
-- 计算当前时间与上一年,当年,下一年生日的时间差
select emp_no,first_name,last_name,birth_date,datediff(today,last) as last_diff,abs(datediff(today,cur)) as cur_diff,abs(datediff(today,next)) as next_diff,
last,cur,next,today
from
(
-- 用户最近的生日可能分布在前一年,当年,下一年。根据年度差year_diff计算上一年,当年,下一年用户的生日
select emp_no,first_name,last_name,birth_date,
DATE_ADD(birth_date,INTERVAL year_diff year) as cur,
DATE_ADD(birth_date,INTERVAL year_diff+1 year) as next,
DATE_ADD(birth_date,INTERVAL year_diff-1 year) as last,
today
from
(
-- 获取当前事前与出生日期的年度差 year_diff
select emp_no,first_name,last_name,birth_date,(year(NOW())-year(birth_date)) as year_diff, NOW() as today
from employees
) a
) b
) c

关于“mysql中生日日期怎么表示”这篇文章的内容就介绍到这里,感谢各位的阅读!相信大家对“mysql中生日日期怎么表示”知识都有一定的了解,大家如果还想学习更多知识,欢迎关注创新互联行业资讯频道。


网页名称:mysql中生日日期怎么表示
文章路径:http://cdxtjz.cn/article/pessej.html

其他资讯