My dad used to emphasize “Use the right tool for the job at hand!”

So, if you need to record a date use a DATE type column. If you need to record a time of day, use a TIME type column (if your system supports such, otherwise use an integer containing the number of hours, minutes, or seconds since midnight). If you need to record a time of day on a particular date then use a DATETIME type column if your system supports such (or use a DATE and a TIME column together). If you need to record a specific date and time in a specific timezone, then your system may support a data types that includes timezone. If so use that.

It all depends on why you are storing the value in the database. For many things datetime is used. You want to know when something happened down to the second or millisecond. I used datetime for birthday once. Some people had problems because the time was set to midnight. If the record was recalled with a different time zone and the converted to local time, the birthday could shift to a different date. I would not use a date/time object for birthday again.

Consider using a datetimeoffset because it handles time zones. This handles daylight savings, when a time object has no time zone information.

In MySQL, a table cannot have two different data types for one column. Each column in a MySQL table is designated to hold data of a specific type. Mixing data types in a single column would violate the basic structure and principles of the database system. However, there are some workarounds or practices that can achieve a similar outcome:

1. Using the Widest Data Type: Choose a data type that can encompass all possible types you intend to store. For instance, if you need to store both integers and decimals, you might use DECIMAL as it can handle both.
2. Using Separate Columns: Split the data that requires different types into separate columns. This allows you to maintain the integrity and structure of the table while accommodating various data types. For instance: If you have a mix of numerical and textual data, create two columns: one for numbers (e.g., INT) and another for text (e.g., VARCHAR).
3. Storing Serialized Data: Serialize the data and store it as a string. This method involves converting diverse data types into a single string format before storage, allowing flexibility. However, it may complicate querying and manipulating the data.
4. Using Numeric or Textual Representation: Sometimes, you can represent different types of data in a compatible format. For example: Dates and times can be stored as TIMESTAMP, which can accommodate both date and time values. Boolean values can be represented as TINYINT(1) where 1 stands for true and 0 for false.

Remember, while these alternatives might serve specific purposes, they come with trade-offs. It's essential to weigh the pros and cons based on your specific use case before implementing any workaround.

In general, there are three time thingies in databases:

• DATE - date only, no time part. Date math assumes that a DATE type has time-part equal is 00:00:00 if you do a calculation that involves time, ie adding hours or something. You’d use this if you only care about the date, not the “time-part” of a fully-expressed moment in time, ie “SHIP_DATE”.
• TIMESTAMP - time since midnight, no date. operations that need a date are undefined, and if they’re implemented, will treat the date as “today”. I’ve usually seen this used in situations where it was useful for the application logic to split the date and time into separate fields, ie if they wanted to have an index on time itself without date.
• DATETIME - has both date and time. You’d use this if you need a fully-expressed moment in time, such as the exact moment an email arrived or a sensor sent its data.

The “Data too long for column” error occurs when you insert more data for a column that does not have the capability to store that data.

For Example - If you have data type of varchar(6) that means it stores only 6 characters. Therefore, if you will give more than 6 characters, then it will give an error.

Let us create a table to understand the error. The query to create a table is as follows −

mysql> create table DataToolongDemo 
  −> ( 
  −> Name varchar(10) 
   −> ); 
Query OK, 0 rows affected (0.55 sec) 

Above, we have created a table successfully. We have set a field “Name” with “varchar(10). Now, if we will give more than 10 characters, an error will generate.

The error is as follows −

mysql> insert into DataToolongDemo values('Carol Taylor'); 
ERROR 1406 (22001): Data too long for column 'Name' at row 1 

To rectify the above error, you can set the type to longtext. The query is as follows to change type to longtext, since currently the type is “varchar” −

mysql> alter table DataToolongDemo change Name Name longtext; 
Query OK, 0 rows affected (2.22 sec) 
Records: 0 Duplicates: 0 Warnings: 0 

Now if you will insert same record, then no error will get generated −

mysql> insert into DataToolongDemo values('Carol Taylor'); 
Query OK, 1 row affected (0.11 sec) 

Display all records from the table with the help of select statement. The query is as follows −

mysql> select *from DataToolongDemo; 

The following is the output −

+--------------+ 
| Name         | 
+--------------+ 
| Carol Taylor | 
+--------------+ 
1 row in set (0.00 sec) 

Storing Room Numbers as Strings in MySQL Databases

When designing a MySQL database, it is important to choose the appropriate data types for each field. In the case of room numbers, some developers may be inclined to store them as integers, but there are advantages to storing room numbers as strings instead.

Flexibility and Data Integrity

One advantage of storing room numbers as strings is that it allows for greater flexibility in formatting and representation. For example, room numbers may include letters or special characters in addition to numbers, such as "A101" or "2B3". By storing room numbers as strings, we can accommodate for these variations and ensure that data integrity is maintained.

If we were to store room numbers as integers, we would need to remove any non-numeric characters before storing them, which can be time-consuming and error-prone. Additionally, storing room numbers as integers may not be sufficient for representing certain types of room numbers, such as those that include leading zeros or negative numbers.

Ease of Use and Readability

Storing room numbers as strings also makes it easier to use and read the data in the database. For example, when querying the database, it is more intuitive to search for a room number using a string comparison operator (such as "WHERE room_number='A101'") than it is to convert the room number to an integer and use a numerical comparison operator (such as "WHERE room_number=101").

In addition, strings are generally easier for humans to read and interpret than integers. By storing room numbers as strings, we can make the data more accessible and user-friendly for both developers and end-users.

Yes, the fast query time is due to the way MySQL is implemented. At a high level, most of the performance advantages are gained by two types of optimizations:

(a) using indexes to turn full table-scans into row lookups; most indexes are stored as B+ tree data structures.

(b) storing frequently-accessed data and indexes in RAM, instead of reading them from disk on every query.

There are many other details of course, but those two broad principles are the most concise answer to your question. For more information I suggest you read the MySQL Internals Manual.

The best book on tuning MySQL includes a lot of information about its internals: High Performance MySQL 3rd Edition.

If you're really interested, you can Download MySQL Community Server source code and read it for yourself. Most of it is written in C and C++.

It’s really up to you!

In my opinion, I would always use the back-end language’s time features.

There are a number of reasons where MySQL DATETIME can prove problematic in some applications:

• DATETIME gets the local time of the server, so if your server is hosted in a different timezone, the timestamp stored will be incorrect for users outside that region. Your back-end language should have features to get the user’s local time, or even better UNIX
  [1]
  time.
• Time stamps aren’t very useful. DATETIME stores a timestamp like 2009-03-15 14:01:43, which isn’t actually useful for most applications these days.

Stored time is commonly used to calculate how much time has elapsed since an event, eg. How long ago did this user login? or Has 24 hours passed since this email was sent?

That’s why UNIX time is so useful, it gives you the number of seconds since 1 January 1970 (eg. 1559292847). This number is universal across all timezones, and performing maths on it is easy. Your backend language can easily convert it back to real time in whichever format and timezone needed.

My suggestion is to store the UNIX time number, and not an SQL timestamp.

Here’s an article that explains it in a bit more detail: Why MySQL’s (SQL) DATETIME can and should be avoided

Again, it’s up to you.

Footnotes

The answer is yes if

If your application is expecting nulls from this column for any reason. Mysql silently places an empty string when null is passed to a text column.

If your intent is to index the entire column. You will be required to specify the first N characters when defining the index.

If storage is an issue. As a general rule a text column is going to need more bytes to describe the length of the column. With this consideration those bytes, on a very busy server, are also taking up cache space.

Finally if a column is just big for an artificial reason then data integrity is at risk if there is an issue with the application. If the column is only supposed to be 60 characters the db should ideally be the last line of defense in enforcing this rule and the application should be written to fail gracefully if the rule is violated.

Yes.

It depends.

For example, a date stored as an integer is easier to compare to another date for purposes of sorting.

But if you use a normalized string of the form yyyymmdd then the normal string comparison operators will show that 20221226 is greater than 20211226.

The problem is with integers is that there is an “epoch date”, the date represented by the value 0.

For Unix/Linux, this is 1-Jan-1970. So you can’t really have an integer that represents an earlier date. Or you can, but it will be a negative integer, and I don’t think conversions to strings for negative integers are supported.

And, for the 32-bit time, it rolls over to 1-Jan-1970 at 03:14:07 UTC on 19-Jan-2038.

The Excel epoch date is 1-Jan-1900. It measures days from that date, not seconds. (There is also an epoch date of 1-Jan-1904, but I don’t want to go down that rabbit hole).

The epoch date for Microsoft Windows is 1-Jan-1600, and the 64-bit timestamp is the number of 100-nanosecond units from that date.

The problem with all of these integers is the inability to represent dates earlier than the epoch date. This makes epoch dates useless for places like libraries and museums, so the text string representation is the only one that can be valid. And even then, for a museum, you have to be able to support dates “BC” or “BCE” or whatever term you want to use before the common “AD” calendar came about (footnote: Jesus was born in 4BC. True. Look it up). So you have to be able to deal with strings like -00041225, which, by the way, was not actually the birthday of Jesus, since he was not born on December 25. That date was chosen to make it work with the pagan winter solstice holiday in England. So a straight string comparison doesn’t really work, either. If you really want an epoch date, something on the order of -13.8 billion years is a good start. Note also that the year starting on 1-Jan is also a reasonably modern invention; September was the 7th month October the 8th month, November the 9th month and December the 10th month, because the year used to start on March 1, the month of the Spring Equinox. So the numbering of the months was different prior to the change to 1-Jan. And let’s not forget the 11-day change caused by the adoption of the Gregorian calendar. Which took place at different times in different countries. George Washington was born under the Julian calendar, because England had not yet accepted the Papist calendar system. But he was inaugurated under the Gregorian calendar system.

As Quora User wrote, there are two good choices. What you should consider, instead of thinking about good practice, is to think about how it would affect your solution.

If your system need to query fields in combination with the text field, or retrieve the text field in all queries, there is a huge benefit in having that field in the table. But it might require that you start earlier calculating how many rows are expected, along with indexes and how much memory that will consume. When you have those numbers, try to create such a database with dummy data, and measure the most critical queries.

For a database, it is important, that it does not have to read from disk, for every query. It will read from disk, if there is not enough memory to cache indexes and the working set of the data. If your indexes fits your queries nicely, your indexed fields will make sure you read just the rows you need from the index, and you only fetch those rows from the database. For the index, this means, that rows are read in a range, until the query is fulfilled. In those situations, the index has good locality, and only a part of the index needs to be in memory, to fulfil the query.

3000 chars could be about 3000-, 6000- or 12000 bytes, depending on encoding and storage options. If your row, without this field, would use a few hundred bytes, then it adds significantly to the size of a row. And if you expect millions of rows, then you better have a good partioning scheme, that will fit nicely with your queries.

To mitigate size problems, and improve I\O, there are database storage engines that offers compression on disk and some in memory cache. Have that in mind, it might make storing varchars a smaller problem.

Do not worry that much about best practice. Problems are not the same. Data sizes and hardware are not the same. No matter what solution you come up with, it might be the best possible to solve the problem you are solving, with the given requirements.