Time being dropped when updating a datetime column

obrienj

I have a curious problem that has caused me to delve into the Editor PHP source.

I have an editor field defined as "datetime" and well as "datetime" in the MySql database

In the database CREATE statement it is defined as:

 start DATETIME, 

and it is defined in my page DataTables Editor definition as:

                  {
                       label: "Start:",
                       name: "start",
                       type: "datetime",
                       format: 'dddd, MMMM D, YYYY h:mm A'
                   },

and it is defined in my Editor PHP as follows:

          Field::inst( 'start' )

          ->validator( 'Validate::dateFormat', array(
                                           "format"  => 'l, F j, Y g:i A',
                                           "message" => "Please enter a date in the correct format"
            ) )

            ->getFormatter( 'Format::date_sql_to_format', 'l, F j, Y g:i A')

            ->setFormatter( 'Format::date_format_to_sql', 'l, F j, Y g:i A')

In the editor page field, the date, which came from a MySql DB created elsewhere, shows as:

Friday, May 26, 2017 9:30 AM

However, if I do an "Update" on the Editor page, the data in the respective DB column changes to:

2017-05-26 00:00:00

from

2017-05-26 09:30:00

and in the Datatables tabular list it shows as

Friday, May 26, 2017 12:00 AM

I climbed into Format.php and in:

    public static function date_format_to_sql( $val, $data, $opts ) {

        // Note that this assumes the date is in the correct format (should be
        // checked by validation before being used here!)
           $date = date_create_from_format($opts, $val);

        // Invalid dates or empty string are replaced with null. Use the
        // validation to ensure the date given is valid if you don't want this!
        if ( $val && $date ) {
            return date_format( $date, 'Y-m-d' );
        }
        return null;
    }

Line 8

return date_format( $date, 'Y-m-d' )

is curious in that it would seem to remove the date.

Is this a bug or am I missing something?

And it raises the question, why do validator and getFormatter seem to work or do they also have an error?

Note, that is the problem determination process, I logged the incoming date and it was correct.

Jim

allan

Hi Jim,

I fear that this isn't a bug, but rather the wrong method is being used for the formatter. Instead of using the date_sql_to_format or date_format_to_sql methods, use datetime.

That will retain the time information. The two date_* methods are designed specifically only for the date part.

Allan

obrienj

Hi Allan,

Thanks for the input.

I figured it out at just about the same time, all is well now.

In fact, there is a Formatters example that shows this.

Regards,
Jim