RegEx for PostgreSQL 'interval' function

user_9886447 0
Viewed 213

I need to implement regex validation for value that will be used in my server side to get data where certain timestamp is older (smaller) than now() - interval 'myValue'.

pSQL interval function is explained here, and in short it can have values like 2 days,3 years,12 hours, but also you can nest more different values like 2 days 6 hours 30 minutes etc.

I currently have a regex /^\d+\s(seconds?|minutes?|hours?|days?|weeks?|months?|years?)$/i that accepts only one value (e.g. 2 days), but can't figure out how to allow multiple values, and set a rule that a certain string from this group can only be repeated once or not at all.

This regex /^\d+\s(seconds?|minutes?|hours?|days?|weeks?|months?|years?)(\s\d+\s(seconds?|minutes?|hours?|days?|weeks?|months?|years?))*$/i allows nesting but also allows repetition of values e.g. 2 days 12 hours 6 hours 2 minutes which will result in a fatal error in pSQL query.

I tried restricting repetition of values in this group with \1 and {0,1} combination of regex operators but I just can't nail it precisely enough.

NOTE: Regex is unfortunately only way I can validate this value, since I don't have access to server-side controller which receives this value nor do I have access to client-side frontend of this form. I can't just throw exceptions or skip query because it is a part of important cron-job, and must be stable at all time.

(All I have access to is json schema of this value, and therefore can only define regex pattern for it)

Any help is appreciated, thanks.

postgresql regex
2 Answers

You can use

^(?!.*(second|minute|hour|day|week|month|year).*\1)\d+\s+(?:second|minute|hour|day|week|month|year)s?(?:\s+\d+\s+(?:second|minute|hour|day|week|month|year)s?)*$

See the regex demo

Details

  • ^ - start of string
  • (?!.*(second|minute|hour|day|week|month|year).*\1) - no second, minute, hour day, week, monthoryear` string repetition allowed in the whole string
  • \d+\s+(?:second|minute|hour|day|week|month|year)s? - 1 or more digits, one or more whitespaces, then either second, minute, hour, day, week, month or year, and then an optional s letter
  • (?:\s+\d+\s+(?:second|minute|hour|day|week|month|year)s?)* - zero or more repetition of one or more whitespaces followed with the pattern described above
  • $ - end of string.
user_38329700

Forget it. The only complete documentation of the supported values for interval is the implementation (the guts are in ParseDateTime).

Consider these:

SELECT INTERVAL '12 00:12:00';

     interval     
══════════════════
 12 days 00:12:00
(1 row)

SELECT INTERVAL '12 d 12 mins';

     interval     
══════════════════
 12 days 00:12:00
(1 row)

SELECT INTERVAL '3-2';

    interval    
════════════════
 3 years 2 mons
(1 row)

What I would do in your place is to write a function that casts the string to interval and catches and reports an error:

CREATE FUNCTION interval_ok(text) RETURNS boolean
   LANGUAGE plpgsql AS
$$BEGIN
   PERFORM CAST ($1 AS interval);
   RETURN TRUE;
EXCEPTION
   WHEN invalid_datetime_format THEN
      RETURN FALSE;
END;$$;
user_64643080
Related