I recently needed to validate that a value stored in a
TEXTcolumn was a valid
time zone identifier. Why? Because I was using its value inside the database to
convert timestamp columns from UTC to a valid zone. So I set about writing a
function I could use in a constraint.
It turns out that PostgreSQL has a pretty nice view that lists all of the time
zones that it recognizes. It’s called
try=# select * from pg_timezone_names limit 5; name | abbrev | utc_offset | is_dst --------------------+--------+------------+-------- Africa/Abidjan | GMT | 00:00:00 | f Africa/Accra | GMT | 00:00:00 | f Africa/Addis_Ababa | EAT | 03:00:00 | f Africa/Algiers | CET | 01:00:00 | f Africa/Asmara | EAT | 03:00:00 | f (5 rows)
Cool. So all I had to do was to look up the value in this view. My first stab at creating a time zone validation function therefore looked like this:
CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$ DECLARE bool BOOLEAN; BEGIN SELECT TRUE INTO bool FROM pg_timezone_names WHERE LOWER(name) = LOWER(tz) OR LOWER(abbrev) = LOWER(tz); RETURN FOUND; END; $$ language plpgsql STABLE;
This should pretty well cover anything that PostgreSQL considers valid. So does it work? You bet:
sandy_development=# \timing Timing is on. sandy_development=# select is_timezone('America/Los_Angeles'); is_timezone ------------- t (1 row) Time: 457.096 ms sandy_development=# select is_timezone('Foo/Bar'); is_timezone ------------- f (1 row) Time: 472.752 ms
Perfect! Well, except for just one thing: performance is abysmal. A half second
per shot? Not very useful for constraints. And since
pg_timezone_names is a
view (and, under that, a function), I can’t create indexes.
But then I did something dangerous: I started thinking. I realized that I needed this function when our app started getting errors like this:
try=# select now() at time zone 'Foo/Bar'; ERROR: time zone "Foo/Bar" not recognized
So the underlying C code throws an error when a time zone is invalid. What if I could just trap the error? Well, PL/pgSQL conveniently has exception handling, so I could do just that. But there was only one problem. PL/pgSQL’s exception handling syntax requires that you specify an error condition. Here’s what the documentation has:
EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements [ WHEN condition [ OR condition ... ] THEN handler_statements ... ] END;
Conditions are error codes. But which one corresponds to the invalid time zone
error? I tried a few, but couldn’t figure out which one. (Anyone know now to map
errors you see in
psql to the error codes listed in Appendix A? Let me know!)
But really, my function just needed to do one thing. Couldn’t I just trap any
A careful re-read of the PL/pgSQL documentation reveals that, yes, you can. Use the condition “OTHERS,” and you can catch almost anything. With this information in hand, I quickly wrote:
CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$ DECLARE date TIMESTAMPTZ; BEGIN date := now() AT TIME ZONE tz; RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END; $$ language plpgsql STABLE;
And how well does this one work?
sandy_development=# select is_timezone('America/Los_Angeles'); is_timezone ------------- t (1 row) Time: 3.009 ms sandy_development=# select is_timezone('Foo/Bar'); is_timezone ------------- f (1 row) Time: 1.224 ms
Yes, I’ll take 1-3 ms over 400-500 ms any day! I might even create a domain for this and be done with it:
CREATE DOMAIN timezone AS TEXT CHECK ( is_timezone( value ) );
Looking for the comments? Try the old layout.