How to Convert Date to Datetime in MySQL

MySQLpythonsqlalchemytype conversion

I'm supposed to add time to a date field. We use MySQL with python3, alembic and sqlalchemy. Now I've written a conversion program. It is my first program of such a kind. I'm not certain how it will work, how I can also migrate the data to the new field or if that is a different scenario.

I'm not sure about ? downgrade , what should it do if it is downgrading? In the examples I've looked at examples where nearly all do an ADD TABLE and therefore at the downgrade those function just delete what they created. But what about an ALTER tatement, how can I roll it back in the downgrade function if downgrading (which probably not will happen).

Is there anything else that I should think about?

def upgrade(**args):
    db_update_context = mnet_get_required_argument("db_update_context", **args)

    if not mnet_column_exists(table_name, column_name, **args):
        sql = textwrap.dedent("""\
                ALTER TABLE {} MODIFY COLUMN `{}` datetime;
                  """.format(table_name, column_name))
        mnet_execute(db_update_context, sql)

        sql = textwrap.dedent("""\
                ALTER TABLE {} MODIFY COLUMN `{}` datetime;
                  """.format(table_name, column_name2))
        mnet_execute(db_update_context, sql)

def downgrade(**args): # TODO: What is supposed to happen here
    columns_to_drop = [column_name]
    for column_to_drop in columns_to_drop:
        medinet_drop_column(table_name, column_to_drop, **args)

Best Answer

Create and populate:

mysql> CREATE TABLE grade(d DATE NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO grade (d) VALUES (NOW()), (CURDATE() - INTERVAL 1 DAY);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'd' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM grade;
+------------+
| d          |
+------------+
| 2017-11-22 |
| 2017-11-21 |
+------------+
2 rows in set (0.00 sec)

Upgrade:

mysql> ALTER TABLE grade MODIFY COLUMN d DATETIME NOT NULL;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM grade;
+---------------------+
| d                   |
+---------------------+
| 2017-11-22 00:00:00 |
| 2017-11-21 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO grade (d) VALUES (NOW()), (CURDATE() - INTERVAL 10 DAY);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM grade;
+---------------------+
| d                   |
+---------------------+
| 2017-11-22 00:00:00 |
| 2017-11-21 00:00:00 |
| 2017-11-22 13:56:37 |
| 2017-11-12 00:00:00 |
+---------------------+
4 rows in set (0.00 sec)

Downgrade:

mysql> ALTER TABLE grade MODIFY COLUMN d DATE NOT NULL;
Query OK, 4 rows affected, 1 warning (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'd' at row 3 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM grade;
+------------+
| d          |
+------------+
| 2017-11-22 |
| 2017-11-21 |
| 2017-11-22 |
| 2017-11-12 |
+------------+
4 rows in set (0.00 sec)

mysql>

I deliberately included trying to put a DATETIME into a DATE column to show what happens, and to show the Warning that occurs.

Related Question