MySQL Cookbook

Chapter 5. Working with Dates and Times

Introduction

MySQL has several data types for representing dates and times, and several functions for operating on them. MySQL stores dates and times in specific formats. It’s important to understand them to avoid surprises in how MySQL interprets input data. MySQL also has reformatting functions for producing date and time output in formats other than the default. This chapter covers the following aspects of working with temporal values in MySQL:

• Displaying dates and times.

  MySQL displays temporal values using specific formats by default, but you can produce other formats by calling the appropriate function.

• Determining the current date or time.

  MySQL provides functions that return the date and time, which is useful for applications that need to know these values or need to calculate other temporal values in relation to them.

• Decomposing dates or times into component values.

  This section explains how to split date and time values when you need only a piece, such as the month part or the hour part.

• Synthesizing dates and times from component values.

  The complement of splitting apart temporal values is to create them from subparts. This section shows how.

• Converting between dates or times and basic units.

  Some date calculations are more easily performed using the number of days or seconds represented by a date or time value than by using the value itself. MySQL makes it possible to perform several kinds of conversions between date and time values ...