Problem with german date format

Hello,

i have problems with german date format and postgresql.

this is part of json request to the API: trainingsdate: "21.02.2019"

Validation in the controller:


        $this->validate($request, [
            'art' => 'required|max:1|min:1',
            'trainingsdate' => 'required|unique:trainings|date|date_format:d.m.Y'
        ]);

and save the data to the database:

$training = new Training([
            'trainingsdate' => $request->get('trainingsdate'),
            'art' => $request->get('art'),
          ]);
        $training->save();

I've got an error:

SQLSTATE[22008]: Datetime field overflow: 7 ERROR: date/time field value out of range: "21.02.2019"

My timezone is set it to Europe/Zurich How can i use the german date format (dd.mm.yyyy) in lumen?

Cronix

7 years ago

Level 67

Convert it to a date format that postgres uses before saving to the db. d.m.Y isn't a valid format for postgres. https://www.postgresql.org/docs/9.1/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE

latz

7 years ago

Level 3

Thanks. I tried to convert the timestamp

$trainingsdate = Carbon::createFromFormat('d.m.Y', $request->get('trainingsdate'))->toDateTimeString();
print_r($trainingsdate);

I've got an error, too. The error is probably due to the validation.

'trainingsdate' => 'required|unique:trainings|date|date_format:d.m.Y'

The unique field is the problem?

Cronix

7 years ago

Level 67

The unique rule wouldn't have to do with an error inserting data in the db. You'd just get a normal validation error stating that xyz field needs to be unique on the form when displaying validation errors and it shouldn't let you insert in the db until it passes validation.

It's probably because you converted it to a toDateTimeString(). It should just be toDateString() if it's a date column. No time portion.

latz

7 years ago

Level 3

After i remove the part of the validation: unique:trainings it works.

With the unique settings I've got the error;

Perhaps you need a different "datestyle" setting. (SQL: select count(*) as aggregate from "trainings" where "trainingsdate" = 30.12.2019

Lumen takes the date in the request array. this is the "wrong" german date. Do I understand this wrong?

Two sql querys:


select count(*) as aggregate from "trainings" where "trainingsdate" = '2019-12-30'

select count(*) as aggregate from "trainings" where "trainingsdate" = '30.12.2019'

The first works. The second not.

Cronix

7 years ago

Level 67

Ah, yes you're correct. When you said you "got an error too" I thought it was the identical error. You'd want to use the Unique class rule and pass a custom where to it, and convert the date value to a format that your database uses (Y-m-d)

Scroll down to "Adding Additional Where Clauses:" https://laravel.com/docs/5.8/validation#rule-unique

It would be a lot easier if you were passing the date in a more appropriate format from the frontend instead of d.m.Y.

Please or to participate in this conversation.