MySQL Cookbook, 2nd Edition

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, and it’s important to understand them to avoid surprising results when you manipulate temporal data. This chapter covers the following aspects of working with date and time values in MySQL:

Choosing a temporal data type

MySQL provides several temporal data types to choose from when you create tables. By knowing their properties, you’ll be able to choose them appropriately.

Displaying dates and times

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

Changing the client time zone

The server interprets TIMESTAMP values in the client’s current time zone rather than its own. Clients in different time zones should set their zone so that the server can properly interpret TIMESTAMP values for them.

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.

Using TIMESTAMP values to track row modifications

The TIMESTAMP data type has some special properties that make it convenient for recording row creation and modification times automatically.

Breaking dates or times into component values

You can split date and time values when you need only a piece, such as the month part of a date or ...