CODEWITHSUNDEEP
mysql
Sonali
Sonali

Reputation: 525

How to maintain different values for every hour of day in week

I want to add different rate(cost of hour) for different hours of day from Monday to Sunday in a mysql DB table.

I thought of 2 ways

  1. creating a column of every day(Week day) so that when I fetch a column I will get entire days rate in one column

  2. creating columns for every hour so that I have to fetch based on week day number(1 for Monday 2 for Tuesday). I will get rates of entire day in row

Which is good approach from above 2 or any other good approach for doing this ? Thanks

Upvotes: 0

Views: 46

Answers (1)

danblack
danblack

Reputation: 14666

Given the available MySQL time functions, WEEKDAY and HOUR are the easiest way to get to the values you need.

So a table like:

 CREATE TABLE rate (day TINYINT NOT NULL,
                    hour TINYINT NOT NULL,
                    rate FLOAT NOT NULL,
                    PRIMARY KEY (DAY, HOUR))

So a query could look like:

 SELECT rate
 FROM rate
 WHERE day=WEEKDAY(NOW())
       AND hour >= HOUR(NOW())
 ORDER BY day, hour LIMIT 1

This >= and ORDER/LIMIT just allows the table to have gaps of hours.

Upvotes: 1

Related Questions