MySQL Cookbook, 3rd Edition

Chapter 6. Working with Dates and Times

Introduction

MySQL has several data types for representing dates and times, and many functions for operating on them. MySQL stores dates and times in specific formats, and it’s important to understand them to avoid surprises in results from manipulating 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. Knowing their properties enables you to choose them appropriately.

Displaying dates and times

MySQL displays temporal values using specific formats by default. 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, not 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 and time

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

Tracking row modification times

The TIMESTAMP and DATETIME data types have special properties that enable you to record row-creation and last-modification times automatically.

Breaking dates and times into component values, creating dates and times from component values

You can split date and time values when you need ...