PHP preg_match mysql时间数据类型，格式为xxx：xx：xx

Question

Looking for the correct preg_match (or other solution) to validate a string to go in mysql time datatype. The format is xxx:xx:xx and between ranges '-838:59:59' to '838:59:59'

Also in mind that "7:53:48", "23:59", "145:19:59", and "-16:24:33" are all valid inputs but "930:00:00", 128:61:61" and "63:59" are all invalid.

I've got this far from a normal time preg_match

preg_match("/([0-9]):([0-5][0-9]):([0-5][0-9])$/", $time)

But this only validates the minutes and seconds properly. Any ideas !?

Also note that I understand that time mysql datatype also accepts values without the colons, but that's been handled separately already ;)

EDIT

I'm really sorry guys, missunderstood mysql doc. 70:59 is actually valid input, it will be translated to 70:59:00

Answer 1

(?<![0-9:])-?([0-9]|[0-9][0-9]|[0-7][0-9][0-9]|8[0-2][0-9]|83[0-8]):([0-5][0-9])(?::([0-5][0-9]))?(?![0-9:])

Explanation:

(?<!                  # look-behind: makes sure we are at the start of a time
  [0-9:]              #   ...not preceded by a digit or a colon
)                     # end look-behind
-?                    # a minus, optional
(                     # group 1 (hours)
   [0-9]              #   single-digit 0-9
   |                  #   or
   [0-9][0-9]         #   double-digit 00-99
   |                  #   or
   [1-7][0-9][0-9]    #   triple-digit 100-799
   |                  #   or
   8[0-2][0-9]        #   triple-digit 800-829
   |                  #   or
   83[0-8]            #   triple-digit 830-838
)                     # end group 1
:                     # :
(                     # group 2 (minutes)
   [0-5][0-9]         #   00-59
)                     # end group 2
(?:                   # non-capturing group
  :                   #   :
  (                   #   group 3 (seconds)
    [0-5][0-9]        #     00-59
  )                   #   end group 3
)?                    # end group, make optional
(?!                   # look-ahead: makes sure we are at the end of a time
  [0-9:]              #   ...not followed by a digit or a colon
)                     # end look-ahead

This actually matches time strings in a larger text, instead of just validating them.