Date and DateTime issue

1

Hi,

I have a form field that asks for a date - day, month and year.

Later, I need to compare this date with another native PHP DateTime object in my business rules.

For the life of me, I can’t figure out how to do it.

  • If I create a migration with a date column, the field is automatically mapped to a `Cake/l18n/Date` object, which cannot be directly compared with a PHP DateTime instance. I rather not write `toNative()` everywhere and generally try to keep all framework logic out of my business rules.
  • If I create a migration with a dateTime column and a dateTime validator, but a form control that only asks for the date, then the validator will either complain about the format, or it will delete the input altogether, leading to a NOT NULL constraint error on DB level.
  • Another reason why `Cake/l18n/Date` doesn’t really work for me is that I need to pass it to vendor libraries which expect a `DateTimeInterface` implementation.

I am sure there are good reasons for CakePHP to insist on its own Date implementation, but it doesn’t really work with my requirements here.

My approach would be to only ask for the date in the form:

$this->Form->control(‘invoice_date’, [‘type’ => ‘date’, ‘label’ => __(‘Invoice Date’)])

And save it as a Postgres `Date` column:

$table->addColumn('invoice_date', 'date', [ 'null' => false ])

or as a “Timestamp without time zone”:

$table->addColumn('invoice_date', 'datetime', [ 'null' => false ])

When querying the DB, I want to receive a DateTimeInterface object - something I can work with native PHP DateTime methods and operators. Anyone care to explain to me how this could be solved?

Thanks

2

Maybe store as datetime, use date-only form control. CakePHP will set time to midnight automatically:

// Migration
$table->addColumn(‘invoice_date’, ‘datetime’, [‘null’ => false]);

// Form - still only asks for date
$this->Form->control(‘invoice_date’, [‘type’ => ‘date’]);

This gives you Cake\I18n\DateTime which implements DateTimeInterface - you can pass it directly to vendor libraries and compare with native DateTime.

3

Okay, in the table class, what kind of validator would I have to use with this?

If I use a date validator, the field seems to be removed from the input data after submission, leading to a NOT NULL constraint error at DB level.

$validator->date('invoice_date')

If I use a datetime validator instead, I get the following form validation error:

The provided value must be a date and time of one of these formats: `ymd`

This didnt do the trick either:

$validator->dateTime('invoice_date', ['Y-m-d'])

The error stays the same.

Edit: the only way I could make this work is to explicitly convert date strings to datetime in AppTable:

public function beforeMarshal(EventInterface $event, ArrayObject $data, ArrayObject $options): void
{
    foreach (array_keys((array)$data) as $field) {
        if (
            str_ends_with($field, '_date')
            && isset($data[$field])
            && is_string($data[$field])
            && preg_match('/^\d{4}-\d{2}-\d{2}$/', $data[$field])
        ) {
            $data[$field] = $data[$field] . ' 00:00:00';
        }
    }
}
4

The workaround in beforeMarshal is actually a reasonable approach if you need datetime columns with date-only input.

I would probably be a bit more simple and clear on the fields itself, instead of iterating over all (if the number is limited):

  if (!empty($data['invoice_date']) && is_string($data['invoice_date'])) {                                     
      $data['invoice_date'] .= ' 00:00:00';                                                                    
  }
1 Like