Regular expression to match MySQL timestamp format "Y-M-D H:M:S"

Question

I am trying to create a regular expression to match a string holding a date in the MySQL timestamp format, ex "2012-07-16 02:04:33".

It is not as easy as it sounds, e.g. you should not end up with February 30th.

I know there are easier ways to do this, but I am depending on being able to pass a string and a regular expression to evaluate that string.

I would be glad for any suggestions.

Answer 1

The regular expression:

/^(((\d{4})(-)(0[13578]|10|12)(-)(0[1-9]|[12][0-9]|3[01]))|((\d{4})(-)(0[469]|1‌​1)(-)([0][1-9]|[12][0-9]|30))|((\d{4})(-)(02)(-)(0[1-9]|1[0-9]|2[0-8]))|(([02468]‌​[048]00)(-)(02)(-)(29))|(([13579][26]00)(-)(02)(-)(29))|(([0-9][0-9][0][48])(-)(0‌​2)(-)(29))|(([0-9][0-9][2468][048])(-)(02)(-)(29))|(([0-9][0-9][13579][26])(-)(02‌​)(-)(29)))(\s([0-1][0-9]|2[0-4]):([0-5][0-9]):([0-5][0-9]))$/

does the job. Thanks for the input.

Answer 2

You should do it in 2 steps, instead of trying to use really complicated regexes.

Step1: Check if the format is right using regex. Use something like

/^\d\d\d\d-(\d)?\d-(\d)?\d \d\d:\d\d:\d\d$/g

Step2: If it is a match, use something similar to strtotime() in PHP (or parse using date-time functions in whichever language you are using) and check if the result is valid, to eliminate dates like February 30th.