Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to store NULL values in datetime fields in MySQL?

I have a "bill_date" field that I want to be blank (NULL) until it's been billed, at which point the date will be entered.

I see that MySQL does not like NULL values in datetime fields. Do any of you have a simple way to handle this, or am I forced to use the min date as a "NULL equivalent" and then check for that date?

Thanks.

EDITED TO ADD:

Ok I do see that MySQL will accept the NULL value, but it won't accept it as a database update if I'm updating the record using PHP.

The variable name is $bill_date but it won't leave the variable as NULL if I update a record without sending a value to $bill_date -- I get this error:

Database query failed: Incorrect datetime value: '' for column 'bill_date' at row 1 

I assume I need to actually send the word NULL, or leave it out of the update query altogether, to avoid this error? Am I right? Thanks!!!

like image 313
rhodesjason Avatar asked Nov 06 '09 23:11

rhodesjason


People also ask

Can DateTime be NULL in MySQL?

The . NET DateTime data type cannot handle NULL values. As such, when assigning values from a query to a DateTime variable, you must first check whether the value is in fact NULL .

Can a DateTime field be NULL?

Using a DateTime column in an SQL table is quite common. Using it in . Net has one limitation – DateTime cannot be null as it is a struct and not a class.

How do you handle NULL values in a date column?

The DATE and TIMESTAMP functions do exactly what you are looking for. If you have a STRING column where its format is like TIMESTAMP , you can simply apply it. Then, DATE will extract just the date and it takes care of the NULL values.

Can we insert NULL in date column?

"NULL" can be specified as a value in the Date field to get an empty/blank by using INSERT statement. Example: CREATE table test1 (col1 date); INSERT into test1 values (NULL);


2 Answers

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) 

EDIT #1: I see in your edit that the error message you are getting in PHP indicates that you are passing an empty string (i.e. ''), not null. An empty string is different than null and is not a valid datetime value which is why you are getting that error message. You must pass the special sql keyword null if that's what you mean. Also, don't put any quotes around the word null. See my insert statement above for an example of how to insert null.

EDIT #2: Are you using PDO? If so, when you bind your null param, make sure to use the [PDO::PARAM_NULL][1] type when binding a null. See the answer to this stackoverflow question on how to properly insert null using PDO.

like image 159
Asaph Avatar answered Oct 07 '22 04:10

Asaph


This is a a sensible point.

A null date is not a zero date. They may look the same, but they ain't. In mysql, a null date value is null. A zero date value is an empty string ('') and '0000-00-00 00:00:00'

On a null date "... where mydate = ''" will fail.
On an empty/zero date "... where mydate is null" will fail.

But now let's get funky. In mysql dates, empty/zero date are strictly the same.

by example

 select if(myDate is null, 'null', myDate) as mydate from myTable where  myDate = ''; select if(myDate is null, 'null', myDate) as mydate from myTable where  myDate = '0000-00-00 00:00:00' 

will BOTH output: '0000-00-00 00:00:00'. if you update myDate with '' or '0000-00-00 00:00:00', both selects will still work the same.

In php, the mysql null dates type will be respected with the standard mysql connector, and be real nulls ($var === null, is_null($var)). Empty dates will always be represented as '0000-00-00 00:00:00'.

I strongly advise to use only null dates, OR only empty dates if you can. (some systems will use "virual" zero dates which are valid Gregorian dates, like 1970-01-01 (linux) or 0001-01-01 (oracle).

empty dates are easier in php/mysql. You don't have the "where field is null" to handle. However, you have to "manually" transform the '0000-00-00 00:00:00' date in '' to display empty fields. (to store or search you don't have special case to handle for zero dates, which is nice).

Null dates need better care. you have to be careful when you insert or update to NOT add quotes around null, else a zero date will be inserted instead of null, which causes your standard data havoc. In search forms, you will need to handle cases like "and mydate is not null", and so on.

Null dates are usually more work. but they much MUCH MUCH faster than zero dates for queries.

like image 32
roselan Avatar answered Oct 07 '22 04:10

roselan