Support ISO 8601 timezoned datetime format

[JeremyKeusters]

Hi,

First of all, thanks for this awesome project. I'm using this nice and simple PHP CRUD for the back-end of an Angular Web App, together with a MYSQL database.

The issue I'm currently having is that when I'm trying to POST a new object with a date(time) type, it throws an error. SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-09-01T13:41:41.191Z' for column 'start_date' at row 1". I did some research and discovered that MYSQL expects a format like Y-m-d H:i:s for a datetime type.

I would prefer to solve this issue on PHP/back-end side instead of trying to fix it in Angular. Is there any quick/ready solution to his, or do I need to manually dig into the code and try to fix this?

Thanks! Jérémy

[mevdschee]

Thank you for your kind words. I think this is a bug. Let me try to help you.

Which version are you using? Are you trying to save the date in the format the API returns?

[JeremyKeusters]

Hi @mevdschee ! Thanks for your quick answer.

I just took the master (V2). I'm posting an object via http.post() in Angular. This post request is performed to your PHP REST.

Here's the object's class:

export class Shift {
    id: number;
    daily_financial_statement_id: number;
    employee_id: number;
    start_date: Date;
    end_date: Date;
}

So the date just gets saved in Angular as a native Angular Date type. Then it's posted to the PHP CRUD via:

this.http.post<any>(this.endpoint + 'shifts', JSON.stringify(shift), this.httpOptions).pipe(
      tap((shift) => console.log(`added product w/ id=${shift}`))

The result of the JSON.stringify(shift) is the following: {"employee_id":1,"daily_financial_statement_id":2,"start_date":"2018-09-01T19:40:22.236Z"}

This means that it's not saved in the format that the API returns. This isn't actually possible since it's a POST request and in theory there's no existing object to rely on. However, on a GET request, I receive it in the following format: "2018-08-28 17:00:00" (Which is the MYSQL preferred mandatory format)

[mevdschee]

Well.. as a workaround you could use:

shift.start_date = shift.start_date.toISOString().substring(0, 19).replace('T', ' ');

and I will investigate whether or not this is a regression compared to v1.

[JeremyKeusters]

Well, this would make things quite complicated. My start_date object is from type date, so I can't save a string into it. If I would do this, I would need to change the type to String and handle it everywhere in my web app as a string, which is far from ideal. Another solution would be to separate/split the JSON.stringify'ed string and then execute your code on it, but this makes it also unnecessary complex.

It would just be nice if there was an option for 'SQL Date' support or something like that in the PHP CRUD API which receives a date in whatever format and formats it correctly for a SQL database.

[mevdschee]

The input and output of the API is in JSON and JSON does not support date types. JavaScript stores absolute datetime while SQL defaults to relative datetime (to the current timezone). This complicates matters. I could support the ISO 8601 date format, but then I would have to read the column timezone and convert to it. Ignoring the timezone part seems wrong to me. What are your thoughts about this?

[JeremyKeusters]

Good point! I'm trying to get insights into how other frameworks solve this issue.. Can't imagine I'm the first person that is being restrained by this limitation/issue.

[JeremyKeusters]

Apparently, I was wrong. It is actually possible to save the string you generate with this.inputModel.start_date.toISOString().substring(0,19).replace('T', ' ') in a Date object in Angular. This workaround fixes my issue, so let's just blame it on the fact that date types make everything too complicated 😛 . All working now!

[mevdschee]

Good that you got it working. Well it is a workaround and not a pretty one. Note that if you do this, that you are converting absolute time (Z means Zulu or +0:00) to relative time.

[mevdschee]

Converted to feature request for future reference.