Infrastructure from Code: the New Wave of Cloud Infrastructure Management

This overview of date and time values in MySQL will help you take advantage of temporal values in your database tables.

Both new and experienced users of the popular MySQL database system can often get confused about how temporal values are handled by the database. Sometimes users don’t bother learning much about temporal value data types. This may be because they think there isn’t much to know about them. A date is a date, right? Well, not always. Taking a few minutes to learn how MySQL stores and displays dates and times is beneficial. Learning how to best take advantage of the temporal values in your database tables can help make you a better coder.

MySQL temporal data types

When you are building your tables in MySQL, you choose the proper data type which most efficiently holds the data you intend to insert into the table (INT, FLOAT, CHAR,and so on). MySQL provides you with five data types for temporal values. They are: DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

MySQL uses the ISO 8601 format to store the values in the following formats:

• DATE  YYYY-MM-DD
• TIME   HH:MM:SS
• TIMESTAMP YYYY-MM-DD  HH:MM:SS
• YEAR YYYY

Datetime compared to Timestamp

You may have noticed that the DATETIME and TIMESTAMP data types hold the same data. You might wonder if there are any differences between the two. There are differences.

First, the range of dates that can be used differ. DATETIME can hold dates between 1000-01-01 00:00:00 and 9999-12-31 23:59:59, whereas TIMESTAMP has a much more limited range of 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC.

Second, while both data types allow you to auto_initialize or auto_update their respective values (with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP respectively), doing so was not available for DATETIME values until version 5.6.5. You can use one of the MySQL synonyms for CURRENT_TIMESTAMP if you choose, such as NOW() or LOCALTIME().

If you use ON UPDATE CURENT_TIMESTAMP (or one of its synonyms) for a DATETIME value, but do not use the DEFAULT CURRENT_TIMESTAMP clause, then the column will default to NULL. This happens unless you include NOT NULL in the table definition, in which case it defaults to zero.

Another important thing to keep in mind is that although normally neither a DATETIME nor a TIMESTAMP column have a default value unless you declare one, there is one exception to this rule. The first TIMESTAMP column in your table is implicitly created with both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses if neither is specified and if the variable explicit_defaults_for_timestamp is disabled.

To check this variable’s status, run:

mysql> show variables like 'explicit_default%';

If you want to turn it on or off, run this code, using 0 for off and 1 for on:

mysql> set explicit_defaults_for_timestamp = 0;

Time

MySQL’s TIME data type may seem simple enough, but there are a few things that a good programmer should keep in mind.

First, be aware that although time is often thought of as the time of day, it is in fact elapsed time. In other words, it can be a negative value or can be greater than 23:59:59. A TIME value in MySQL can be in the range of -838:59:59 to 838:59:59.

Also, if you abbreviate a time value, MySQL interprets it differently depending on whether you use a colon. For example, the value 10:34 is seen by MySQL as 10:34:00. That is, 34 minutes past ten o’clock. But if you leave out the colon, 1034′, MySQL sees that as 00:10:34. That is, ten minutes and 34 seconds.

Finally, you should know that TIME values (as well as the time portion of DATETIME and TIMESTAMP columns) can, as of version 5.6.4, take a fractional unit. To use it, add an integer (max value six) in parentheses at the end of the data type definition.

time_column TIME(2)

Time zones

Time zone changes not only cause confusion and fatigue in the real world, but have also been known to cause problems in database systems. The earth is divided into 24 separate time zones which usually change with every 15 degrees of longitude. I say usually because some nations choose to do things differently. China, for example, operates under a single time zone instead of the five that would be expected.

The question is, how do you handle users of a database system who are in different time zones. Fortunately, MySQL doesn’t make this too difficult.

To check your session time zone, run:

mysql> select @@session.time_zone;

If it says System, that means that it is using the timezone set in your my.cnf configuration file. If you are running your MsSQL server on your local computer, this is probably what you’ll get, and you don’t need to make any changes.

If you would like to change your session’s time zone, run a command such as:

mysql> set time_zone = '-05:00';

This sets your time zone to five hours behind UTC. (US/Eastern).

Getting the day of the week

To follow along with the code in the rest of this tutorial, you should create a table with date values on your system. For example:

mysql> create table test
( row_id smallint not null auto_increment primary key,
the_date date not null);

Then insert some random dates into the table using the ISO 8601 format, such as:

mysql> insert into test (the_date) VALUES ('2022-01-05');

I put four rows of date values in my test table, but put as few or as many as you’d like.

Sometimes you may wish to know what day of the week a particular day happened to be. MySQL gives you a few options.

The first, and perhaps most obvious way, is to use the DAYNAME() function. Using the example table, DAYNAME() tells you the day of the week for each of the dates:

mysql> SELECT the_date, DAYNAME(the_date) FROM test ;
+------------+-------------------------------+
| the_date   | DAYNAME(the_date)             |
+------------+-------------------------------+
| 2021-11-02 | Tuesday                       |
| 2022-01-05 | Wednesday                     |
| 2022-05-03 | Tuesday                       |
| 2023-01-13 | Friday                        |
+------------+-------------------------------+
4 rows in set (0.00 sec)

The other two methods for getting the day of the week return integer values instead of the name of the day. They are WEEKDAY() and DAYOFWEEK(). They both return numbers, but they do not return the same number. The WEEKDAY() function returns a number from 0 to 6, with 0 being Monday and 6 being Sunday. On the other hand, DAYOFWEEK() returns a number from 1 to 7, with 1 being Sunday and 7 being Saturday.

mysql> SELECT the_date, DAYNAME(the_date),
WEEKDAY(the_date), DAYOFWEEK(the_date) FROM test;
+------------+------------------+------------------+--------------------+
| the_date   | DAYNAME(the_date)| WEEKDAY(the_date)| DAYOFWEEK(the_date)|
| 2021-11-02 | Tuesday          | 1                | 3                  |
| 2022-01-05 | Wednesday        | 2                | 4                  |
| 2022-05-03 | Tuesday          | 1                | 3                  |
| 2023-01-13 | Friday           | 4                | 6                  |
+------------+------------------+------------------+--------------------+
4 rows in set (0.00 sec)

When you only want part of the date

Sometimes you may have a date stored in your MySQL table, but you only wish to access a portion of the date. This is no problem.

There are several conveniently-named functions in MySQL that allow for easy access to a particular portion of a date object. To show just a few examples:

mysql> SELECT the_date, YEAR(the_date), MONTHNAME(the_date), 
DAYOFMONTH(the_date) FROM test ;
+-----------+---------------+-------------------+---------------------+
| the_date  | YEAR(the_date)|MONTHNAME(the_date)| DAYOFMONTH(the_date)|
+-----------+---------------+-------------------+---------------------+
| 2021-11-02| 2021          | November          | 2                   |
| 2022-01-05| 2022          | January           | 5                   |
| 2022-05-03| 2022          | May               | 3                   |
| 2023-01-13| 2023          | January           | 13                  |
+-----------+---------------+-------------------+---------------------+
4 rows in set (0.00 sec)

MySQL also allows you to use the EXTRACT() function to access a portion of a date. The arguments you provide to the function are a unit specifier (be sure that it’s singular), FROM, and the column name. So, to get just the year from our test table, you could write:

mysql> SELECT EXTRACT(YEAR FROM the_date) FROM test;
+----------------------------------------------+
| EXTRACT(YEAR FROM the_date)                  |
+----------------------------------------------+
| 2021                                         |
| 2022                                         |
| 2022                                         |
| 2023                                         |
+----------------------------------------------+
4 rows in set (0.01 sec)

Inserting and reading dates with different formats

As mentioned earlier, MySQL stores date and time values using the ISO 8601 format. But what if you want to store date and time values another way, such as MM-DD-YYYY for dates? Well, first off, don’t try. MySQL stores dates and times in the 8601 format and that’s the way it is. Don’t try to change that. However, that doesn’t mean you have to convert your data to that particular format before you enter it into your database, or that you cannot display the data in whatever format you desire.

If you would like to enter a date into your table that is formatted in a non-ISO way, you can use STR_TO_DATE(). The first argument is the string value of the date you want to store in your database. The second argument is the formatting string which lets MySQL know how the date is organized. Let’s look at a quick example, and then I’ll delve a little deeper into what that odd-looking formatting string is all about.

mysql> insert into test (the_date) values (str_to_date('January 13, 2023','%M %d, %Y'));

Query OK, 1 row affected (0.00 sec)

You put the formatting string in quotes, and precede each of the special characters with a percent sign. The format sequence in the above code tells MySQL that my date consists of a full month name (%M), followed by a two-digit day (%d), then a comma, and finally a four-digit year (%Y). Note that capitalization matters.

Some of the other commonly used formatting string characters are:

• %b abbreviated month name (example: Jan)
• %c numeric month (example: 1)
• %W name of day (example: Saturday)
• %a abbreviated name of day (example: Sat)
• %T 24-hour time (example: 22:01:22)
• %r 12-hour time with AM/PM (example: 10:01:22 PM)
• %y 2-digit year (example: 23)

Note that for the 2-digit year (%y) the range of years is 1970 to 2069. So numbers from 70 through 99 are assumed 20th century, while numbers from 00 to 69 are assumed to be 21st century.

If you have a date stored in your database, and you would like to display it using a different format, you can use the DATE_FORMAT() function:

mysql> SELECT DATE_FORMAT(the_date, '%W, %b. %d, %y') FROM test;
+-----------------------------------------+
| DATE_FORMAT(the_date, '%W, %b. %d, %y') |
+-----------------------------------------+
| Tuesday, Nov. 02, 21                    |
| Wednesday, Jan. 05, 22                  |
| Tuesday, May. 03, 22                    |
| Friday, Jan. 13, 23                     |
+-----------------------------------------+
4 rows in set (0.00 sec)

Conclusion

This tutorial should give you a helpful overview of date and time values in MySQL. I hope that this article has taught you something new that allows you to have both better control and a greater understanding into how your MySQL database handles temporal values.

By: Hunter Coleman
Source: Opensource