Joomla 3.9 replace zero dates with null on save/checkin (mysql zero date deprecated)

Question

So zero dates are deprecated in MySQL. J4 has replaced them with nulls.I am trying to update a J3 component to stop J setting zeros in the modified and checked_out_time fields.

For modified I am using table prepareTable() to set an empty modified date from the form to NULL for saving and table getItem() to replace nulls in modified with a zero date to keep the calendar control happy.

The problem is with checked_out_time which is set back to zero by joomla in a separate query when the item is closed (ie after save).

How can I override this to force Joomla to save a valid for MySQL date?

Answer 1

With thanks to @glenn-arkell who pointed me in the right direction, for j3.9 this works to stop Joomla writing '0000-00-00 00:00:00' into the checked_out_time field. For other fields (eg modified and any of your own datetime fields) use bind() or prepareTable() and getItem() to set empty dates from a form control to null and null dates back to zero for use in a form control.

In your table class add the following function (copied from the parent table class and modified to write nulls for the checked_out_time field. The parent class is replaced.

    public function checkIn($pk = null) {
    $checkedOutField = $this->getColumnAlias('checked_out');
    $checkedOutTimeField = $this->getColumnAlias('checked_out_time');

    // If there is no checked_out or checked_out_time field, just return true.
    if (!property_exists($this, $checkedOutField) || !property_exists($this, $checkedOutTimeField))
    {
        return true;
    }

    if (is_null($pk))
    {
        $pk = array();

        foreach ($this->_tbl_keys as $key)
        {
            $pk[$this->$key] = $this->$key;
        }
    }
    elseif (!is_array($pk))
    {
        $pk = array($this->_tbl_key => $pk);
    }

    foreach ($this->_tbl_keys as $key)
    {
        $pk[$key] = empty($pk[$key]) ? $this->$key : $pk[$key];

        if ($pk[$key] === null)
        {
            throw new \UnexpectedValueException('Null primary key not allowed.');
        }
    }

    // Check the row in by primary key.
    $query = $this->_db->getQuery(true)
        ->update($this->_tbl)
        ->set($this->_db->quoteName($checkedOutField) . ' = 0' )
        ->set($this->_db->quoteName($checkedOutTimeField) . ' = NULL' );
    parent::appendPrimaryKeys($query, $pk);
    $this->_db->setQuery($query);

    // Check for a database error.
    $this->_db->execute();

    // Set table values in the object.
    $this->$checkedOutField     =  0;
    //we are putting an empty, not null, value here for compatibility with anything else using onAfterCheckin.
    $this->$checkedOutTimeField =  '';

    $dispatcher = \JEventDispatcher::getInstance();
    $dispatcher->trigger('onAfterCheckin', array($this->_tbl));

    return true;
}

This seems to work ok for getting rid of those pesky '0000-00-00 00:00:00' values in the checked_out_time field in the database and stops mysql throwing warnings and allows phpmyadmin to work without having to change the Strict mode.

Answer 2

Some added suggestions around null dates - I came upon the need to remove a date from a datetime field using the standard Table from the Model class. This makes the removal of a date nice and clean. This may be obvious to some.

$updateNulls = true;
$table = $this->getTable();
$table->load($id);

$table->proved_date = NULL;

$table->store($updateNulls);

Answer 3

A further add to this regarding comparing dates with nulls.

If a datetime can be set to null or a valid value and you want to update it if the new value is later (greater than) the existing value (null or a valid date) then you need to be able to compare the new date with null - which doesn't work.

So you can use the COALESCE() mysql function to replace the null with a valid value.

But since zero is no longer a valid date you can't use COALESCE(a.mydate,0) as I found suggested elsewhere on t'internet.

The minimum valid MySQL date is '1000-01-01 00:00:00' so my solution is this:

Assuming 'mytable' has a column 'last_seen' which I want to update a row with id = $id with a '$newdate' if $newdate is greater than the existing value in a.last-Seen. Assume newdate is a string as might be returned from a calendar form control.

$query = 'UPDATE `#__mytable`  AS a 
  SET `last_seen` =  '.$db->quote($newdate).'
  WHERE a.id  = $id 
  AND COALESCE(a.last_seen, STR_TO_DATE("1000-01-01","%Y-%m-%d"))
    < STR_TO_DATE("'.$newdate.'","%Y-%m-%d")';

In this case I'm not checking the time part. There may be a better way...