Dates and Times in MySQL, Part 1: Introduction

Storing dates and times in MySQL may appear to be as complicated as dates in real life to those who have never delved into the subject. Programmers often default time using an int datatype and storing the current timestamp using the time() function in PHP. Is there a best way to store dates and times or is it specific to the application I’m working on? This tutorial should answer that question and we’ll start by giving a general introduction to what MySQL has to offer in storing dates.

MySQL as three datatypes for storing date and time information: datetime, date, and timestamp. They all serve the purpose of storing date or time related information and all take the same syntax for input. However, each stores the information in a different way giving it advantages over others in certain situations.

  • Datetime:
    • Stores: date and time.
    • Format: YYYY-MM-DD HH:MM:SS.
    • Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
  • Date:
    • Stores: date.
    • Format: YYYY-MM-DD.
    • Range: 1000-01-01 to 9999-12-31.
  • Timestamp:
    • Stores: date and time.
    • Format: YYYY-MM-DD HH:MM:SS.
    • Range: 1970-01-01 00:00:00 to 2037-12-31 23:59:59.

Obviously, one would want to use datetime when needing to store the date and the time and just use date when you know you only need to store the date. For example, a published book would only need a date whereas a blog comment would need a date and a time. So, what’s the point of the timestamp type? Datetime has a wider range of values and stores the same information as timestamp.

Timestamp has three key features that date and datetime do not have. First, timestamps can default to the current date and time. All other data types must have constant default values. So, for example, instead of specifying the current date and time in your application’s query, one can specify the default value to CURRENT_TIMESTAMP as follows,

`timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Second, timestamps can be set to automatically update to the current date and time whenever an update statement is run on the row the field is in. So, instead of specifying the updated date and time in your application’s query, one can specify the value to automatically update to CURRENT_TIMESTAMP as follows,

`timestamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

And, these two can be combined,

`timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Third, timestamps convert input to the current UTC time and convert output to the current time zone. Matt Bianco’s comment on a Stackoverflow answer sums up why this is important. He asks, “How long ago was ‘2010-09-01 16:31:00’?” If that datetime was recorded in Wellington, you are in Brussels, and I am in New York, then we have to do some computing here against UTC time to find the difference. New Zealand is +12 UTC, Brussels +1, and New York -6. So, the recorded time is 2010-09-01 05:31:00 in Brussels and 2010-08-31 10:31:00 in New York. The time difference is 7 hours! And what if we don’t know where the datetime was originally recorded? Then we only have guesses!

All that to say, if one wants to record the current date and time rows are inserted and/or updated and/or one wants to know the exact date and time something happened, timestamp is the route to go, though, at the expense of a shorter time frame. So, if one is recording birthdays they will run into errors if the birthday is before 1970.

So, that is what MySQL has to offer natively for storing dates and times. Next we will look at the acceptable inputs to these data types and later what the performance differences are and some alternatives.

Published
Categorized as Database

By Joe Purcell

Joe Purcell is a technology virtuoso, cyberspace frontiersman, and connoisseur of Linux, Mac, and Windows alike.

Leave a comment