MarketOrders/Issued trouble with datetime conversion for MySQL

EVE Technology and Research Center Third Party Developers
1

I am having an issue converting the ‘Issued’ value from the format given to datetime using MySQL.

Before I write a conversion I just wanted to make sure I am not overlooking something. It may be that I am using the wrong type for MySQL.

Currently I am trying to save it as a datetime variable, but that throws the following error:

SQLSTATE[22007]: Invalid datetime format:1292 Incorrect datetime value: '2020-01-28T16:59:49Z

This what ESI returns for the ‘issued’ column when requesting from marketorders:

"issued": "2020-01-28T16:59:49Z"

Am I using the wrong type or do I need to write a conversion?

2

Hi @Iggys
I had the same problem with the character birthday
Here is what I ended up doing, this converts it to UK/AU date format as well.
This is in python by the way
bdate= datetime.datetime.strptime(str(result['birthday']),'%Y-%m-%dT%H:%M:%SZ').strftime('%d/%m/%y %H:%M:%S')

3

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

Probably just need to ditch the “T” and the “Z”

4

Decode the field correctly. The format is a standard ISO 8601 time format.
MySQL (so is many other databases) do not accept it directly, but many languages you would use between API endpoint and database could parse it just fine.

5

Hey I figured I should respond to this since the issue has since been resolved.

If you’re having this problem all you have to do is run a bit of regex and get rid of the T and the Z in the returned datetime just like Covid Serine said.

Thanks all who replied!

6

“If you can’t solve something with regexp, you’re doing it wrong” ?

No, you don’t need regex here.

7

Agreed, regex is overkill. The position of the offending T and Z is known, and won’t change since we can see the date format coming from ESI zero pads all of the datetime components. Just use the known indices of the T and Z and replace the T with a space and delete the Z to conform to the MySQL DATETIME format.

8

Again, you don’t need “positions”. All you need is to correctly parse the input data and correctly present it in the output. Any relevant language you name has methods to parse ISO 8601 datetime format.

1 Like
9

Normally a fair statement, but since the OP didn’t specify any language maybe we shouldn’t assume that a “relevant” language is being used. What if he’s a CS undergrad doing a side project in C on a Windows box because its the only language he knows right now?

10

Of all the languages I use daily, only Windows CMD can’t deal with dates. For C, there’s standard libs that deal in dates and times.
My point being, you can’t apply string transformations to data that is not actually textual. Datetime is not. It only encoded as text, but to decode it properly, you need a lot of care.

11

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.