Perfect hash function for weekday names

This is probably only of limited usefulness in practice, but it came as a spinoff from a discussion on #postgresql. The basic problem is, given a string which you already know is an English weekday name, return the day number (Sunday=0):

create function dayno(text)
  returns integer
  language sql immutable
  as $f$
    select (( ((ascii(substring($1 from 3)) & 22)*10)
              # (ascii($1) & 23) )*5 + 3) % 7;
$f$;

I came up with several alternative functions, but this one has the advantage of only needing one substring() call (it makes use of the fact that ascii() looks only at the first character). It’s also case-insensitive (since it looks at only the low bits of the characters).

select dayno(d)
  from regexp_split_to_table('Sun,MON,tUe,WeD,tHU,FRi,saT',',') r(d);
 dayno 
-------
     0
     1
     2
     3
     4
     5
     6
(7 rows)

This entry was posted in PlanetPG, Postgres, SQL. Bookmark the permalink.