1

I have a field which stores the days of the week in binary representation. For example, Mon-Wed-Fri would be represented as b'0101010' with the 1st digit as Sunday and the last as Saturday.

What I want to achieve: If today is Monday, I want to check if the Monday bit is set to 1. I do that by doing (b'0101010' & b'0100000' > 0). But I am struggling in converting the current date into the corresponding weekday binary representation. How can I achieve so?

1 Answer 1

3

The following expression will give the bit representation of today's day of week:

POW(2, 7 - DAYOFWEEK(NOW())
  • POW(2, n) raises 2 to the nth power, which is the same as shifting 1 n bits from the right.
  • DAYOFWEEK(date) returns the day of the week as a number, with Sunday = 1.
  • Since you have the low-numbered dates in the high-order bits, 7 - DOW converts that to Sunday = 6, Saturday = 0.

Wouldn't it be easier to declare the column as a SET, though? Then you could just use FIND_IN_SET().

4
  • I was not aware of this column SET. Also your formula works!
    – drum
    May 24, 2013 at 6:25
  • Sets are implemented as bit masks just like you're doing, but MySQL takes care of translating to and from strings for programming ease.
    – Barmar
    May 24, 2013 at 6:33
  • If I use SET, would I need 1 column per bit?
    – drum
    May 24, 2013 at 6:54
  • 1
    No, it's just like you're doing. Read the documentation of mysql SET datatype
    – Barmar
    May 24, 2013 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.