MySQL Date data type and time type usage Summary

Turn from:

http://blog.chinaunix.net/space.php?uid=11327712&do=blog&id=32416

MySQL Date Type: Date format, occupied storage space, date range comparison.

Date type storage space 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 1970-01-01 00:00:01 ~ 2038 date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31 year 1 bytes YYYY 1901 ~ 2155
When creating a table in MySQL, you can easily select the appropriate data type by comparing the table above. However, whether to choose datetime or timestamp may be a bit difficult. These two datetime types each have advantages: datetime has a large date range; timestamp takes up less storage space, only half of datetime.

In addition, a timestamp column has another feature: by default, when inserting or updating data, the timestamp column is automatically filled / updated with the current time (CURRENT_TIMESTAMP). "Automatic" means that if you leave it alone, MySQL will handle it for you.

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

MySQL time type: time format, storage space occupied, time range.
Time type storage space time format time range ------------ --------- --------------------- ----------------------------------------- time 3 bytes HH: MM: SS- 838: 59: 59 ~ 838: 59: 59
The time range has such a large range, especially the negative value of time, which is a bit strange. Later, after reading the MySQL manual, I realized that this was designed to meet the subtraction of two datetimes.

select timediff ('2000: 01: 31 23:59:59', '2000: 01: 01 00:00:00');-743: 59: 59 select timediff ('2000: 01: 01 00:00: 00 ',' 2000: 01: 31 23:59:59 ');--743: 59: 59 select timediff ('23: 59: 59', '12: 00: 00 ');-11:59 : 59
Note that the two parameters of timediff can only be of type datetime / timestamp, time, and the two parameter types must be the same. That is: datetime / timestamp is compared with datetime / timestamp; time is compared with time.

Although the date and time types in MySQL are relatively rich, unfortunately, currently (2008-08-08) these date and time types can only be supported to the level of seconds, not milliseconds or microseconds. There is also no function that produces milliseconds.

"MySQL: Summary of MySQL Date Data Type and MySQL Time Type Usage" is applicable to MySQL 5.X and above.

First, MySQL get the current date and time function
1.1 Get the current date + time (date + time) function: now ()

mysql> select now (); + --------------------- + | now () | + -------------- ------- + | 2008-08-08 22:20:46 | + --------------------- +
In addition to the current () function to get the current date and time, there are the following functions in MySQL:

current_timestamp (), current_timestamp, localtime (), localtime, localtimestamp-(v4.0.6), localtimestamp ()-(v4.0.6)
These datetime functions are equivalent to now (). Given that the now () function is short and easy to remember, it is recommended to always use now () instead of the functions listed above.

1.2 Get the current date + time (date + time) function: sysdate ()

The sysdate () datetime function is similar to now (), except that now () gets the value at the beginning of execution, and sysdate () gets the value dynamically when the function executes. See the following example to understand:

mysql> select now (), sleep (3), now (); + --------------------- + ---------- + --------------------- + | now () | sleep (3) | now () | + ------------- -------- + ---------- + --------------------- + | 2008-08-08 22: 28:21 | 0 | 2008-08-08 22:28:21 | + --------------------- + ---------- + --------------------- +
mysql> select sysdate (), sleep (3), sysdate (); + --------------------- + ---------- + --------------------- + | sysdate () | sleep (3) | sysdate () | + ------------- -------- + ---------- + --------------------- + | 2008-08-08 22: 28:41 | 0 | 2008-08-08 22:28:44 | + --------------------- + ---------- + --------------------- +
As you can see, although the sleep time is 3 seconds, the time value of the now () function is the same twice; the time value obtained by the sysdate () function is 3 seconds. The MySQL Manual describes sysdate () like this: Return the time at which the function executes.

The sysdate () datetime function is rarely used in general.

2. Get the current date (date) function: curdate ()

mysql> select curdate (); + ------------ + | curdate () | + ------------ + | 2008-08-08 | +- ---------- +
Among them, the following two date functions are equivalent to curdate ():

current_date (), current_date
3. Get the current time (time) function: curtime ()

mysql> select curtime (); + ----------- + | curtime () | + ----------- + | 22:41:30 | + ---- ------- +
Among them, the following two time functions are equivalent to curtime ():

current_time (), current_time
4. Get 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 () | + --------------------- + ------------ + -------- ---- + --------------------- + | 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 | + --------------------- + ------------ +- ---------- + --------------------- +
Because our country is in the East Eighth Time Zone, local time = UTC time + 8 hours. UTC time is useful when your business involves multiple countries.

Second, the MySQL date and time Extract function.
1. Select each part of the date and time: date, time, year, quarter, month, day, hour, minute, second, microsecond

set @dt = '2008-09-10 07: 15: 30.123456'; select date (@dt);-2008-09-10 select 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);-123456
2. MySQL Extract () function, which can achieve similar functions above:

set @dt = '2008-09-10 07: 15: 30.123456'; select extract (year from @dt);-2008 select extract (quarter from @dt);-3 select extract (month from @dt); -9 select extract (week from @dt);-36 select extract (day from @dt);-10 select extract (hour from @dt);-7 select extract (minute from @dt);- 15 select extract (second from @dt);-30 select extract (microsecond from @dt);-123456
select extract (year_month from @dt);-200809 select extract (day_hour from @dt);-1007 select extract (day_minute from @dt);-100715 select extract (day_second from @dt);-10071530 select extract (day_microsecond from @dt);-10071530123456 select extract (hour_minute from @dt);-715 select extract (hour_second from @dt);-71530 select extract (hour_microsecond from @dt);-71530123456 select extract (minute_second from @dt);-1530 select extract (minute_microsecond from @dt);-1530123456 select extract (second_microsecond from @dt);-30123456
The MySQL Extract () function has no functions except date (), time ().all. It also has functions like selecting ‘day_microsecond’. Note that instead of just selecting day and microsecond, we are selecting from the day part of the date all the way to the microsecond part. It's powerful enough!

The only bad thing about the MySQL Extract () function is that you need to type the keyboard a few more times.

3. MySQL dayof ... Functions: dayofweek (), dayofmonth (), dayofyear ()

Returns the date parameters, respectively, during the week, month, and year.

set @dt = ‘2008-08-08’; select dayofweek (@dt);-6 select dayofmonth (@dt);-8 select dayofyear (@dt);-221
The date ‘2008-08-08’ is the 6th day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday); the 8th day of January; the 221th day of the year.

4. MySQL week ... Functions: week (), weekofyear (), dayofweek (), weekday (), yearweek ()

set @dt = '2008-08-08'; select week (@dt);-31 select week (@ dt, 3);-32 select weekofyear (@dt);-32 select dayofweek (@dt) ;-6 select weekday (@dt);-4 select yearweek (@dt);-200831
The MySQL week () function can take two parameters, see the manual for details. weekofyear () is the same as week (), counting "day" as the week of the year. weekofyear (@dt) is equivalent to week (@ dt, 3).

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

The MySQL yearweek () function returns year (2008) + week position (31).

5. MySQL returns week and month name functions: dayname (), monthname ()

set @dt = ‘2008-08-08’; select dayname (@dt);-Friday select monthname (@dt);-August
Thinking, how to return the Chinese name?

6. MySQL last_day () function: Returns the last day of the month.

select last_day (‘2008-02-01’);-2008-02-29 select last_day (‘2008-08-08‘);-2008-08-31
The MySQL last_day () function is very useful. For example, I want to get the number of days in the current month. I can calculate it like this:

mysql> select now (), day (last_day (now ())) as days; + --------------------- + ------ + | now () | days | + --------------------- + ------ + | 2008-08-09 11:45:45 | 31 | + --------------------- + ------ +
Third, MySQL date time calculation function
1. MySQL adds a time interval to the date: 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);
select date_add (@dt, interval -1 day);-sub 1 day
MySQL adddate (), addtime () functions can be replaced with date_add (). Here is an example of date_add () implementing the addtime () function:

mysql> set @dt = '2008-08-09 12:12:33'; mysql> mysql> select date_add (@dt, interval '01: 15: 30 'hour_second); + --------- --------------------------------------- + | date_add (@dt, interval '01: 15:30 'hour_second) | + ------------------------------------------ ------ + | 2008-08-09 13:28:03 | + ------------------------------ ------------------ +
mysql> select date_add (@dt, interval '1 01:15:30' day_second); + ----------------------------- -------------------- + | date_add (@dt, interval '1 01:15:30' day_second) | + ---------- --------------------------------------- + | 2008-08-10 13:28: 03 | + ----------------------------------------------- -+
The date_add () function adds "1 hour 15 minutes 30 seconds" and "1 day 1 hour 15 minutes 30 seconds" for @dt, respectively. Recommendation: Always use date_add () datetime functions instead of adddate (), addtime ().

2. MySQL subtracts an interval from date: 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 | + ----------------------------------------------- ----------------- +
MySQL date_sub () datetime functions are used in the same way as date_add () and will not be repeated here. In addition, MySQL also has two functions subdate (), subtime (). It is recommended to use date_sub () instead.

3. MySQL alternative date functions: period_add (P, N), period_diff (P1, P2)

The format of the function parameter "P" is "YYYYMM" or "YYMM". The second parameter "N" means to add or subtract N month.

MySQL period_add (P, N): date plus / minus N months.

mysql> select period_add (200808,2), period_add (20080808, -2) + ---------------------- + --------- ---------------- + | period_add (200808,2) | period_add (20080808, -2) | + ---------------- ------ + ------------------------- + | 200810 | 20080806 | + ----------- ----------- + ------------------------- +
MySQL period_diff (P1, P2): dates P1-P2, returns N months.

mysql> select period_diff (200808, 200801); + ----------------------------- + | period_diff (200808, 200801) | + ----------------------------- + | 7 | + ---------------- ------------- +
In MySQL, these two date functions are rarely used in general.

4. MySQL Date and Time Subtraction Functions: datediff (date1, date2), timediff (time1, time2)

MySQL datediff (date1, date2): Subtract two dates from date1-date2 and return the number of days.

select datediff (‘2008-08-08’, ‘2008-08-01’);-7 select datediff (‘2008-08-01’, ‘2008-08-08’);--7
MySQL timediff (time1, time2): Subtract two dates time1-time2 and return the time difference.

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
Note: The two parameter types of the timediff (time1, time2) function must be the same.

Fourth, MySQL date conversion function, time conversion function
1. MySQL (time, second) conversion functions: time_to_sec (time), sec_to_time (seconds)

select time_to_sec (‘01: 00: 05 ‘);-3605 select sec_to_time (3605);-‘01: 00: 05’
2. MySQL (date, days) conversion function: to_days (date), from_days (days)

select to_days (‘0000-00-00‘);-0 select to_days (‘2008-08-08‘);-733627
select from_days (0);-‘0000-00-00’ select from_days (733627);-‘2008-08-08’
3. MySQL Str to Date (string to date) function: 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
As you can see, the str_to_date (str, format) conversion function can convert some messy strings into date formats. It can also be converted to time. "Format" can be found in the MySQL manual.

4. MySQL Date / Time to Str (date / time to string) functions: date_format (date, format), time_format (time, format)

mysql> select date_format ('2008-08-08 22:23:00', '% W% M% Y'); + --------------------- --------------------------- + | date_format ('2008-08-08 22:23:00', '% W% M% Y ') | + --------------------------------------------- --- + | Friday August 2008 | + ---------------------------------------- -------- +
mysql> select date_format ('2008-08-08 22:23:01', '% Y% m% d% H% i% s'); + --------------- ------------------------------------- + | date_format ('2008-08-08 22:23 : 01 ','% Y% m% d% H% i% s') | + ----------------------------- ----------------------- + | 20080808222301 | + ---------------------- ------------------------------ +
mysql> select time_format ('22: 23: 01 ','% H.% i.% s'); + ------------------------ ------------- + | time_format ('22: 23: 01 ','% H.% i.% s') | + ------------ ------------------------- + | 22.23.01 | + ------------------ ------------------- +
MySQL date and time conversion functions: date_format (date, format), time_format (time, format) can convert a date / time into various string formats. It is an inverse conversion of the str_to_date (str, format) function.

5. MySQL get country time format function: get_format ()

MySQL get_format () syntax:

get_format (date | time | datetime, ‘eur’ | ’usa’ | ’jis’ | ’iso’ | ’internal’
Full examples of MySQL get_format () usage:

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' select 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 ');-'% H% i% s'
The MySQL get_format () function uses fewer opportunities in practice.

6. MySQL piece together 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 ’
Five, MySQL timestamp (Timestamp) function
1. MySQL get 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)
Here is an example:

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 '
select from_unixtime (1218169800, ‘% Y% D% M% h:% i:% s% x’);-‘2008 8th August 12:30:00 2008’
3. MySQL timestamp conversion, increase and decrease functions:

timestamp (date)-date to timestamp timestamp (dt, time)-dt + time timestampadd (unit, interval, datetime_expr)-timestampdiff (unit, datetime_expr1, datetime_expr2)-
Please see the example section:

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
The MySQL timestampadd () function is similar to 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
The MySQL timestampdiff () function is much stronger than the datediff () function. Datediff () can only calculate the number of days between the two dates.

Six, MySQL timezone (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
Time zone conversion can also be implemented by 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
 

Several ways to intercept string functions in MySQL
left (str, length)
Description: left (truncated field, truncated length)
Example: select left (content, 200) as abstract from my_table
2.Truncate strings from the right
right (str, length)
Description: right (truncated field, truncated length)
Example: select right (content, 200) as abstract from my_table
3.Intercept strings
substring (str, pos)
substring (str, pos, length)
Description: substring (truncated field, truncated from the first few digits)
substring (truncated field, truncated from the first few digits, truncated length)
Example: select substring (content, 5) as abstract from my_table
select substring (content, 5,200) as abstract from my_table
(Note: if the number of digits is negative, such as -5, it is the length from the last digit to the end of the string or truncation)
4.Intercept strings by keywords
substring_index (str, delim, count)
Description: substring_index (truncated field, keyword, number of times the keyword appears)
Example: select substring_index ("blog.chinabyte.com", ".", 2) as abstract from my_table
(Note: If the number of times the keyword appears is negative, such as -2, it will count down from the end to the end of the string)
Example: select "..." WHERE 1 = 1 AND L.number = RIGHT (O.flowid, 3) AND L.loyid = 1 Transfer from: http://www.php114.net/2011/0910/71.html
Summary of MySQL date data type and time type usage