Compare weekday

[Anon]

Dear all,

I would like to compare weekdays ( Monday, Tuesday,...., which are stored in the mysql database. For example, select * from data where weekday <"Tuesday", then, is the data on "Sunday" included? Please give us reply, thanks

Sincerely yours,
Simon

[bfranco]

you should define the field where you're storing the weekday as an enumeration:

CREATE TABLE data (
id INTEGER NOT NULL PRIMARY KEY auto_increment,
weekday ENUM("sunday", "monday", "tuesday", "wednesday", "thursday", "friday", "saturday")
);

you can read more bout enums at http://www.mysql.com/doc/E/N/ENUM.html

[[deleted]]

It depends entierly on how you store the weekday in the first place.
So how do you store the weekday in the table?

[bfranco]

don't understand what you mean, but here is an example bout how to use it.

mysql>INSERT INTO data (weekday) VALUES ('sunday');
Query OK, 1 row affected (0.00 sec)

mysql>INSERT INTO data (weekday) VALUES ('friday');
Query OK, 1 row affected (0.00 sec)

mysql>INSERT INTO data (weekday) VALUES ('monday');
Query OK, 1 row affected (0.00 sec)

mysql>INSERT INTO data (weekday) VALUES ('wednesday');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM data WHERE weekday>=4;
+----+-----------+
| id | weekday |
+----+-----------+
| 2 | friday |
| 4 | wednesday |
+----+-----------+
2 rows in set (0.00 sec)
mysql>

[[deleted]]

I was asking Simon how he is currently storing the weekday.

There are two 'major' drawbacks to using enums:
1- they are a mysql thing.
2- if you redefine the enum in the table structure, will either mess up the results or lose data.

I'd propose to just store the weekday as an integer (which is really what enum does anyway) and use numbers in the query too (with constants if need be) That way you are compatible with all databases, plus you can't mess up your data should the database model change (and we all know that it will)

BTW you can always just grab the weekday from a complete date field too.

[bfranco]

yeah, you're right.

using an integer field and a check constraint to be sure that the weekday is in the range 0-6 is a good one.

[[deleted]]

A contraint is good, but on the other hand I don't think you'll find many dates that fall on the 8th day of the week. 🙂