CODEWITHSUNDEEP
phpmysqlsqldatetime
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Storing times - use Time or Int field? (PHP & mySQL Database)

Consider the SQL query below:

SELECT * FROM opening_hours  
        WHERE week_day = WEEKDAY(NOW()) + 1 
              AND open_hour =< date_format(now(),'%H:%i') 
              AND close_hour >= date_format(now(),'%H:%i')

open_hour / close_hour fields are TIME type.

I am using MySQL.

Suppose that open_time is "18:00", close_time is "02:00", current time is "22:41". We have a separate DB record for the close_time (cause it's after midnight), but we will never get it in the result, because the close_time "02:00" is NOT greater than the current time, "22:41".

Also if current time is "01:00", we will get the NEXT day values, because the weekday doesn't match.

Solution?

Would you rather store these values in integer (minutes), so the php is able to process these values directly, without any conversion?

For example...

Current time:

$timearr = explode(':',date("w:H:i"));
$currenttime = ($timearr[0]) * 1440 + $timearr[1] * 60 + $timearr[2]

Minimal value of current time = 0 (Sun, 00:00), max value is 10079 (Sat, 23:59)

In the database, the stored open/close time values may be between 0 and 11519 (Sat, 47:59)

Now suppose, that the current time is "Sun, 01:00" (first day of week), executing the above coversion this value is 60; and the open/close times for last day of week (Saturday) are set to "17:00" and "02:00" (which is actually Sunday), stored in the DB as 9660 and 10200 (Saturday, 26:00). In this case, the above query will not find the record we need (Sat, 17:00, 02:00), because we probably don't have any open_time less than "02:00" (120). To solve this, we convert "Sun, 01:00" to "Sat, 25:00", by adding 7*1440 (a whole week) to the $currenttime, which will result 10140. Then DB query like this: 

SELECT open_time,clos_time FROM open_hours 
    WHERE (open_time <= $currenttime 
         AND close_time >= $currenttime)
         OR (open_time <= $currenttime +10080
         AND close_time >= $currenttime + 10080);

Or what is alternative and neater solution?

Upvotes: 2

Views: 5734

Answers (2)

Michael Durrant
Michael Durrant

Reputation: 96484

I have learned from experience to always store things for what they are and use datatypes appropriately so I would recommend the time or date-time option. As you start to do anything else with it it is better to have it in the correct format and just cast it for display, sorting, etc.

All the SQL databases I know support date / date/time / time formats, certainly mysql.

If you ever need to move it i.e. export it to another db or format you'll be using a tool (even if just a sql script) that will have the ability to cast it to another format or type.

Upvotes: 7

SeanCannon
SeanCannon

Reputation: 77966

Use INT since not all databases support DATE fields, or support them in the same way. INT is much more portable and it's just as easy to figure out your date with the integer timestamp if you choose to go that route.

Upvotes: 1

Related Questions