asked on

MYSQL/PHP inserting 1969-12-31 instead of NULL

$emptyDate = '';

when I insert a row with a column (type DATE, NULL allowed, default NULL) and insert $emptyDate I end up with 1969-12-31 in the column.

I understand the unix epoch starting in 1970, and the 1969 part is likely a timezone thing but why isn't it inserting NULL instead?

FYI it does inserts 1969-12-31 when $emptyDate = NULL and $emptyDate = 'NULL' too

Chris Stanyon

You're trying to insert an empty string into your database:

$emptyDate = '';

which is not the same as NULL.

It can't convert an empty string to a date, so it sets it to the default value for that data type

Dave Baldwin

To complicate the problem, I think that a date column always returns a date, not a NULL. ??

Chris Stanyon

@Dave - you can insert a NULL into a date column (if it's NULLable) - or at least I can on my current version of MySQL ;)

SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tf2012

ASKER

working on this now, I'll have some feedback shortly

tf2012

ASKER

@ray thanks,

I'm using $tomorrowDateTime = new DateTime('tomorrow');
$tomorrowDate = $tomorrowDateTime->format('Y-m-d'); // going from memory here

And it produces well formed MySQL compatible date strings for inserting into the column with the DATE type.

The problem arises when I try to insert NULL, 'NULL', or ''. It ends up being 1969. With a raw SQL statement I can insert NULL and it stores NULL which tells me that the code igniter DB class must be converting the NULL value to a string 'NULL' etc. I'm working in that direction and I may end up using a SQL statement instead of the db class in code igniter for this task. I'll be back in a bit with the outcome.

@Chris & Dave, as mentioned above the column will store NULL when updated with an SQL statement, the NULL value seems to be corrupted when run through the code igniter db class.

Ray Paseur

Sorry - I missed the part about CodeIgniter. Sometimes these frameworks are far too "smart" for us, and end up doing things we really do not want.

Instead of NULLable DATE columns, I indicate the lack of a date value by setting the DEFAULT value to 0000-00-00 and that has worked well for me.

tf2012

ASKER

Ha!
Ray is right... again.

I just found this nested in a method that calls my model just before the insert/update
// format the reminder date
$reminderDate = strtotime($reminderDate);

tf2012

ASKER

forgot to mention the little CodeIgniter detail sorry

tf2012

ASKER

Thanks for the help. In the end I found, as Ray suggested, I was trying to pass a null value to strtotime() and it was returning 1969. I ditched the strtotime() call and it is happily inserting NULL when a date is not passed to the insert/update queries.