Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 28 '13 at 1:09

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 3 down vote accepted

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().

share|improve this answer
    
I was not aware of this column SET. Also your formula works! –  drum May 24 '13 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 '13 at 6:33
    
If I use SET, would I need 1 column per bit? –  drum May 24 '13 at 6:54
1  
No, it's just like you're doing. Read the documentation of mysql SET datatype –  Barmar May 24 '13 at 6:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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