You can use the dayname(timestamp) or dayofweek(timestamp) function.

e.g.,  select dayname(current_date) from blah will give you "Tuesday".
        select dayofweek(current_date) from blah will give you 3.