How to store NULL values in datetime fields in MySQL?

by

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.

More Related Contents:

  1. PHP DateTime supported formats
  2. timestamp within a form [closed]
  3. Php date() Date and time is stuck [closed]
  4. Convert from MySQL datetime to another format with PHP
  5. How to store repeating dates keeping in mind Daylight Savings Time
  6. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  7. MySQL datetime fields and daylight savings time — how do I reference the “extra” hour?
  8. PHP/MySQL Insert null values
  9. Object of class DateTime could not be converted to string
  10. How to separate DATE and TIME from DATETIME in MySQL?
  11. PHP, MySQL and Time Zones
  12. Set timezone in PHP and MySQL
  13. using nulls in a mysqli prepared statement
  14. PHP Check for NULL
  15. How to convert ISO8601 to Date format in php
  16. Date in a URL dd/mm/yyyy
  17. mysql – INSERT date range into date columns IF dates don’t overlap with existing ones
  18. MySQL and PHP – insert NULL rather than empty string
  19. How to select rows where column value IS NOT NULL using CodeIgniter’s ActiveRecord?
  20. adding 30 minutes to datetime php/mysql
  21. Using MySQL’s TIMESTAMP vs storing timestamps directly
  22. adding 1 day to a DATETIME format value
  23. MySQL, how to insert null dates
  24. How to apply bindValue method in LIMIT clause?
  25. mysql_fetch_array() expects parameter 1 to be resource problem [duplicate]
  26. mysql select query within a serialized array
  27. ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
  28. return one value from database with mysql php pdo
  29. How can I pass an array of PDO parameters yet still specify their types?
  30. Which is fastest in PHP- MySQL or MySQLi?

Leave a Comment