Type convert from date to datetime in MySQL

Question

If I type convert from date to datetime in order to store time of day, what will be the default value for the time of day? I want to migrate the data which is just dates. There are pairs from "value_to" and "value_from" in the data. These values have been dates and should be datetimes instead. I suppose I can make the conversion with alembic+sqlalchemy upgrades and the downgrade with be to convert it back. I suppose it is not a problem and "value_from" should be 00:00:00 and "value_to" should be 23:59:59 but do you see any mistakes with my plan?

Answer 1

This kind of question can be answered without alembic+sqlalchemy. This is what your test database is for. Just try it with the mysql client:

mysql> use test;

mysql> create table foo ( d date );

mysql> insert into foo set d = now();

mysql> select * from foo;
+------------+
| d          |
+------------+
| 2017-11-22 |
+------------+

mysql> alter table foo modify d datetime;

mysql> select * from foo;
+---------------------+
| d                   |
+---------------------+
| 2017-11-22 00:00:00 |
+---------------------+

And yes, if your column is DATETIME and you want to search for a certain day you can use BETWEEN:

mysql> select * from foo where d between '2017-11-22 00:00:00' and '2017-11-22 23:59:59';

But if you use DATETIME with fractional precision, you might want to do inequalities, so you don't miss the last second of the day:

mysql> select * from foo where d >= '2017-11-22 00:00:00' and d < '2017-11-23 00:00:00';