(based on a conversation with a colleague, and a bit of Twitter) A Conundrum A developer colleague paged me with this:
1 2 3 4 |
mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 2 YEAR) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 2 YEAR) as delta\G delta: 420 |
It is obviously wrong, and weirdly so. It only works for “2 year”, not with other values:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-11 year_month) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-11 year_month) as delta\G delta: 3600 mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-12 year_month) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-12 year_month) as delta\G delta: 3600 mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-13 year_month) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-13 year_month) as delta\G delta: 3600 |
It has to be exactly 730 days (2 * 365 days, 2 years):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 729 day) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 729 day) as delta\G delta: 3600 mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 730 day) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 730 day) as delta\G delta: 420 mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 731 day) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 731 day) as delta\G delta: 3600 |
The […]
Read More