How to Store Null Values in Datetime Fields in MySQL

How to store NULL values in datetime fields in MySQL?

MySQL does allow NULL values for datetime fields. I just tested it:

mysql> create table datetimetest (testcolumn datetime null default null);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into datetimetest (testcolumn) values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from datetimetest;
+------------+
| testcolumn |
+------------+
| NULL       | 
+------------+
1 row in set (0.00 sec)

I'm using this version:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.45    | 
+-----------+
1 row in set (0.03 sec)

Speaking of inserting NULL values through PHP code, given you are using prepared statements (as you definitely should), there is no problem with inserting NULL values. Just bind your variable the usual way, and all PHP variables that contain null values will be stored as NULL in MySQL.

Just make sure that your PHP variable indeed contains NULL and not an empty string.

Store null time in datetime field?

A datetime is a datatype which contains both the date and the time part, and the other one cannot be null.

If you need to have separate case where the time can be null, you have two options: use separate fields to the date and the datetime or use separate fields for date and time. The best solution really depends on what the data represents (if it is a same thing or a different thing in real life).

Mysql - inserting NULL values in datetime field

Send an unquoted literal NULL to MySQL. MySQL does not know of your PHP's NULL type.

UPDATE tbl_dummy SET Id=3,dtp=NULL,Remark="update1"

How to import datetime field coded with `NULL` keyword in MySQL?

In MySQL version >=8.0 by default local_infile is off. To load you need to first enable it.

set global local_infile = on;

load data local infile 'E:so.csv' into table dummy
fields terminated by ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(Subject,@Date_entry)
set Date_entry=if(@Date_entry='',null, @Date_entry) ;

It will insert the data. If it throws error then share the result of sql_mode.

select @@sql_mode;

Note: Make sure that the csv on Date_entry column is yyyy-mm-dd hh:mm:ss format as MySQL does, btw in your csv it is not in db format. I have tested the above case in MySQL Workbench.

How to set to NULL a datetime with 0000-00-00 00:00:00 value?

You need to first make the column nullable:

ALTER TABLE mytable MODIFY COLUMN field DATETIME NULL;

And then update the values:

UPDATE mytable SET field = NULL WHERE field = '0000-00-00 00:00:00';

---

Related Topics