Learning MySQL and MariaDB

Chapter 11. Date and Time Functions

For many of us, there is a morning and an afternoon in each day. Days are measured in either two 12-hour blocks or one 24-hour block. There are 12 months in a year, with each month consisting of 30 or 31 days, except for one month which usually contains 28 days, but once every four years it contains 29. While this all may be rather natural or at least familiar to humans, putting it in terms a computer can manipulate can make it seem very unnatural and frustrating. However, the recording and manipulating of date and time in a database is a very common requirement.

For storing dates and times, known as temporal data, one needs to know which type of column to use in a table. More important is knowing how to record chronological data and how to retrieve it in various formats. Although this seems to be basic, there are many built-in time functions that can be used for more accurate SQL statements and better formatting of data. In this chapter, we will explore these various aspects of date and time functions in MySQL and MariaDB.

Date and Time Data Types

Because dates and times are ultimately just strings containing numbers, they could be stored in a regular character column. However, there are data types designed specifically for dates and times. By using temporal data type columns, you can make use of several built-in functions offered by MySQL and MariaDB. So before we start learning about the date and time functions, let’s look at the data types that ...