What type is used for birthdays in mysql?

Available types of birthdays in mysql: 1. DATE type, the date format that can be represented is "YYYY-MM-DD", which requires 3 bytes for storage; 2. DATETIME type, the date format that can be represented It is "YYYY-MM-DD HH:MM:SS", which requires 8 bytes when storing; 3. TIMESTAMP type, the date format that can be expressed is "YYYY-MM-DD HH:MM:SS", when storing Requires 4 bytes.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Date and time types suitable for birthdays in MySQL

Type name	Date Format	Date Range	Storage Requirements
DATE	YYYY-MM-DD	1000- 01-01 ~ 9999-12-3	3 bytes
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 bytes
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 Bytes

DATE type

The DATE type is used when only a date value is required, without the time part, and requires 3 bytes for storage. The date format is 'YYYY-MM-DD', where YYYY represents the year, MM represents the month, and DD represents the day.

When assigning values ​​to DATE type fields, you can use string type or numeric type data to insert, as long as it conforms to the date format of DATE. As shown below:

• The date expressed in the format of 'YYYY-MM-DD' or 'YYYYMMDD' characters, the value range is '1000-01-01'～'9999- 12-3'. For example, enter '2015-12-31' or '20151231', and the date inserted into the database will be 2015-12-31.

• Represent the date in 'YY-MM-DD' or 'YYMMDD' string format, where YY represents the two-digit year value. MySQL interprets the rules for two-digit year values: year values ​​in the range of '00~69' are converted to '2000~2069', and year values ​​in the range of '70~99' are converted to '1970~1999'. For example, if you enter '15-12-31', the date inserted into the database is 2015-12-31; if you enter '991231', the date inserted into the database is 1999-12-31.

• The date expressed in YYMMDD numeric format is similar to the previous one. Year values ​​in the range of 00~69 are converted to 2000~2069, and year values ​​in the range of 80~99 are converted to 1980~1999. For example, if you enter 151231, the date inserted into the database is 2015-12-31, and if you enter 991231, the date inserted into the database is 1999-12-31.

• Use CURRENT_DATE or NOW() to insert the current system date.

Tip: MySQL allows "relaxed" syntax: any punctuation mark can be used as a separator between date parts. For example, '98-11-31', '98.11.31', '98/11/31' and '98@11@31' are equivalent and these values ​​will be inserted into the database correctly.

DATETIME type

The DATETIME type is used for values ​​that need to contain both date and time information and requires 8 bytes for storage. The date format is 'YYYY-MM-DD HH:MM:SS', where YYYY represents the year, MM represents the month, DD represents the day, HH represents the hour, MM represents the minute, and SS represents the second.

When assigning values ​​to fields of DATETIME type, you can use string type or numeric type data to insert, as long as it conforms to the date format of DATETIME, as shown below:

• The date expressed in the string format of 'YYYY-MM-DD HH:MM:SS' or 'YYYYMMDDHHMMSS', the value range is '1000-01-01 00:00:00'～'9999-12-3 23 :59:59'. For example, if you enter '2014-12-31 05:05:05' or '20141231050505', the DATETIME value inserted into the database will be 2014-12-31 05:05:05.

• A date represented in 'YY-MM-DD HH:MM:SS' or 'YYMMDDHHMMSS' string format, where YY represents a two-digit year value. Same as before, the year value in the range of '00~79' is converted to '2000~2079', and the year value in the range of '80~99' is converted to '1980~1999'. For example, if you enter '14-12-31 05:05:05', the DATETIME inserted into the database is 2014-12-31 05:05:05; if you enter 141231050505, the DATETIME inserted into the database is 2014-12-31 05:05:05 .

• Date and time expressed in YYYYMMDDHHMMSS or YYMMDDHHMMSS numeric format. For example, if you enter 20141231050505, the DATETIME inserted into the database is 2014-12-31 05:05:05; if you enter 140505050505, the DATETIME inserted into the database is 2014-12-31 05:05:05.

Tip: MySQL allows "relaxed" syntax: any punctuation mark can be used as a separator between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11 30 35', '98/12/31 11*30*45' and '98@12@31 11^30^45' are Equivalently, these values ​​can be correctly inserted into the database.

TIMESTAMP type

The display format of TIMESTAMP is the same as DATETIME, the display width is fixed at 19 characters, and the date format is YYYY-MM-DD HH:MM :SS, requires 4 bytes for storage. However, the value range of the TIMESTAMP column is smaller than the value range of DATETIME, which is '1970-01-01 00:00:01'UTC～'2038-01-19 03:14:07'UTC. When inserting data, make sure it is within the legal value range.

Tip: Coordinated Universal Time (English: Coordinated Universal Time, French: Temps Universel Coordonné) is also known as Universal Unified Time, World Standard Time, and International Coordinated Time. The English (CUT) and French (TUC) abbreviations are different, and as a compromise, the abbreviation is UTC.

TIMESTAMP and DATETIME, in addition to different storage bytes and supported ranges, the biggest difference is:

• DATETIME When storing date data, It is stored according to the actual input format, that is, whatever is entered is stored, regardless of the time zone;

• The TIMESTAMP value is stored in UTC (Universal Standard Time) format, and is stored correctly Convert to the current time zone, and then convert back to the current time zone when retrieving. That is, when querying, the displayed time value is different depending on the current time zone.

Tip: If you assign a DATE value to a DATETIME or TIMESTAMP object, the time portion of the resulting value is set to '00:00:00', so the DATE value does not contain time information. If a DATE object is assigned a DATETIME or TIMESTAMP value, the time portion of the resulting value is removed, so the DATE value contains no time information.

Time and date functions

• ##System time function now(), current_timestamp, sysdate ()

These three functions can all return the system time. See their differences. Accessing these three functions at the same time will get the same result, but let them wait for a few seconds. After that, the results were different.

The above figure shows that the time returned by sysdate() is different from now(), current_timestamp, which is 2 seconds slower. The reasons are as follows:

current_timestamp is now Synonyms, the two are actually the same;

The sysdate() function returns the time when the current function is executed, while now() returns the time when the sql statement is executed;

• Time addition and subtraction functions

① date_add and date_sub

date_add(date, interval expr unit) and date_sub( date, interval expr unit) represent increase and decrease respectively.

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);

How to handle date_add when encountering a leap month? MySQL returns the date February 29 if it is a leap month, and February 28 if it is not a leap month.

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 and timediff

• datediff(date1,date2): Subtract two dates date1 - date2, return the number of days. select datediff('2016-02-29 22:51:00','2016-03-29 22:51:00');

##timediff(time1,time2)：two Subtract time1 - time2 from each date and return the time difference (format: 838:59:59). select timediff('2016-02-29 22:51:00','2016-01-29 22:51:00');
Note: timediff(time1,time2) The two parameters of the function must be of the same type.

③ timestamp increase, decrease, conversion function

• timestamp(date)

  -- date to timestamp

• timestamp(date, time)

  -- date time

• timestampadd(unit,interval,datetime_expr)

  -- Similar to date_add, Increase the time by unit and decrease it

  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)

  --Calculate the difference between two times, Can calculate seconds, minutes, hours, days, months, years, weeks

  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 function

date_format function is mainly used to format dates

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');

Note: If you use this function for a date field when querying data, the optimizer will not use the index of the field, nor can you query the data through the index, so the query efficiency will be very low. For example, in the following query statement, if the field birth_date If there is an index, the index will be invalid because of the use of the date_format function:

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

Classic programming issues with datesAccording to the date of birth of a user With the current date, calculate his latest birthday (including past and future ones)

-- 上一年，当年，下一年生日与当前时间差最小的年就是用户最近的生日
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

[Related recommendations:

mysql video tutorial

]

The above is the detailed content of What type is used for birthdays in mysql?. For more information, please follow other related articles on the PHP Chinese website!