Holding times in a database: STRING vs TIMESTAMP

Question

I have a database that each entry is a business, some of the data is opening and closing times. Originally, to support multiple opening and closing times in a day I was going to store it as a string such as: '09:00-15:00,17:00-22:00' and then split it up and convert it to TIMESTAMPS server-side. I now understand that it is "bad form" to store times as strings. But the solution requires a second table.

What exactly is the issue with using a string instead of DATE or TIMESTAMP? If I kept it all in one table it would get pulled with my current query and then just processed and converted into a TIMESTAMP. Using multiple tables causes more queries. Is the string manipulation and conversion really that much more taxing than another query searching through an entire table that will end up with thousands of entries?

Answer 1

There are three separate issues here. One is whether it is better to combine two elementary data items into a list, and store the list in one place, or to keep the elementary items separate, and store them in two separate places. For these purposes, I'm considering the intersection of a single row and a single column to be one place.

The second issue is whether it is better to identify a time interval as a single elementary item or better to describe a time interval as two elementary items, side by side.

The third issue is whether it is better to store dates as strings or use the DBMS timestamp datatype.

For the first issue, it's better to store separate elementary items in separate places. In this case, in two separate rows of the same table.

For the second issue, it's better to describe a time interval as two timestamps side by side than to combine them into an interval, for the DBMS systems I have used. But there are DBMS systems that have a time interval datatype that is distinct from the timestamp datatype. You mileage may vary.

For the third issue, it's better to use the DBMS timestamp datatype to describe a point in time than a character string. Different DBMS products have different facilities for storing a time without any date associated with it, and in your case, you may want to make use of this. It depends on how you will be searching the data. If you are going to want to find all the rows that contain a 9:00 to 15:00 time range regardless of date, you will want to make use of this. If you are going to want to find all the rows that contain a 9:00 to 15:00 range on any Tuesday, I suggest you look into data warehousing techniques.

Why do I gove these answers?

For the first two answers, it has to do with normalization, indexing, and searching efficiency. These concepts are all related, and you should learn them all at about the same time, because each of them will help you understand the other two.

For the third answer, it has to do with using the power of the DBMS to do the detailed work for you. Any good DBMS will have tools for subtracting timestamps to yield a time interval, and for ordering timestamps correctly. Getting the DBMS to do this work for you will save you a lot of work in your application programming, and generally use less computer resources in production.

Answer 2

There are three separate issues here. One is whether it is better to combine two elementary data items into a list, and store the list in one place, or to keep the elementary items separate, and store them in two separate places. For these purposes, I'm considering the intersection of a single row and a single column to be one place.

The second issue is whether it is better to identify a time interval as a single elementary item or better to describe a time interval as two elementary items, side by side.

The third issue is whether it is better to store points in time as strings or use the DBMS timestamp datatype.

For the first issue, it's better to store separate elementary items in separate places. In this case, in two separate rows of the same table.

For the second issue, it's better to describe a time interval as two timestamps side by side than to combine them into an interval, for the DBMS systems I have used. But there are DBMS systems that have a time interval datatype that is distinct from the timestamp datatype. Your mileage may vary.

For the third issue, it's better to use the DBMS timestamp datatype to describe a point in time than a character string. Different DBMS products have different facilities for storing a time without any date associated with it, and in your case, you may want to make use of this. It depends on how you will be searching the data. If you are going to want to find all the rows that contain a 9:00 to 15:00 time range regardless of date, you will want to make use of this. If you are going to want to find all the rows that contain a 9:00 to 15:00 range on any Tuesday, I suggest you look into data warehousing techniques.

Why?

For the first two answers, it has to do with normalization, indexing, searching strategies, and query optimization. These concepts are all related, and each of them will help you understand the other three.

For the third answer, it has to do with using the power of the DBMS to do the detailed work for you. Any good DBMS will have tools for subtracting timestamps to yield a time interval, and for ordering timestamps correctly. Getting the DBMS to do this work for you will save you a lot of work in your application programming, and generally use less computer resources in production.

Having said that, I don't know mySQL, so I don't know how good it is for date manipulations or for query optimization.

Answer 3

By '09:00-15:00,17:00-22:00', you mean that it is open for two periods of time? Then 2 rows in one table. If the open times vary by day of week, then another column to specify that. If you want holidays, another column.

But... What will you be doing with the data?...

If you are going to write SQL to use the times, then what I described may work best.

If the processing will be in your application code, then a CSV file that you load every time, parse every time, etc, may be best.

Or, you could put that CSV file into a table as one big string. (However, this is not much different than having a file.)