1. Home
  2. Forums Index
  3. Server Side / Databases 11:47 pm May 20, 2026

Forum Moderators: open

Message Too Old, No Replies

storing yes/no for every hour of every week?

sql guru assistance needed

         

mihomes

2:01 pm on May 17, 2014 (gmt 0)

10+ Year Member



Working on a project and have come across the situation where I need to store a yes/no value for every hour of every day for various 'filters' of users.

Here is my current setup :

filter_id | user_id | filter | type | day | 12a | 1a | 2a | ... and so on for each hour
1 | 1 | filter1 | 1 | 1 | 1 | 0 | 1 |
2 | 1 | filter1 | 1 | 2 | 1 | 0 | 1 |
3 | 1 | filter1 | 1 | 3 | 1 | 0 | 1 |
4 | 1 | filter1 | 1 | 4 | 1 | 0 | 1 |
5 | 1 | filter1 | 1 | 5 | 1 | 0 | 1 |
6 | 1 | filter1 | 1 | 6 | 1 | 0 | 1 |
7 | 1 | filter1 | 1 | 7 | 1 | 0 | 1 |
8 | 1 | filter2 | 1 | 1 | 1 | 0 | 1 |
9 | 1 | filter2 | 1 | 2 | 1 | 0 | 1 |
10 | 1 | filter2 | 1 | 3 | 1 | 0 | 1 |
11 | 1 | filter2 | 1 | 4 | 1 | 0 | 1 |
12 | 1 | filter2 | 1 | 5 | 1 | 0 | 1 |
13 | 1 | filter2 | 1 | 6 | 1 | 0 | 1 |
14 | 1 | filter2 | 1 | 7 | 1 | 0 | 1 |

Breakdown of the above would be there are two filters of type '1' for user '1' where they are 'active' for 12am and 2am each day of the week. Hopefully you get the gist.

user_id has a foreign relationship to my users table and I have setup a unique constraint for the group (user_id, filter, type, day).

With that said, there will always be 7 rows (one for each day) for every unique user_id/filter/type entry.

I feel there must be an easier way to do this! I recently just played around with outputting this data where I would like to show the 'schedules' for these filters and I end up joining the table on itself 7 times with aliases for every hour just to get the days 'combined' for each users filter. On simple testing I noticed this will be an issue with large numbers of entries.

Having a column for each hour of the week in one row is not an option of course as that would be 168 columns of hours in one row.

Certainly this must have come up with other people and/or there is an easier way. While searching I came across bitwise storing which seemed like a possibility here.

Ideas?

LifeinAsia

2:30 pm on May 17, 2014 (gmt 0)

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month



Can you give a sample of what the schedule would look like?

mihomes

5:55 pm on May 17, 2014 (gmt 0)

10+ Year Member



I did in the original post... each filter has a row each day of the week which has each hour of the day. The schedule is simply that... every hour of every day of the week... for each unique user_id/filter/type combination.

phranque

9:08 am on May 18, 2014 (gmt 0)

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month



Having a column for each hour of the week in one row is not an option of course as that would be 168 columns of hours in one row.

that number (168) isn't an issue per se.
Limits on Table Column Count and Row Size:
http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html [dev.mysql.com]

shouldn't this decision be based on your requirements?
if it makes more sense to have 168 hours per row and you don't need separate rows per day-of-week...

mihomes

2:50 pm on May 18, 2014 (gmt 0)

10+ Year Member



Yes, I actually came across that same information prior when I was doing research on this. Perhaps I should have rephrased it as not practical or common... I would think there would still be a performance issue with that many columns as well. On a side note if you stored all 168 hours you would not need to know the day - you already would with some simple logic (1-24 is Monday, etc)

To give further information here is a query I came up with the other say that satisfies my requirements of having all the info in one result row. Note the joins and aliases to make this happen. Now imagine 100 users and each has 50 filters (50x7 days)... I noticed the result took some time to populate which is leading me to find a 'best approach' to this. 

$stmt = $db->prepare("
SELECT users.user_id, users.username, filters.user_id, filters.filter, filters.type,
monday.12a as m12a,
monday.1a as m1a,
monday.2a as m2a,
monday.3a as m3a,
monday.4a as m4a,
monday.5a as m5a,
monday.6a as m6a,
monday.7a as m7a,
monday.8a as m8a,
monday.9a as m9a,
monday.10a as m10a,
monday.11a as m11a,
monday.12p as m12p,
monday.1p as m1p,
monday.2p as m2p,
monday.3p as m3p,
monday.4p as m4p,
monday.5p as m5p,
monday.6p as m6p,
monday.7p as m8p,
monday.9p as m9p,
monday.10p as m10p,
monday.11p as m11p,

tuesday.12a as t12a,
tuesday.1a as t1a,
tuesday.2a as t2a,
tuesday.3a as t3a,
tuesday.4a as t4a,
tuesday.5a as t5a,
tuesday.6a as t6a,
tuesday.7a as t7a,
tuesday.8a as t8a,
tuesday.9a as t9a,
tuesday.10a as t10a,
tuesday.11a as t11a,
tuesday.12p as t12p,
tuesday.1p as t1p,
tuesday.2p as t2p,
tuesday.3p as t3p,
tuesday.4p as t4p,
tuesday.5p as t5p,
tuesday.6p as t6p,
tuesday.7p as t8p,
tuesday.9p as t9p,
tuesday.10p as t10p,
tuesday.11p as t11p,

wednesday.12a as w12a,
wednesday.1a as w1a,
wednesday.2a as w2a,
wednesday.3a as w3a,
wednesday.4a as w4a,
wednesday.5a as w5a,
wednesday.6a as w6a,
wednesday.7a as w7a,
wednesday.8a as w8a,
wednesday.9a as w9a,
wednesday.10a as w10a,
wednesday.11a as w11a,
wednesday.12p as w12p,
wednesday.1p as w1p,
wednesday.2p as w2p,
wednesday.3p as w3p,
wednesday.4p as w4p,
wednesday.5p as w5p,
wednesday.6p as w6p,
wednesday.7p as w8p,
wednesday.9p as w9p,
wednesday.10p as w10p,
wednesday.11p as w11p,

thursday.12a as r12a,
thursday.1a as r1a,
thursday.2a as r2a,
thursday.3a as r3a,
thursday.4a as r4a,
thursday.5a as r5a,
thursday.6a as r6a,
thursday.7a as r7a,
thursday.8a as r8a,
thursday.9a as r9a,
thursday.10a as r10a,
thursday.11a as r11a,
thursday.12p as r12p,
thursday.1p as r1p,
thursday.2p as r2p,
thursday.3p as r3p,
thursday.4p as r4p,
thursday.5p as r5p,
thursday.6p as r6p,
thursday.7p as r8p,
thursday.9p as r9p,
thursday.10p as r10p,
thursday.11p as r11p,

friday.12a as f12a,
friday.1a as f1a,
friday.2a as f2a,
friday.3a as f3a,
friday.4a as f4a,
friday.5a as f5a,
friday.6a as f6a,
friday.7a as f7a,
friday.8a as f8a,
friday.9a as f9a,
friday.10a as f10a,
friday.11a as f11a,
friday.12p as f12p,
friday.1p as f1p,
friday.2p as f2p,
friday.3p as f3p,
friday.4p as f4p,
friday.5p as f5p,
friday.6p as f6p,
friday.7p as f8p,
friday.9p as f9p,
friday.10p as f10p,
friday.11p as f11p,

saturday.12a as s12a,
saturday.1a as s1a,
saturday.2a as s2a,
saturday.3a as s3a,
saturday.4a as s4a,
saturday.5a as s5a,
saturday.6a as s6a,
saturday.7a as s7a,
saturday.8a as s8a,
saturday.9a as s9a,
saturday.10a as s10a,
saturday.11a as s11a,
saturday.12p as s12p,
saturday.1p as s1p,
saturday.2p as s2p,
saturday.3p as s3p,
saturday.4p as s4p,
saturday.5p as s5p,
saturday.6p as s6p,
saturday.7p as s8p,
saturday.9p as s9p,
saturday.10p as s10p,
saturday.11p as s11p,

sunday.12a as s12a,
sunday.1a as s1a,
sunday.2a as s2a,
sunday.3a as s3a,
sunday.4a as s4a,
sunday.5a as s5a,
sunday.6a as s6a,
sunday.7a as s7a,
sunday.8a as s8a,
sunday.9a as s9a,
sunday.10a as s10a,
sunday.11a as s11a,
sunday.12p as s12p,
sunday.1p as s1p,
sunday.2p as s2p,
sunday.3p as s3p,
sunday.4p as s4p,
sunday.5p as s5p,
sunday.6p as s6p,
sunday.7p as s8p,
sunday.9p as s9p,
sunday.10p as s10p,
sunday.11p as s11p

FROM
users

LEFT JOIN filters
on filters.user_id = users.user_id

INNER JOIN filters as monday
on monday.user_id = filters.user_id and monday.filter = filters.filter and monday.day = 1

INNER JOIN filters as tuesday
on tuesday.user_id = filters.user_id and tuesday.filter = filters.filter and tuesday.day = 2

INNER JOIN filters as wednesday
on wednesday.user_id = filters.user_id and wednesday.filter = filters.filter and wednesday.day = 3

INNER JOIN filters as thursday
on thursday.user_id = filters.user_id and thursday.filter = filters.filter and thursday.day = 4

INNER JOIN filters as friday
on friday.user_id = filters.user_id and friday.filter = filters.filter and friday.day = 5

INNER JOIN filters as saturday
on saturday.user_id = filters.user_id and saturday.filter = filters.filter and saturday.day = 6

INNER JOIN filters as sunday
on sunday.user_id = filters.user_id and sunday.filter = filters.filter and sunday.day = 7


WHERE filters.type = 1
GROUP BY filters.filter
");


Off the top of my head I am thinking one column stored as 0010110... and so on for each hour. varchar(168)

I could then do something like this on the processing side of things while only storing one column in the database. Note I did not use 0/1 in the original string for sake of explaining position. This is the best method I could come up with. Working with it should be easy and all queries, inserts, updates would be simple as well.

Opinions? 

$foo="123456789";
echo $foo[0];//1
echo $foo[1];//2

$foo[0] = 1;
$foo[1] = 1;
echo $foo[0];//1
echo $foo[1];//2

LifeinAsia

3:12 pm on May 18, 2014 (gmt 0)

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month



outputting this data where I would like to show the 'schedules'

This output is what I meant when I asked you to show the schedule.

There are a number of ways you can store the data. But the way you want to retrieve/present it can suggest which ways may be more efficient.

As Phranque says, putting all the columns in one row is going to be more efficient that joining a table on itself multiple times.

If all you're doing is simple lookups for the entire day, using a 168-char field would also work. But if you start searching for more discrete data (e.g., specific hours of the day), it's a little more complicated and the queries will take longer (searching on integers is faster than searching on character strings).

mihomes

7:14 pm on May 18, 2014 (gmt 0)

10+ Year Member



At this time I would only be displaying the values and updating them which is why I thought of the 168 char idea. I honestly didn't think about queries on the db side of things for specific hours, but can't see why I would need that - at least at this time.

Assuming I did want to query specific hours and that I did want to use int(1) for all those hours... are you saying you would recommend storing everything in one row... all 168 hours plus the few others used I currently use? To be honest I have not tried it, but it just seemed like it would be a performance issue returning that many per row. Say my query returns a hundred 'results' then that would be 168 + 4 additional or 172000 column values. Sounds like that wouldn't be to 'fast' to return although as you stated there would be a lot less joins involved.

I'd really be interested in opinions... I have used MySQL in the past, but things like this are completely new to me.

penders

4:31 pm on May 24, 2014 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



tl;dr (sorry)... So may have missed the point...

Are most hours set or not set?

Gut feeling is that you should only be storing the hours that are "set". ie. a datetime field for that hour. (?)

brotherhood of LAN

5:14 pm on May 24, 2014 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



>At this time I would only be displaying the values and updating them which is why I thought of the 168 char idea.

Alternatively you can use 21 bytes and just use the bits as hours. 24 * 7 = 168 bits = 21 bytes. I've used this on data that only periodically gets queried (i.e. a users preferred hours of being contacted, run daily). You can then use server/client side to read the bits/bytes when necessary. I have some PHP (or Javascript, can't remember) that can do this for you.

If it were data you constantly queried for in WHERE statements it'd probably deserve its own columns, to avoid function calls on every row like SUBSTRING().

Other alternatives, store day of week (1 byte) and hours of day as a 3 byte bitset ( 7 rows max)... or simply store "hour of week" as a 1 byte tinyint (168 rows max) .
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 11:47 pm May 20, 2026