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:00
Berlin: NULL
London: 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:00
Berlin: 2006-11-23 16:00:00.000000
London: 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:00
Berlin: NULL
London: 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:00
Berlin: 2006-11-23 16:00:00.000000
London: 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)
6.21 基于时间特性来查询行
https://blog.csdn.net/liqfyiyi/article/details/50886752