[Dbix-class] Re: RFC: Patch for InflateColumn::Datetime to handle invalid date values

[Jason Kohles]

On Jun 15, 2007, at 7:31 AM, Matt S Trout wrote:

> On Thu, Jun 14, 2007 at 09:14:48PM -0500, Brandon Black wrote:
>> On 6/14/07, A. Pagaltzis <pagaltzis at gmx.de> wrote:
>>> * Matt S Trout <dbix-class at trout.me.uk> [2007-06-14 19:50]:
>>>> if mysql considers that equivalent to null
>>>
>>> It does *and* does not:
>>
>> That doesn't surprise me at all.  It's not like you're using an  
>> actual
>> relational database :p
>>
>> Maybe this will finally drive it home for everyone: MySQL is to
>> database server software what PHP is to programming languages.
>
> I'm starting to come to the conclusion that that's a slight to PHP.
>
Timestamps were one of the things that finally made me just refuse to
use MySQL, rather than to simply prefer something else.  In addition to
what has already been mentioned, here are some of the things that
drove me crazy on this topic...

* In some versions you can put 0's in the day and month columns.
* For the first timestamp column in a table, if you say 'default null'
    it actually means 'default current_timestamp'
* For the second-and-later timestamp columns in a table, 'default null'
    means 'default 0'
* If you want a timestamp column other than the first one to be
    automatically updated, it isn't enough to just tell it 'default  
current_timestamp
    on update current_timestamp', you _also_ have to supress those  
settings
    in the first timestamp column by giving it an explicit default,  
like 'default 0'.
* You can't change the defaults after the table is created, ALTER TABLE
    throws an error if you try to alter it with current_timestamp.
* It seems like it would be a common thing to want to store both the  
time of
    creation, and the time of last update for a table, but you cannot  
do this:
	created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

And don't even get me started on the whole 'sql_mode=MAXDB' issue...   
If you set sql_mode=MAXDB and then create a table, any columns you  
specify as timestamp will be created as datetime.  But if you create  
the table first and then set sql_mode to MAXDB, then the columns will  
be created as timestamp columns, but behave like datetime...  Unless  
you specify a width when you create the column, in which case you  
still get a timestamp either way and it behaaves like a timestamp.

-- 
Jason Kohles
email at jasonkohles.com
http://www.jasonkohles.com/
"A witty saying proves nothing."  -- Voltaire