板凳-------Mysql cookbook学习（十--5）

6.11 计算年龄 2025年6月11日星期三

--创建表、初始化数据
drop table if exists sibling;
create table sibling
(name  char(20),birth date
);insert into sibling (name,birth) values('Gretchen','1942-04-14');
insert into sibling (name,birth) values('Wilbur','1946-11-28');
insert into sibling (name,birth) values('Franz','1953-03-05');select * from sibling;
+----------+------------+
| name     | birth      |
+----------+------------+
| Gretchen | 1942-04-14 |
| Wilbur   | 1946-11-28 |
| Franz    | 1953-03-05 |
+----------+------------+
3 rows in set (0.00 sec)mysql> select name, birth, curdate() as today, timestampdiff(year, birth, curdate()) as 'age in years' from sibling;
+----------+------------+------------+--------------+
| name     | birth      | today      | age in years |
+----------+------------+------------+--------------+
| Gretchen | 1942-04-14 | 2025-06-11 |           83 |
| Wilbur   | 1946-11-28 | 2025-06-11 |           78 |
| Franz    | 1953-03-05 | 2025-06-11 |           72 |
+----------+------------+------------+--------------+
3 rows in set (0.00 sec)mysql> select name, birth, '1953-03-05' as 'Franz'' birth', timestampdiff(year, birth, '1953-03-05') as 'age in years' from sibling where name != 'Franz';
+----------+------------+--------------+--------------+
| name     | birth      | Franz' birth | age in years |
+----------+------------+--------------+--------------+
| Gretchen | 1942-04-14 | 1953-03-05   |           10 |
| Wilbur   | 1946-11-28 | 1953-03-05   |            6 |
+----------+------------+--------------+--------------+
2 rows in set (0.00 sec)mysql> select name, birth, curdate() as today, timestampdiff(month, birth, curdate()) as 'age in months' from sibling;
+----------+------------+------------+---------------+
| name     | birth      | today      | age in months |
+----------+------------+------------+---------------+
| Gretchen | 1942-04-14 | 2025-06-11 |           997 |
| Wilbur   | 1946-11-28 | 2025-06-11 |           942 |
| Franz    | 1953-03-05 | 2025-06-11 |           867 |
+----------+------------+------------+---------------+
3 rows in set (0.00 sec)mysql> select dayofyear('1995-03-01'), dayofyear('1996-02-29');
+-------------------------+-------------------------+
| dayofyear('1995-03-01') | dayofyear('1996-02-29') |
+-------------------------+-------------------------+
|                      60 |                      60 |
+-------------------------+-------------------------+
1 row in set (0.00 sec)mysql> select right('1995-03-01', 5), right('1996-02-29', 5);
+------------------------+------------------------+
| right('1995-03-01', 5) | right('1996-02-29', 5) |
+------------------------+------------------------+
| 03-01                  | 02-29                  |
+------------------------+------------------------+
1 row in set (0.00 sec)mysql> select if('02-29' < '03-01', '02-29', '03-01') as earliest;
+----------+
| earliest |
+----------+
| 02-29    |
+----------+
1 row in set (0.00 sec)mysql> set @birth = '1965-03-01';
Query OK, 0 rows affected (0.00 sec)mysql> set @target = '1975-01-01';
Query OK, 0 rows affected (0.00 sec)mysql> select @birth, @target, year(@target)- year(@birth) as 'difference',->         if(right(@target, 5) < right(@birth, 5), 1, 0) as 'adjustment',->         year(@target) - year(@birth) - if(right(@target, 5) < right(@birth, 5), 1, 0) as 'age';
+------------+------------+------------+------------+------+
| @birth     | @target    | difference | adjustment | age  |
+------------+------------+------------+------------+------+
| 1965-03-01 | 1975-01-01 |         10 |          1 |    9 |
+------------+------------+------------+------------+------+
1 row in set (0.00 sec)mysql> select name, birth, curdate() as today, year(curdate()) - year(birth) - if(right(curdate(), 5) < right(birth, 5), 1, 0) as 'age in years' from sibling;
+----------+------------+------------+--------------+
| name     | birth      | today      | age in years |
+----------+------------+------------+--------------+
| Gretchen | 1942-04-14 | 2025-06-11 |           83 |
| Wilbur   | 1946-11-28 | 2025-06-11 |           78 |
| Franz    | 1953-03-05 | 2025-06-11 |           72 |
+----------+------------+------------+--------------+
3 rows in set (0.00 sec)mysql> select name, birth, '1953-03-05' as 'Franz'' birthday', year('1953-03-05') - year(birth) - if(right('1953-03-05', 5) < right(birth, 5), 1, 0) as 'age in years' from sibling where name != 'Franz';
+----------+------------+-----------------+--------------+
| name     | birth      | Franz' birthday | age in years |
+----------+------------+-----------------+--------------+
| Gretchen | 1942-04-14 | 1953-03-05      |           10 |
| Wilbur   | 1946-11-28 | 1953-03-05      |            6 |
+----------+------------+-----------------+--------------+
2 rows in set (0.00 sec)mysql> select name, birth, curdate() as today, (year(curdate()) - year(birth)) * 12 + (month(curdate()) - month(birth)) - if(dayofmonth(curdate()) < dayofmonth(birth), 1, 0) as 'age in months' from sibling;
+----------+------------+------------+---------------+
| name     | birth      | today      | age in months |
+----------+------------+------------+---------------+
| Gretchen | 1942-04-14 | 2025-06-11 |           997 |
| Wilbur   | 1946-11-28 | 2025-06-11 |           942 |
| Franz    | 1953-03-05 | 2025-06-11 |           867 |
+----------+------------+------------+---------------+
3 rows in set (0.00 sec)--将一个日期和时间值切换到另一个时区
mysql> set @dt = '2006-11-23 09:00:00';
Query OK, 0 rows affected (0.00 sec)mysql> select @dt as Chicago, convert_tz(@dt, 'US/Central', 'Europe/Berlin') as Berlin,->         convert_tz(@dt, 'US/Central', 'Europe/London') as London,->         convert_tz(@dt, 'US/Central', 'America/Edmonton') as Edmonton,->         convert_tz(@dt, 'US/Central', 'Australia/Brisbane') as Brisbane\G
*************************** 1. row ***************************Chicago: 2006-11-23 09:00:00Berlin: NULLLondon: NULL
Edmonton: NULL
Brisbane: NULL
1 row in set (0.02 sec)mysql> select @dt as Chicago, convert_tz(@dt, '-06:00', '+01:00') as Berlin,->         convert_tz(@dt, '-06:00', '+00:00') as London,->         convert_tz(@dt, '-06:00', '-07:00') as Edmonton,->         convert_tz(@dt, '-06:00', '+10:00') as Brisbane\G
*************************** 1. row ***************************Chicago: 2006-11-23 09:00:00Berlin: 2006-11-23 16:00:00.000000London: 2006-11-23 15:00:00.000000
Edmonton: 2006-11-23 08:00:00.000000
Brisbane: 2006-11-24 01:00:00.000000
1 row in set (0.00 sec)

6.12 将一个日期和时间值切换到另一个时区

mysql> set @dt = '2006-11-23 09:00:00';
Query OK, 0 rows affected (0.00 sec)mysql> select @dt as Chicago, convert_tz(@dt, 'US/Central', 'Europe/Berlin') as Berlin,->         convert_tz(@dt, 'US/Central', 'Europe/London') as London,->         convert_tz(@dt, 'US/Central', 'America/Edmonton') as Edmonton,->         convert_tz(@dt, 'US/Central', 'Australia/Brisbane') as Brisbane\G
*************************** 1. row ***************************Chicago: 2006-11-23 09:00:00Berlin: NULLLondon: NULL
Edmonton: NULL
Brisbane: NULL
1 row in set (0.00 sec)mysql> select @dt as Chicago, convert_tz(@dt, '-06:00', '+01:00') as Berlin,->         convert_tz(@dt, '-06:00', '+00:00') as London,->         convert_tz(@dt, '-06:00', '-07:00') as Edmonton,->         convert_tz(@dt, '-06:00', '+10:00') as Brisbane\G
*************************** 1. row ***************************Chicago: 2006-11-23 09:00:00Berlin: 2006-11-23 16:00:00.000000London: 2006-11-23 15:00:00.000000
Edmonton: 2006-11-23 08:00:00.000000
Brisbane: 2006-11-24 01:00:00.000000
1 row in set (0.00 sec)

6.13 找出每月的第一天，最后一天或者天数

mysql> select d, date_format(d, '%Y-%m-01') as method1,->         concat(year(d), '-', lpad(month(d), 2, '0'), '-01') as method2-> from date_val;
+------------+------------+------------+
| d          | method1    | method2    |
+------------+------------+------------+
| 1864-02-28 | 1864-02-01 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 | 1900-01-01 |
| 1999-12-31 | 1999-12-01 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 | 2000-06-01 |
| 2017-03-16 | 2017-03-01 | 2017-03-01 |
| 1864-02-28 | 1864-02-01 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 | 1900-01-01 |
| 1999-12-31 | 1999-12-01 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 | 2000-06-01 |
| 2017-03-16 | 2017-03-01 | 2017-03-01 |
| 2007-05-13 | 2007-05-01 | 2007-05-01 |
+------------+------------+------------+
11 rows in set (0.01 sec)mysql>
mysql> select d, date_format(d, '%Y-01-01') as method1,->         concat(year(d), '-01-01') as method2-> from date_val;
+------------+------------+------------+
| d          | method1    | method2    |
+------------+------------+------------+
| 1864-02-28 | 1864-01-01 | 1864-01-01 |
| 1900-01-15 | 1900-01-01 | 1900-01-01 |
| 1999-12-31 | 1999-01-01 | 1999-01-01 |
| 2000-06-04 | 2000-01-01 | 2000-01-01 |
| 2017-03-16 | 2017-01-01 | 2017-01-01 |
| 1864-02-28 | 1864-01-01 | 1864-01-01 |
| 1900-01-15 | 1900-01-01 | 1900-01-01 |
| 1999-12-31 | 1999-01-01 | 1999-01-01 |
| 2000-06-04 | 2000-01-01 | 2000-01-01 |
| 2017-03-16 | 2017-01-01 | 2017-01-01 |
| 2007-05-13 | 2007-01-01 | 2007-01-01 |
+------------+------------+------------+
11 rows in set (0.00 sec)mysql>
mysql> select d, date_format(d, '%Y-12-15') as method1,->         concat(year(d), '-12-15') as method2-> from date_val;
+------------+------------+------------+
| d          | method1    | method2    |
+------------+------------+------------+
| 1864-02-28 | 1864-12-15 | 1864-12-15 |
| 1900-01-15 | 1900-12-15 | 1900-12-15 |
| 1999-12-31 | 1999-12-15 | 1999-12-15 |
| 2000-06-04 | 2000-12-15 | 2000-12-15 |
| 2017-03-16 | 2017-12-15 | 2017-12-15 |
| 1864-02-28 | 1864-12-15 | 1864-12-15 |
| 1900-01-15 | 1900-12-15 | 1900-12-15 |
| 1999-12-31 | 1999-12-15 | 1999-12-15 |
| 2000-06-04 | 2000-12-15 | 2000-12-15 |
| 2017-03-16 | 2017-12-15 | 2017-12-15 |
| 2007-05-13 | 2007-12-15 | 2007-12-15 |
+------------+------------+------------+
11 rows in set (0.00 sec)mysql>
mysql> select curdate(), date_add(date_format(curdate(), '%Y-12-25'), interval 2 year) as method1,->         date_format(date_add(curdate(), interval 2 year), '%Y-12-25') as method2;
+------------+------------+------------+
| curdate()  | method1    | method2    |
+------------+------------+------------+
| 2025-06-11 | 2027-12-25 | 2027-12-25 |
+------------+------------+------------+
1 row in set (0.00 sec)

6.14 通过子串替换来计算日期

mysql> select curdate(), dayname(curdate());
+------------+--------------------+
| curdate()  | dayname(curdate()) |
+------------+--------------------+
| 2025-06-11 | Wednesday          |
+------------+--------------------+
1 row in set (0.00 sec)
--一个月的第一天是星期几
mysql> set @d = curdate();
Query OK, 0 rows affected (0.00 sec)mysql> set @first = date_sub(@d, interval dayofmonth(@d)-1 day);
Query OK, 0 rows affected (0.00 sec)mysql> select @d as 'starting date',->         @first as '1st of month date',->         dayname(@first) as '1st of month day';
+---------------+-------------------+------------------+
| starting date | 1st of month date | 1st of month day |
+---------------+-------------------+------------------+
| 2025-06-11    | 2025-06-01        | Sunday           |
+---------------+-------------------+------------------+
1 row in set (0.00 sec)

6.15 计算某个日期为星期几

mysql> select d, dayname(d) as day,->         date_add(d, interval 1-dayofweek(d) day) as sunday,->         date_add(d, interval 7-dayofweek(d) day) as saturday-> from date_val;
+------------+----------+------------+------------+
| d          | day      | sunday     | saturday   |
+------------+----------+------------+------------+
| 1864-02-28 | Sunday   | 1864-02-28 | 1864-03-05 |
| 1900-01-15 | Monday   | 1900-01-14 | 1900-01-20 |
| 1999-12-31 | Friday   | 1999-12-26 | 2000-01-01 |
| 2000-06-04 | Sunday   | 2000-06-04 | 2000-06-10 |
| 2017-03-16 | Thursday | 2017-03-12 | 2017-03-18 |
| 1864-02-28 | Sunday   | 1864-02-28 | 1864-03-05 |
| 1900-01-15 | Monday   | 1900-01-14 | 1900-01-20 |
| 1999-12-31 | Friday   | 1999-12-26 | 2000-01-01 |
| 2000-06-04 | Sunday   | 2000-06-04 | 2000-06-10 |
| 2017-03-16 | Thursday | 2017-03-12 | 2017-03-18 |
| 2007-05-13 | Sunday   | 2007-05-13 | 2007-05-19 |
+------------+----------+------------+------------+
11 rows in set (0.00 sec)
--两个星期前星期三的日期
mysql> set @target = date_sub(date_add(curdate(), interval 4-dayofweek(curdate()) day), interval 14 day);
Query OK, 0 rows affected (0.00 sec)mysql> select curdate(), @target, dayname(@target);
+------------+------------+------------------+
| curdate()  | @target    | dayname(@target) |
+------------+------------+------------------+
| 2025-06-11 | 2025-05-28 | Wednesday        |
+------------+------------+------------------+
1 row in set (0.00 sec)mysql>
mysql> set @target = date_add(date_sub(curdate(), interval 14 day), interval 4-dayofweek(curdate()) day);
Query OK, 0 rows affected (0.00 sec)mysql> select curdate(), @target, dayname(@target);
+------------+------------+------------------+
| curdate()  | @target    | dayname(@target) |
+------------+------------+------------------+
| 2025-06-11 | 2025-05-28 | Wednesday        |
+------------+------------+------------------+
1 row in set (0.00 sec)

6.16 查出给定某周的某天的日期

mysql> select d, dayname(d) as day,->         date_add(d, interval 1-dayofweek(d) day) as sunday,->         date_add(d, interval 7-dayofweek(d) day) as saturday-> from date_val;
+------------+----------+------------+------------+
| d          | day      | sunday     | saturday   |
+------------+----------+------------+------------+
| 1864-02-28 | Sunday   | 1864-02-28 | 1864-03-05 |
| 1900-01-15 | Monday   | 1900-01-14 | 1900-01-20 |
| 1999-12-31 | Friday   | 1999-12-26 | 2000-01-01 |
| 2000-06-04 | Sunday   | 2000-06-04 | 2000-06-10 |
| 2017-03-16 | Thursday | 2017-03-12 | 2017-03-18 |
| 1864-02-28 | Sunday   | 1864-02-28 | 1864-03-05 |
| 1900-01-15 | Monday   | 1900-01-14 | 1900-01-20 |
| 1999-12-31 | Friday   | 1999-12-26 | 2000-01-01 |
| 2000-06-04 | Sunday   | 2000-06-04 | 2000-06-10 |
| 2017-03-16 | Thursday | 2017-03-12 | 2017-03-18 |
| 2007-05-13 | Sunday   | 2007-05-13 | 2007-05-19 |
+------------+----------+------------+------------+
11 rows in set (0.00 sec)
--两个星期前星期三的日期
set @target = date_sub(date_add(curdate(), interval 4-dayofweek(curdate()) day), interval 14 day);
select curdate(), @target, dayname(@target);set @target = date_add(date_sub(curdate(), interval 14 day), interval 4-dayofweek(curdate()) day);
select curdate(), @target, dayname(@target);

6.17 执行闰年计算

mysql> select d, year(d) % 4 = 0 as 'rule-of-thumb test',->         (year(d) % 4 = 0) and ((year(d) % 100 != 0) or (year(d) % 400 = 0))->         as 'complete test'-> from date_val;
+------------+--------------------+---------------+
| d          | rule-of-thumb test | complete test |
+------------+--------------------+---------------+
| 1864-02-28 |                  1 |             1 |
| 1900-01-15 |                  1 |             0 |
| 1999-12-31 |                  0 |             0 |
| 2000-06-04 |                  1 |             1 |
| 2017-03-16 |                  0 |             0 |
| 1864-02-28 |                  1 |             1 |
| 1900-01-15 |                  1 |             0 |
| 1999-12-31 |                  0 |             0 |
| 2000-06-04 |                  1 |             1 |
| 2017-03-16 |                  0 |             0 |
| 2007-05-13 |                  0 |             0 |
+------------+--------------------+---------------+
11 rows in set (0.00 sec)mysql>
mysql> set @d = '2006-04-13';
Query OK, 0 rows affected (0.00 sec)mysql> select dayofyear(date_format(@d, '%Y-12-31'));
+----------------------------------------+
| dayofyear(date_format(@d, '%Y-12-31')) |
+----------------------------------------+
|                                    365 |
+----------------------------------------+
1 row in set (0.00 sec)mysql>
mysql> set @d = '2008-04-13';
Query OK, 0 rows affected (0.00 sec)mysql> select dayofyear(date_format(@d, '%Y-12-31'));
+----------------------------------------+
| dayofyear(date_format(@d, '%Y-12-31')) |
+----------------------------------------+
|                                    366 |
+----------------------------------------+
1 row in set (0.00 sec)

6.18 接近但不是iso格式的日期格式

mysql> select d, concat(year(d), '-', month(d), '-01') from date_val;
+------------+---------------------------------------+
| d          | concat(year(d), '-', month(d), '-01') |
+------------+---------------------------------------+
| 1864-02-28 | 1864-2-01                             |
| 1900-01-15 | 1900-1-01                             |
| 1999-12-31 | 1999-12-01                            |
| 2000-06-04 | 2000-6-01                             |
| 2017-03-16 | 2017-3-01                             |
| 1864-02-28 | 1864-2-01                             |
| 1900-01-15 | 1900-1-01                             |
| 1999-12-31 | 1999-12-01                            |
| 2000-06-04 | 2000-6-01                             |
| 2017-03-16 | 2017-3-01                             |
| 2007-05-13 | 2007-5-01                             |
+------------+---------------------------------------+
11 rows in set (0.00 sec)mysql> select d, concat(year(d), '-', lpad(month(d), 2, '0'), '-01') from date_val;
+------------+-----------------------------------------------------+
| d          | concat(year(d), '-', lpad(month(d), 2, '0'), '-01') |
+------------+-----------------------------------------------------+
| 1864-02-28 | 1864-02-01                                          |
| 1900-01-15 | 1900-01-01                                          |
| 1999-12-31 | 1999-12-01                                          |
| 2000-06-04 | 2000-06-01                                          |
| 2017-03-16 | 2017-03-01                                          |
| 1864-02-28 | 1864-02-01                                          |
| 1900-01-15 | 1900-01-01                                          |
| 1999-12-31 | 1999-12-01                                          |
| 2000-06-04 | 2000-06-01                                          |
| 2017-03-16 | 2017-03-01                                          |
| 2007-05-13 | 2007-05-01                                          |
+------------+-----------------------------------------------------+
11 rows in set (0.00 sec)mysql>
mysql> select concat(year(d), '-', month(d), '-01') as 'non-iso',->         date_add(concat(year(d), '-', month(d), '-01'), interval 0 day) as 'iso 1',->         concat(year(d), '-', month(d), '-01') + interval 0 day as 'iso2',->         from_days(to_days(concat(year(d), '-', month(d), '-01'))) as 'iso 3',->         str_to_date(concat(year(d), '-', month(d), '-01'), '%Y-%m-%d') as 'iso 4'-> from date_val;
+------------+------------+------------+------------+------------+
| non-iso    | iso 1      | iso2       | iso 3      | iso 4      |
+------------+------------+------------+------------+------------+
| 1864-2-01  | 1864-02-01 | 1864-02-01 | 1864-02-01 | 1864-02-01 |
| 1900-1-01  | 1900-01-01 | 1900-01-01 | 1900-01-01 | 1900-01-01 |
| 1999-12-01 | 1999-12-01 | 1999-12-01 | 1999-12-01 | 1999-12-01 |
| 2000-6-01  | 2000-06-01 | 2000-06-01 | 2000-06-01 | 2000-06-01 |
| 2017-3-01  | 2017-03-01 | 2017-03-01 | 2017-03-01 | 2017-03-01 |
| 1864-2-01  | 1864-02-01 | 1864-02-01 | 1864-02-01 | 1864-02-01 |
| 1900-1-01  | 1900-01-01 | 1900-01-01 | 1900-01-01 | 1900-01-01 |
| 1999-12-01 | 1999-12-01 | 1999-12-01 | 1999-12-01 | 1999-12-01 |
| 2000-6-01  | 2000-06-01 | 2000-06-01 | 2000-06-01 | 2000-06-01 |
| 2017-3-01  | 2017-03-01 | 2017-03-01 | 2017-03-01 | 2017-03-01 |
| 2007-5-01  | 2007-05-01 | 2007-05-01 | 2007-05-01 | 2007-05-01 |
+------------+------------+------------+------------+------------+
11 rows in set (0.00 sec)

6.19 将日期或时间当成数值

mysql> select t1, t1+0 as 't1 as number',->     floor(t1) as 't1 as number',->     floor(t1/10000) as 'hour part'-> from time_val;
+----------+--------------+--------------+-----------+
| t1       | t1 as number | t1 as number | hour part |
+----------+--------------+--------------+-----------+
| 15:00:00 |       150000 |       150000 |        15 |
| 05:01:30 |        50130 |        50130 |         5 |
| 12:30:20 |       123020 |       123020 |        12 |
+----------+--------------+--------------+-----------+
3 rows in set (0.01 sec)mysql>
mysql> select d, d+0 from date_val;
+------------+----------+
| d          | d+0      |
+------------+----------+
| 1864-02-28 | 18640228 |
| 1900-01-15 | 19000115 |
| 1999-12-31 | 19991231 |
| 2000-06-04 | 20000604 |
| 2017-03-16 | 20170316 |
| 1864-02-28 | 18640228 |
| 1900-01-15 | 19000115 |
| 1999-12-31 | 19991231 |
| 2000-06-04 | 20000604 |
| 2017-03-16 | 20170316 |
| 2007-05-13 | 20070513 |
+------------+----------+
11 rows in set (0.00 sec)mysql> select dt, dt+0, floor(dt+0) from datetime_val;
+---------------------+----------------+----------------+
| dt                  | dt+0           | floor(dt+0)    |
+---------------------+----------------+----------------+
| 1970-01-01 00:00:00 | 19700101000000 | 19700101000000 |
| 1999-12-31 09:00:00 | 19991231090000 | 19991231090000 |
| 2000-06-04 15:45:30 | 20000604154530 | 20000604154530 |
| 2017-03-16 12:30:15 | 20170316123015 | 20170316123015 |
| 1970-01-01 00:00:00 | 19700101000000 | 19700101000000 |
| 1999-12-31 09:00:00 | 19991231090000 | 19991231090000 |
| 2000-06-04 15:45:30 | 20000604154530 | 20000604154530 |
| 2017-03-16 12:30:15 | 20170316123015 | 20170316123015 |
+---------------------+----------------+----------------+
8 rows in set (0.00 sec)mysql> select '1999-01-01' + 0, '1999-01-01 12:30:45' + 0, '12:30:45'+0;
+------------------+---------------------------+--------------+
| '1999-01-01' + 0 | '1999-01-01 12:30:45' + 0 | '12:30:45'+0 |
+------------------+---------------------------+--------------+
|             1999 |                      1999 |           12 |
+------------------+---------------------------+--------------+
1 row in set, 3 warnings (0.00 sec)mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1999-01-01'          |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1999-01-01 12:30:45' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '12:30:45'            |
+---------+------+---------------------------------------------------------+
3 rows in set (0.00 sec)

6.20 强制mysql将字符串当作时间值

mysql> select '12:30:45'+0, sec_to_time(time_to_sec('12:30:45'))+0;
+--------------+----------------------------------------+
| '12:30:45'+0 | sec_to_time(time_to_sec('12:30:45'))+0 |
+--------------+----------------------------------------+
|           12 |                                 123045 |
+--------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)mysql> select '1999-01-01'+0, from_days(to_days('1999-01-01'))+0;
+----------------+------------------------------------+
| '1999-01-01'+0 | from_days(to_days('1999-01-01'))+0 |
+----------------+------------------------------------+
|           1999 |                           19990101 |
+----------------+------------------------------------+
1 row in set, 1 warning (0.00 sec)mysql> select date_add('1999-01-01 12:30:45', interval 0 day)+0 as 'numberic datetime';
+-------------------+
| numberic datetime |
+-------------------+
|    19990101123045 |
+-------------------+
1 row in set (0.00 sec)