Home / Basic MySQL Tutorial / MySQL CHAR Data Type

 

Summary: in this tutorial, you will learn about MySQL CHAR data type and how to apply it in your database table design.

Introduction to MySQL CHAR data type

The CHAR data type is a fixed-length character type in MySQL. We often declare the CHAR type with a length that specifies the maximum number of characters that we want to store. For example, CHAR(20)can hold up to 20 characters.

If the data that you want to store is a fixed size, you should use the CHAR data type. You’ll get a better performance in comparison with VARCHAR in this case.

The length of the CHAR data type can be any value from 0 to 255. When you store a CHAR value, MySQL pads its value with spaces to the length that you declared.

When you query the CHAR value, MySQL removes the trailing spaces.

Note that MySQL will not remove the trailing spaces if you enable the PAD_CHAR_TO_FULL_LENGTHSQL mode.

The following statement creates a table with a CHAR column.

The status column has the CHAR data type. It can hold up to 3 characters.

Now, we insert 2 rows into the mysql_char_test table.

We use the length function to get the length of each CHAR value.

The following statement inserts a CHAR value with the leading and trailing spaces.

However, when we retrieve the value, MySQL removes the trailing space.

Comparing MySQL CHAR values

When storing or comparing the CHAR values, MySQL uses the character set collation assigned to the column.

MySQL does not consider trailing spaces when comparing CHAR values using the comparison operator such as =, <>, >, <, etc.

Notice that the LIKE operator does consider the trailing spaces when you do pattern matching with CHAR values.

In the previous example, we stored the value Y with both leading and trailing spaces. However, when we execute the following query:

MySQL returns no row because it does not consider the trailing space. To match with the ‘ Y ‘, we need to remove the trailing space as follows:

MySQL CHAR and UNIQUE index

If the CHAR column has a UNIQUE index and you insert a value that is different from an existing value in a number of trailing spaces, MySQL will reject the changes because of duplicate-key error.

See the following example.

First, create a unique index for the status column of the mysql_char_test table.

Second, insert a new row into the mysql_char_test table.

Third, insert the following value will cause a duplicate-key error.

 

In this tutorial, we have introduced you to the MySQL CHAR data type and its features. Now, you should have a good understanding of the CHAR data type to apply it in your database design.

Home / Basic MySQL Tutorial / A Complete Guide To MySQL DATETIME Data Type

 

Summary: in this tutorial, you will learn about MySQL DATETIME data type and how to use some handy functions for manipulating DATETIME effectively.

Introduction to MySQL DATETIME data type

You use MySQL DATETIME to store a value that contains both date and time. When you query data from a DATETIME column, MySQL displays the DATETIME value in the following format:

By default, DATETIME values range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

A DATETIME value uses 5 bytes for storage. In addition, a DATETIME value can include a trailing fractional second up to microseconds with the format YYYY-MM-DD HH:MM:SS[.fraction] e.g., 2015-12-20 10:01:00.999999. When including the fractional second precision, DATETIME values require more storage as illustrated in the following table:

Fractional Seconds Precision	Storage (Bytes)
0	0
1, 2	1
3, 4	2
5, 6	3

For example, 2015-12-20 10:01:00.999999 requires 8 bytes, 5  bytes for 2015-12-20 10:01:00 and 3 bytes for .999999 while 2015-12-20 10:01:00.9  requires only 6 bytes, 1 byte for the fractional second precision.

Note that before MySQL 5.6.4, DATETIME values requires 8 bytes storage instead of 5 bytes.

MySQL DATETIME vs. TIMESTAMP

MySQL provides another temporal data type that is similar to the DATETIME called  TIMESTAMP.

The TIMESTAMP requires 4 bytes while DATETIME requires 5 bytes. Both TIMESTAMP and DATETIME require additional bytes for fractional seconds precision.

TIMESTAMP values range from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. If you want to store temporal values that are beyond 2038, you should use DATETIME instead of TIMESTAMP.

MySQL stores TIMESTAMP in UTC value. However, MySQL stores the DATETIME value as is without timezone. Let’s see the following example.

First, set the timezone of the current connection to +00:00.

Next, create a table named timestamp_n_datetime that consists of two columns: ts and dt with TIMESTAMP and DATETIME types using the following statement.

Then, insert the current date and time into both ts and dt columns of the timestamp_n_datetime table,

After that, query data from the timestamp_n_datetime table.

Both values in DATETIME and TIMESTAMP columns are the same.

Finally, set the connection’s time zone to +03:00 and query data from the timestamp_n_datetime table again.

As you can see, the value in the TIMESTAMP column is different. This is because the TIMESTAMP column stores the date and time value in UTC when we changed the time zone, the value of the TIMESTAMPcolumn is adjusted according to the new time zone.

It means that if you use the TIMESTAMP data to store date and time values, you should take a serious consideration when you move your database to a server located in a different time zone.

MySQL DATETIME functions

The following statement sets the variable @dt to the current date and time using the NOW() function.

To query the value of the @dt variable, you use the following SELECT statement:

MySQL DATE function

To extract the date portion from a DATETIME value, you use the DATE function as follows:

This function is very useful in case you want to query data based on a date but the data stored in the column is based on both date and time.

Let’s see the following example.

Suppose you want to know which row created on 2015-11-05, you use the following query:

It returns no rows.

This is because the created_at column contains not only date but also time. To correct it, you use the DATE function as follows:

It returns one row as expected. In case the table has many rows, MySQL has to perform a full table scan to locate the rows that match the condition.

MySQL TIME function

To extract the time portion from a DATETIME value, you use the TIME function as the following statement:

MySQL YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE and SECOND functions

To get the year, quarter, month, week, day, hour, minute, and second from a DATETIME value, you use the functions as shown in the following statement:

MySQL DATE_FORMAT function

To format a DATETIME value, you use the DATE_FORMAT function. For example, the following statement formats a DATETIME value based on the %H:%i:%s - %W %M %Y format:

MySQL DATE_ADD function

To add an interval to a DATETIME value, you use DATE_ADD function as follows:

MySQL DATE_SUB function

To subtract an interval from a DATETIME value, you use DATE_SUB function as follows:

MySQL DATE_DIFF function

To calculate a difference in days between two DATETIME values, you use the DATEDIFF function. Notice that the DATEDIFF function only considers the date part of a DATETIME value in the calculation.

See the following example.

First, create a table named datediff_test that has one column whose data type is DATETIME.

Second, insert some rows into the datediff_test table.

Third, use the DATEDIFF function to compare the current date and time with the value in each row of the datediff_test table.

In this tutorial, you have learned about MySQL DATETIME data type and some useful DATETIME functions.

Home / Basic MySQL Tutorial / MySQL DECIMAL Data Type

 

Summary: in this tutorial, we will introduce you to the MySQL DECIMAL data type and how to use it effectively in your database table.

Introduction to MySQL DECIMAL data type

The MySQL DECIMAL data type is used to store exact numeric values in the database. We often use the DECIMAL data type for columns that preserve exact precision e.g., money data in accounting systems.

To define a column whose data type is DECIMAL you use the following syntax:

In the syntax above:

• P is the precision that represents the number of significant digits. The range of P is 1 to 65.
• D is the scale that that represents the number of digits after the decimal point. The range of D is 0 and 30. MySQL requires that D is less than or equal to (<=) P.

The DECIMAL(P,D) means that the column can store up to P digits with D decimals. The actual range of the decimal column depends on the precision and scale.

Besides the DECIMAL keyword, you can also use DEC, FIXED, or NUMERIC because they are synonyms for DECIMAL.

Like the INT data type, the DECIMAL type also has UNSIGNED and ZEROFILL attributes. If we use the UNSIGNED attribute, the column with DECIMAL UNSIGNED will not accept negative values.

In case we use ZEROFILL, MySQL will pad the display value by 0 up to display width specified by the column definition. In addition, if we use ZERO FILL for the DECIMAL column, MySQL will add the UNSIGNED attribute to the column automatically.

The following example defines amount column with DECIMAL data type.

In this example, the amount column can store 6 digits with 2 decimal places; therefore, the range of the amount column is from 9999.99 to -9999.99.

MySQL allows us to use the following syntax:

This is equivalent to:

In this case, the column contains no fractional part or decimal point.

In addition, we can even use the following syntax.

The default value of P is 10 in this case.

MySQL DECIMAL storage

MySQL assigns the storage for integer and fractional parts separately. MySQL uses binary format to store the DECIMAL values. It packs 9 digits into 4 bytes.

For each part, it takes 4 bytes to store each multiple of 9 digits. The storage required for leftover digits is illustrated in the following table:

Leftover Digits	Bytes
0	0
1–2	1
3–4	2
5–6	3
7–9	4

For example, DECIMAL(19,9) has 9 digits for the fractional part and 19-9 = 10 digits for integer part. The fractional part requires 4 bytes. The integer part requires 4 bytes for the first 9 digits, for 1 leftover digit, it requires 1 more byte. In total, the DECIMAL(19,9) column requires 9 bytes.

MySQL DECIMAL data type and monetary data

We often use the DECIMAL data type for monetary data such as prices, salary, account balances, etc. If you design a database that handle the monetary data, the following syntax should be OK.

However, if you want to comply with Generally Accepted Accounting Principles (GAAP) rules, the monetary column must have at least 4 decimal places to make sure that the rounding value does not exceed $0.01. In this case, you should define the column with 4 decimal places as follows:

 

 MySQL DECIMAL data type example

First, create a new table named materials with three columns: id, description, and cost.

Second, insert data into the materials table.

Third, query data from the materials table.

Fourth, change the cost column to include  the ZEROFILL attribute.

Fifth, query the materials table again.

As you see, we have many zeros padded in the output values.

In this tutorial, we have shown gave you detailed information on MySQL DECIMAL data type and shown you how to apply it to the columns that store exact numeric data such as financial data.

Home / Basic MySQL Tutorial / Mastering MySQL TIME Data Type

 

Summary: in this tutorial, we will introduce you to the MySQL TIME data type and show you useful temporal functions to manipulate time data effectively.

Introduction to MySQL TIME data type

MySQL uses the ''HH:MM:SS'' format for querying and displaying a time value that represents a time of day, which is within 24 hours. To represent a time interval between two events, MySQL uses the ''HHH:MM:SS'' format, which is larger than 24 hours.

To define a TIME column, you use the following syntax:

For example, the following snippet defines a column named start_at with TIME data type.

A TIME value ranges from -838:59:59 to 838:59:59. In addition, a TIME value can have fractional seconds part that is up to microseconds precision (6 digits). To define a column whose data type is TIMEwith a fractional second precision part, you use the following syntax:

N is an integer that represents the fractional part, which is up to 6 digits.

The following snippet defines a column with TIME data type including 3 digits of fractional seconds.

A TIME value takes 3 bytes for storage. In case a TIME value includes fractional second precision, it will take additional bytes based on the number of digits of the fractional second precision. The following table illustrates the storage required for fractional second precision.

Fractional Second Precision	Storage (BYTES)
0	0
1, 2	1
3, 4	2
5, 6	3

For example, TIME and TIME(0) takes 3 bytes. TIME(1) and TIME(2) takes 4 bytes (3  + 1); TIME(3) and TIME(6) take 5 and 6 bytes.

MySQL TIME data type example

Let’s take a look at an example of using the TIME data type for columns in a table.

First, create a new table named tests that consists of four columns: id, name, start_at, and end_at. The data types of the start_at and end_at columns are TIME.

Second, insert a row into the tests table.

Third, query data from the tests table.

Notice that we use ''HH:MM:SS'' as the literal time value in the INSERT statement. Let’s examine all the valid time literals that MySQL can recognize.

MySQL TIME literals

MySQL recognizes various time formats besides the ''HH:MM:SS'' format that we mentioned earlier.

MySQL allows you to use the ''HHMMSS'' format without delimiter ( : ) to represent time value. For example, ''08:30:00'' and ''10:15:00'' can be rewritten as ''083000'' and ''101500''.

However, 108000 is not a valid time value because 80 does not represent the correct minute. In this case, MySQL will raise an error if you try to insert an invalid time value into a table.

MySQL issued the following error message after executing the above statement.

In addition to the string format, MySQL accepts the HHMMSS as a number that represents a time value. You can also use SS, MMSS. For example, instead of using ''082000'', you can use 082000 as follows:

For the time interval, you can use the ''D HH:MM:SS'' format where D represents days with a range from 0 to 34. A more flexible syntax is ''HH:MM'', ''D HH:MM'', ''D HH'', or ''SS''.

If you use the delimiter:, you can use 1 digit to represent hours, minutes, or seconds. For example, 9:5:0 can be used instead of ''09:05:00''.

Useful MySQL TIME functions

MySQL provides several useful temporal functions for manipulating TIME data.

Getting to know the current time

To get the current time of the database server, you use the CURRENT_TIME function. The CURRENT_TIMEfunction returns the current time value as a string ( ''HH:MM:SS'') or a numeric value ( HHMMSS) depending on the context where the function is used.

The following statements illustrate the CURRENT_TIME function in both string and numeric contexts:

Adding and Subtracting time from a TIME value

To add a TIME value to another TIME value, you use the ADDTIME function. To subtract a TIME value from another TIME value, you use  the SUBTIME function.

The following statement adds and subtracts 2 hours 30 minutes to and from the current time.

In addition, you can use the TIMEDIFF() function to get a difference between two TIME values.

 

Formatting MySQL TIME values

Although MySQL uses ''HH:MM:SS'' when retrieving and displaying the a TIME value, you can display the TIME value in your preferred way using the TIME_FORMAT function.

The TIME_FORMAT function is like the DATE_FORMAT function except that the TIME_FORMAT function is used to format a TIME value only.

See the following example.

In the time format string above:

•  %h means two-digit hours from 0 to 12.
•  %i means two-digit minutes from 0 to 60.
•  %p means AM or PM.

Extracting hour, minute, and second from a TIME value

To extract the hour, minute, and second from a TIME value, you use HOUR, MINUTE, and SECONDfunctions as follows:

Getting UTC time value

To get the UTC time, you use UTC_TIME function as follows:

In this tutorial, we have been covered a lot about MySQL TIME data type and some commonly used temporal functions for manipulating TIME values.