MySQL Bugs: #82375: Function to parse ISO8601 timestamp

Bug #82375	Function to parse ISO8601 timestamp
Submitted:	28 Jul 2016 16:05	Modified:	1 Aug 2016 6:44
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S4 (Feature request)
Version:	5.7.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, iso, iso8601, STANDARDS, timestamp

Description:
Please add a function to parse a ISO 8601 timestamp string.
Many applications who export JSON use this. MySQL might need to parse this from a JSON column...

This SQL Function mostly works:
CREATE FUNCTION from_iso8601_txt (s CHAR(35))
RETURNS DATETIME(6)
SQL SECURITY INVOKER
COMMENT 'Parse iso8601 string'
DETERMINISTIC
LANGUAGE SQL
RETURN CONVERT_TZ(STR_TO_DATE(LEFT(s,26), '%Y-%m-%dT%H:%i:%S.%f'), RIGHT(s,6), @@global.time_zone);

Some problems
- MySQL expects microseconds to be 6 digits, but I've seen data with 9 digits. That's difficult to parse in a format string.
- I have to split the string for timezone and datetime data, There is no option to ignore the noise in the end and just return the string.
- generated columns don't allow stored functions
- generated columns don't allow convert_tz()

Related:
Bug #27906 	MySQL doesn't accept ISO8601 date

How to repeat:
See description

Suggested fix:
- Add format string for 9 digits microseconds
- Add format string for 'ignore random character(s)'
- Create C function in the server to do what the SQL fuction from the description does.

Hello Daniël,

Thank you for the feature request!

Thanks,
Umesh

I'm not sure UDF is the right category as UDF's are not allowed for generated columns. If possible this new function should be build-in so it can be used with generated columns.