MySQL: MySQL date data type and MySQL time type _ MySQL

1. MySQL date type: date 26684; type, storage space occupied, date range comparison date type storage space date 26684; type 1. MySQL date type: comparison of date format, storage space occupied, and date range

Date type bucket date format date range ------------ ------------------------------------------------------------------- datetime 8 bytes YYYY-MM-DD HH: MM: SS 1000-01-01 00:00:00 ~ 9999-12-31 23: 59: 59 timestamp 4 bytes YYYY-MM-DD HH: MM: SS 00:00:01 ~ 2038 date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31year 1 bytes YYYY 1901 ~ 2155

When creating a table in MySQL, you can easily select a suitable data type based on the preceding table. However, it may be difficult to select datetime or timestamp. These two date and time types have their own advantages: datetime has a large date range; timestamp occupies a small storage space, only half of datetime.

In addition, columns of the timestamp type have another feature: by default, the timestamp column is automatically filled/updated with the current time (CURRENT_TIMESTAMP) during insert and update data. "Automatic" means that MySQL will handle it for you without worrying about it. The table creation code is: create table t8 ('id1' timestamp not null default CURRENT_TIMESTAMP, 'id2' datetime default NULL );

In general, I tend to use the datetime date type.

Comparison between the two:
1. timestamp easily supports a smaller range than timedate. And prone to exceeding
2. timestamp is affected by timezone and the SQL MODE of MYSQL version and server.

II. MySQL time type: time format, storage space occupied, and time range

Time format time range ------------ --------- --------------------------- --------------------------------------------- time 3 bytes HH: MM: SS-838: 59: 59 ~ 838: 59: 59 The time range has such a large range. in particular, the time value can be negative, which is a bit strange. Later, I read the MySQL manual to know that this was designed to meet the requirements of two Date-Time subtraction. For example, select timediff ('2014: 01: 31 23:59:59 ', '2014: 01: 01 00:00:00'); -- 2000: 59: 59 select timediff ('2014: 01: 01 00:00:00 ', '2014: 01: 31 23:59:59'); ---2000: 59: 59 select timediff ('23: 59: 59', '12: 00: 00'); -- 11:59:59

Note: The two timediff parameters must be of the datetime, timestamp, and time types, and must be the same. That is, compare datetime/timestamp with datetime/timestamp; compare time with time.

Related knowledge reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Although MySQL has a wide range of date and time types, it is a pity that currently () these date and time types can only be in seconds, not milliseconds, microseconds. No function is generated in milliseconds.
III. Summary of MySQL date data types and MySQL Time Types Note: the summary of MySQL date data types and MySQL time types is applicable to MySQL 5.X and later versions.
1. MySQL obtains the current date and time function 1.1 and obtains the current date + time (date + time) function: now ()

mysql> select now();+-----------------------------+| now() |+-----------------------------+| 2014-10-29 10:52:57 |+-----------------------------+

In addition to the now () function, MySQL also has the following functions:

current_timestamp()current_timestamplocaltime()localtimelocaltimestamp -- (v4.0.6)localtimestamp() -- (v4.0.6)

These datetime functions are equivalent to now (). Considering that the now () function is short and easy to remember, we recommend that you always use now () to replace the functions listed above.
1.2 obtain the current date + time (date + time) function: the sysdate () date and time function is similar to the now () function. The difference is that: now () the execution start value is obtained, and sysdate () dynamically obtains the value during function execution. You can see the following example:

mysql> select now(), sleep(3), now();+-----------------------------+--------------+-----------------------------+| now() | sleep(3) | now() |+-----------------------------+--------------+-----------------------------+| 2014-10-29 10:55:11 | 0 | 2014-10-29 10:55:11 |+-----------------------------+--------------+-----------------------------+mysql> select sysdate(), sleep(3), sysdate();+-----------------------------+--------------+-----------------------------+| sysdate() | sleep(3) |sysdate() |+-----------------------------+--------------+-----------------------------+| 2014-10-29 10:55:11 | 0 | 2014-10-29 10:55:14 |+-----------------------------+--------------+-----------------------------+

We can see that although sleep lasts for 3 seconds, the time values of the now () function are the same. the time values obtained by The sysdate () function differ by 3 seconds. In MySQL Manual, sysdate () is described as follows: Return the time at which the function executes.

Sysdate () date and time functions are rarely used.
2. obtain the current date function: curdate ()

mysql> select curdate();+----------------+| curdate() |+----------------+| 2014-10-29 |+----------------+

The following two date functions are equivalent to curdate ():

current_date()current_date

3. obtain the current time function: curtime ()

mysql> select curtime();+-----------+| curtime() |+-----------+| 10:59:58 |+-----------+

The following two time functions are equivalent to curtime ():

current_time()current_time

4. obtain the current UTC date and time functions: utc_date (), utc_time (), utc_timestamp (),

mysql> select utc_timestamp(), utc_date(), utc_time(), now() ;+-------------------------------+-----------------+-----------------+-------------------------------+| utc_timestamp() | utc_date() | utc_time() | now() |+-------------------------------+-----------------+-----------------+-------------------------------+| 2014-10-29 03:00:55 | 2014-10-29 | 03:00:55 | 2014-10-29 11:00:55 |+-------------------------------+-----------------+-----------------+-------------------------------+

Because our country is located in the East eight time zone, the local time = UTC time + 8 hours. UTC time is very useful when your business involves multiple countries and regions.
5. MySQL date and time Extract (select) function. 5.1. select each part of the date and time: date, time, year, quarter, month, day, hour, minute, second, microsecond

Set @ dt = '2017-09-10 07:15:30. 123456 '; select date (@ dt); -- 2008-09-10select time (@ dt); -- 07:15:30. 123456 select year (@ dt); -- 2008 select quarter (@ dt); -- 3 select month (@ dt); -- 9 select week (@ dt ); -- 36 select day (@ dt); -- 10 select hour (@ dt); -- 7 select minute (@ dt); -- 15 select second (@ dt ); -- 30 select microsecond (@ dt); -- 1234565.2. the MySQL Extract () function can implement similar functions:

set @dt = '2008-09-10 07:15:30.123456';select extract(year from @dt); -- 2008select extract(quarter from @dt); -- 3select extract(month from @dt); -- 9select extract(week from @dt); -- 36select extract(day from @dt); -- 10select extract(hour from @dt); -- 7select extract(minute from @dt); -- 15select extract(second from @dt); -- 30select extract(microsecond from @dt); -- 123456select extract(year_month from @dt); -- 200809select extract(day_hour from @dt); -- 1007select extract(day_minute from @dt); -- 100715select extract(day_second from @dt); -- 10071530select extract(day_microsecond from @dt); -- 10071530123456select extract(hour_minute from @dt); -- 715select extract(hour_second from @dt); -- 71530select extract(hour_microsecond from @dt); -- 71530123456select extract(minute_second from @dt); -- 1530select extract(minute_microsecond from @dt); -- 1530123456select extract(second_microsecond from @dt); -- 30123456

In addition to date () and time () functions, the MySQL Extract () function must be fully functional. You can also select 'Day _ microsecond. Note that we will not only select day and microsecond, but choose from day of the date to microsecond. Powerful enough!

The only bad thing about the MySQL Extract () function is that you need to press the keyboard several more times.
6. MySQL dayof... function: dayofweek (), dayofmonth (), and dayofyear () return the date parameters in the week, month, or year respectively.

set @dt = '2008-08-08';select dayofweek(@dt); -- 6select dayofmonth(@dt); -- 8select dayofyear(@dt); -- 221

The date '2017-08-08 'is the first day of the week (1 = Sunday, 2 = Monday,..., 7 = Saturday); the second day of the month; the second day of the year.
7. MySQL week... function: week (), weekofyear (), dayofweek (), weekday (), yearweek ()

set @dt = '2008-08-08';select week(@dt); -- 31select week(@dt,3); -- 32select weekofyear(@dt); -- 32select dayofweek(@dt); -- 6select weekday(@dt); -- 4select yearweek(@dt); -- 200831

The MySQL week () function has two parameters. for details, refer to the manual. Similar to weekofyear () and week (), weekofyear is the day of the year in which the calculation "one day" is located. Weekofyear (@ dt) is equivalent to week (@ dt, 3 ).

The MySQL weekday () function is similar to dayofweek () and returns the position of "one day" in a week. The difference lies in the reference standard, weekday :( 0 = Monday, 1 = Tuesday ,..., 6 = Sunday); dayofweek :( 1 = Sunday, 2 = Monday ,..., 7 = Saturday)

MySQL yearweek () function, returns year (2008) + week position (31 ).
8. MySQL returns the week and month name functions: dayname (), monthname ()

set @dt = '2008-08-08';select dayname(@dt); -- Fridayselect monthname(@dt); -- August

Think about how to return the Chinese name?
9. MySQL last_day () function: returns the last day of the month.

Select last_day ('2017-02-01 '); -- 2008-02-29select last_day ('2017-08-08'); -- the 2008-08-31MySQL last_day () function is very useful, for example, how many days do I want to get in the current month, it can be calculated as follows: mysql> select now (), day (last_day (now () as days; + ------------------- + ------ + | now () | days | + ------------------- + ------ + | 11:06:55 | 31 | + ----------------------- + ------ +

IV. MySQL date and time calculation function 1. MySQL adds a time interval for the date: date_add ()

Set @ dt = now (); select date_add (@ dt, interval 1 day); -- add 1 dayselect date_add (@ dt, interval 1 hour ); -- add 1 hourselect 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 y Ear); select date_add (@ dt, interval-1 day); -- sub 1 dayMySQL adddate (), addtime () function, which can be replaced by date_add. The following is an example of addtime () implemented by date_add (): mysql> set @ dt = '2017-08-09 12:12:33 '; mysql> select date_add (@ dt, interval '01: 15: 30 'hour_second); + ---------------------------------------------- + | date_add (@ dt, interval '01: 15: 30' hour_second) | + minute + | 13:28:03 | + ---------------------------------------------------- + mysql> select date_add (@ dt, interval '1 01:15:30 'day_second); + minute + | date_add (@ dt, interval '1 01:15:30 'day_second) | + ------------------------------------------------- + | 13:28:03 | + ----------------------------------------- +

The date_add () function adds "1 hour 15 minutes 30 seconds" and "1 day 1 hour 15 minutes 30 seconds" to @ dt respectively ". Suggestion: Always use the date_add () date and time function to replace adddate () and addtime ().
2. MySQL is a date minus a time interval: date_sub ()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);+----------------------------------------------------------------+| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) |+----------------------------------------------------------------+| 1997-12-30 22:58:59 |+----------------------------------------------------------------+

The usage of MySQL date_sub () DATETIME function is the same as that of date_add. In addition, there are two subdate () and subtime () functions in MySQL. we recommend that you use date_sub () instead.
3. mySQL alternative date functions: period_add (P, N), period_diff (P1, P2) function parameter "P" format is "YYYYMM" or "YYMM ", the second parameter "N" indicates adding or subtracting N month ).

MySQL period_add (P, N): date plus/minus N months. Mysql> select period_add (20080808), period_add (20080808,-2) + ------------------------ + --------------------------- + | period_add (, 2) | period_add (,-2) | + -------------------- + bytes + | 200810 | 20080806 | + ---------------------------- + ----------------------- + MySQL period_diff (P1, P2): date P1-P2, returns N months. Mysql> select period_diff (200808,200 801); + --------------------------- + | period_diff (200808,200 801) | + period + | 7 | + period + in MySQL, the two date functions, it is rarely used in general cases.

4. MySQL date and time subtraction functions: datediff (date1, date2), timediff (time1, time2)

MySQL datediff (date1, date2): two dates subtract date1-date2 and return the number of days. Select datediff ('2017-08-08 ', '2017-08-01'); -- 7 select datediff ('2017-08-01 ', '2017-08-08 '); ---7 MySQL timediff (time1, time2): two dates minus time1-time2, return the time difference. Select timediff ('2017-08-08 08:08:08 ', '2017-08-08 00:00:00'); -- 08: 08: 08 select timediff ('08: 08: 08 ', '00: 00: 00'); -- 08:08:08 note: the two parameter types of the timediff (time1, time2) function must be the same.

V. MySQL date conversion function and time conversion function 1. MySQL (time, second) conversion function: time_to_sec (time), sec_to_time (seconds)

select time_to_sec('01:00:05'); -- 3605select sec_to_time(3605); -- '01:00:05'

2. MySQL (date, number of days) conversion functions: to_days (date), from_days (days)

select to_days('0000-00-00'); -- 0select to_days('2008-08-08'); -- 733627select from_days(0); -- '0000-00-00'select from_days(733627); -- '2008-08-08'

3. MySQL Str to Date (string converted to Date) function: str_to_date (str, format)

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

We can see that the str_to_date (str, format) conversion function can convert disordered strings into date formats. In addition, it can be converted to time. For "format", see the MySQL manual.
4. MySQL Date/Time to Str (convert date/time to string) functions: date_format (Date, format), time_format (Time, format)

Mysql> select date_format ('2017-08-08 22:23:00 ',' % W % M % Y'); + ------------------------------------------------ + | date_format ('2017-08-08 22:23:00 ', '% W % M % Y') | + -------------------------------------------------- + | Friday August 2008 | + week + mysql> select date_format ('2017-08-08 22:23:01 ', '% Y % m % d % H % I % S'); + ---------------------------------------------------- + | Date_format ('2017-08-08 22:23:01 ',' % Y % m % d % H % I % S ') | + records + | 20080808222301 | + ---------------------------------------------------- + mysql> select time_format ('22: 23: 01', '% H. % I. % S'); + ------------------------------------- + | time_format ('22: 23: 01', '% H. % I. % S') | + ------------------------------------- + | 22.23.01 | + --------------------------------------- + M YSQL date and time conversion functions: date_format (date, format), time_format (time, format) can convert a date/time to a variety of string formats. It is an inverse conversion of the str_to_date (str, format) function.

5. MySQL returns the country/region time format function: get_format ()

MySQL get_format () syntax: get_format (date | time | datetime, 'eur' | 'USA' | 'jis '| 'ISO' | 'internal' MySQL get_format () example: select get_format (date, 'USA'); -- '% m. % d. % Y' select get_format (date, 'jis '); --' % Y-% m-% d' select get_format (date, 'ISO '); -- '% Y-% m-% d' select get_format (date, 'eur'); --' % d. % m. % Y' select get_format (date, 'internal'); -- '% Y % m % d' select get_format (datetime, 'USA '); -- '% Y-% m-% d % H. % I. % s's Elect get_format (datetime, 'jis '); --' % Y-% m-% d % H: % I: % s' select get_format (datetime, 'ISO '); -- '% Y-% m-% d % H: % I: % s' select get_format (datetime, 'eur '); -- '% Y-% m-% d % H. % I. % s' select get_format (datetime, 'internal'); -- '% Y % m % d % H % I % s' select get_format (time, 'USA '); -- '% h: % I: % s % p' select get_format (time, 'jis'); -- '% H: % I: % s' select get_format (time, 'ISO '); --' % H: % I: % s' select get_format (time, 'eur '); -- '% H. % I. % s' select get_format (time, 'internal'); -- the '% H % I % s' MySQL get_format () function has fewer opportunities in actual use.

6. MySQL patchwork date and time functions: 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'

VI. MySQL Timestamp function 1. MySQL obtains the current Timestamp function: current_timestamp, current_timestamp ()

mysql> select current_timestamp, current_timestamp();+---------------------+---------------------+| current_timestamp | current_timestamp() |+---------------------+---------------------+| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |+---------------------+---------------------+

2. MySQL (Unix timestamp, date) conversion function:

Unix_timestamp (), unix_timestamp (date), from_unixtime (unix_timestamp), from_unixtime (unix_timestamp, format) The following is an example: select unix_timestamp (); -- 1218290027 select unix_timestamp ('2017-08-08 '); -- 2008 select unix_timestamp ('2017-08-08 12:30:00'); -- 1218124800 select from_unixtime (2008 ); -- '2014-08-09 21:53:47 'select from_unixtime (2008); -- '2014-08-08 00:00:00' select from_unixtime (1218124800 ); -- '2014-08-08 12:30:00 'select from_unixtime (2008,' % Y % D % M % h: % I: % s % X '); -- '2017 8th August 12:30:00 123'

3. MySQL timestamp conversion, addition, and subtraction functions:

timestamp(date) -- date to timestamptimestamp(dt,time) -- dt + timetimestampadd(unit,interval,datetime_expr) --timestampdiff(unit,datetime_expr1,datetime_expr2) --

See the example section:

Select timestamp ('2017-08-08 '); -- 2008-08-08 00: 00: 00 select timestamp ('2017-08-08 08:00:00', '01: 01: 01 '); -- 2008-08-08 09: 01: 01 select timestamp ('2017-08-08 08:00:00 ', '10 01:01:01'); -- 2008-08-18 09: 01: 01 select timestampadd (day, 1, '2017-08-08 08:00:00 '); -- 2008-08-09 08: 00: 00 select date_add ('2017-08-08 08:00:00', interval 1 day); -- 2008-08-09 08: 00: 00 MySQL timestampadd () function is similar to date_add (). Select timestampdiff (year, '2017-05-01 ', '2017-01-01'); ---1 select timestampdiff (day, '2017-05-01 ', '2017-01-01 '); -- 485 select timestampdiff (hour, '2017-08-08 12:00:00 ', '2017-08-08 00:00:00 '); ---12 select datediff ('2017-08-08 12:00:00 ', '2017-08-01 00:00:00'); -- 7 MySQL timestampdiff () function is better than datediff () with more features, datediff () can only calculate the number of days for the difference between two dates.

VII. MySQL timezone conversion function

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

You can also use date_add, date_sub, and timestampadd to convert time zones.

select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00

Note: data source network