Please add regex for datetime data type search

r5057 introduces a regular expression per data type. I added expressions for UUID and some simple ones for INT, SMALLINT and BIGINT. The condition is left away if the value does not match the expression of the relevant data types, just like discussed above.

I can now easily extend this feature for other data types which throw SQL errors when the user enters a non-matching value. I'm not sure on which data types that makes also sense.

Can we also include one for DATETIME values:

//-- Complete precision:
/\d{4}-[01]\d-[0-3]\dT[0-2]\d:[0-5]\d:[0-5]\d\.\d+([+-][0-2]\d:[0-5]\d|Z)/

//-- No milliseconds:
/\d{4}-[01]\d-[0-3]\dT[0-2]\d:[0-5]\d:[0-5]\d([+-][0-2]\d:[0-5]\d|Z)/

//-- No Seconds:
/\d{4}-[01]\d-[0-3]\dT[0-2]\d:[0-5]\d([+-][0-2]\d:[0-5]\d|Z)/

//-- Putting it all together:
/(\d{4}-[01]\d-[0-3]\dT[0-2]\d:[0-5]\d:[0-5]\d\.\d+([+-][0-2]\d:[0-5]\d|Z))|(\d{4}-[01]\d-[0-3]\dT[0-2]\d:[0-5]\d:[0-5]\d([+-][0-2]\d:[0-5]\d|Z))|(\d{4}-[01]\d-[0-3]\dT[0-2]\d:[0-5]\d([+-][0-2]\d:[0-5]\d|Z))/

source: http://stackoverflow.com/questions/3143070/javascript-regex-iso-datetime

Thanks, Ben

You are referring to ISO dates with a "T" in the middle, and probably "Z" at the end. Is that syntax correct for PostgreSQL types DATE, DATETIME, TIME, TIMESTAMP and INTERVAL?

Yes for DATE, DATETIME, TIME, TIMESTAMP. It is not the case for INTERVAL.

The T and Z are accepted, though optional, per https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE and I also tested both forms myself. The query works a space instead of the T. So I guess the T in the regex could be replaced with (T|\s) i.e.

/\d{4}-[01]\d-[0-3]\d(T|\s)[0-2]\d:[0-5]\d:[0-5]\d([+-][0-2]\d:[0-5]\d|Z)/

The time component is also optional. So this becomes:

/\d{4}-[01]\d-[0-3]\d((T|\s)[0-2]\d:[0-5]\d:[0-5]\d([+-][0-2]\d:[0-5]\d|Z))/

PostgreSQL also accepts many different formats however I think the simplest thing is to just have one accepted regex for date and date+time.

Example Description
1999-01-08  ISO 8601; January 8 in any mode (recommended format)
January 8, 1999 unambiguous in any datestyle input mode
1/8/1999    January 8 in MDY mode; August 1 in DMY mode
1/18/1999   January 18 in MDY mode; rejected in other modes
01/02/03    January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode
1999-Jan-08 January 8 in any mode
Jan-08-1999 January 8 in any mode
08-Jan-1999 January 8 in any mode
99-Jan-08   January 8 in YMD mode, else error
08-Jan-99   January 8, except error in YMD mode
Jan-08-99   January 8, except error in YMD mode
19990108    ISO 8601; January 8, 1999 in any mode
990108  ISO 8601; January 8, 1999 in any mode
1999.008    year and day of year
J2451187    Julian date
January 8, 99 BC    year 99 BC