6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types

The DATETIME, DATE, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ.

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. (``Supported'' means that although earlier values might work, there is no guarantee that they will.)

The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

Automatic updating of the first TIMESTAMP column occurs under any of the following conditions:

• The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
• The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.)
• You explicitly set the TIMESTAMP column to NULL.

TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW().

You can set any TIMESTAMP column to a value different from the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later:

• Let MySQL set the column when the row is created. This will initialise it to the current date and time.
• When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value.

On the other hand, you may find it just as easy to use a DATETIME column that you initialise to NOW() when the row is created and leave alone for subsequent updates.

TIMESTAMP values may range from the beginning of 1970 to sometime in the year 2037, with a resolution of one second. Values are displayed as numbers.

The format in which MySQL retrieves and displays TIMESTAMP values depends on the display size, as illustrated by the following table. The `full' TIMESTAMP format is 14 digits, but TIMESTAMP columns may be created with shorter display sizes:

All TIMESTAMP columns have the same storage size, regardless of display size. The most common display sizes are 6, 8, 12, and 14. You can specify an arbitrary display size at table creation time, but values of 0 or greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the next higher even number.

Note: From version 4.1, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are no longer supported.

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
• As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
• As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
• As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
• As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
• As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.

Illegal DATETIME, DATE, or TIMESTAMP values are converted to the ``zero'' value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000).

For values specified as strings that include date part delimiters, it is not necessary to specify two digits for month or day values that are less than 10. '1979-6-9' is the same as '1979-06-09'. Similarly, for values specified as strings that include time part delimiters, it is not necessary to specify two digits for hour, minute, or second values that are less than 10. '1979-10-30 1:2:3' is the same as '1979-10-30 01:02:03'.

Values specified as numbers should be 6, 8, 12, or 14 digits long. If the number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or YYYYMMDDHHMMSS format and that the year is given by the first 4 digits. If the number is 6 or 12 digits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format and that the year is given by the first 2 digits. Numbers that are not one of these lengths are interpreted as though padded with leading zeros to the closest length.

Values specified as non-delimited strings are interpreted using their length as given. If the string is 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise, the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute, and second values, for as many parts as are present in the string. This means you should not use strings that have fewer than 6 characters. For example, if you specify '9903', thinking that will represent March, 1999, you will find that MySQL inserts a ``zero'' date into your table. This is because the year and month values are 99 and 03, but the day part is missing (zero), so the value is not a legal date.

TIMESTAMP columns store legal values using the full precision with which the value was specified, regardless of the display size. This has several implications:

• Always specify year, month, and day, even if your column types are TIMESTAMP(4) or TIMESTAMP(2). Otherwise, the value will not be a legal date and 0 will be stored.
• If you use ALTER TABLE to widen a narrow TIMESTAMP column, information will be displayed that previously was ``hidden''.
• Similarly, narrowing a TIMESTAMP column does not cause information to be lost, except in the sense that less information is shown when the values are displayed.
• Although TIMESTAMP values are stored to full precision, the only function that operates directly on the underlying stored value is UNIX_TIMESTAMP(). Other functions operate on the formatted retrieved value. This means you cannot use functions such as HOUR() or SECOND() unless the relevant part of the TIMESTAMP value is included in the formatted value. For example, the HH part of a TIMESTAMP column is not displayed unless the display size is at least 10, so trying to use HOUR() on shorter TIMESTAMP values produces a meaningless result.

You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:

• If you assign a DATE value to a DATETIME or TIMESTAMP object, the time part of the resulting value is set to '00:00:00', because the DATE value contains no time information.
• If you assign a DATETIME or TIMESTAMP value to a DATE object, the time part of the resulting value is deleted, because the DATE type stores no time information.
• Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not a valid TIMESTAMP value and will be converted to 0 if assigned to such an object.

Be aware of certain pitfalls when specifying date values:

• The relaxed format allowed for values specified as strings can be deceiving. For example, a value such as '10:11:12' might look like a time value because of the `:' delimiter, but if used in a date context will be interpreted as the year '2010-11-12'. The value '10:45:15' will be converted to '0000-00-00' because '45' is not a legal month.
• The MySQL server only performs basic checking on the validity of a date: days 00-31, months 00-12, years 1000-9999. Any date not within this range will revert to 0000-00-00. Please note that this still allows you to store invalid dates such as 2002-04-31. It allows web applications to store data from a form without further checking. To ensure a date is valid, perform a check in your application.
• Year values specified as two digits are ambiguous, because the century is unknown. MySQL interprets 2-digit year values using the following rules:
  • Year values in the range 00-69 are converted to 2000-2069.
  • Year values in the range 70-99 are converted to 1970-1999.

User Comments

To get a MySQL compatible DATETIME string with
php, use the following:

$datetime = date("Y-m-d H:i:s");

Usually I don't want to make the timestamp string
in php and then query it to the db as many people
seem to do, then its better to do a "insert into
db values(now(),'otherstuff');" for a perfect fitting

A way to display a Timestamp as a normal date-
time, assuming that datetime is the Timestamp
field:

select DATE_FORMAT(datetime, \'%Y-%m-%d %T\')
from some_table

A first reading of this page makes me seriously
worried about the implied lack of design.
Knowning just how dangerous bad date parsing can
be the ability to
<UL>
<LI>convert 2002122 to 02002122
<LI>tale a HH::MM::SS string and parse as date
>br><I>(ignore seperators values)</I>
</UL>
can only be deacribed as dangerous.

It would be far more advisable to have a stricter
date format parser and allow date and time formats
to be set system wide (default), per databasse and
then per session.

I am a critic of the brain dead SQLServer but even
this has broken but better (less dangerous) date
parsing rules that this.

2 pitfalls I've encountered with mysql timestamps:
1) timestamp in mysql differs from the unix-
timestamp in php.
2) this automatic-update-one-but-not-the-other
column sounds, and actually is, scary. and it's not
intuitive too - I was figthing with all types of weird
behaviour until I found this manual here ;-). my
suggestion - use datetime with now() instead.

if you need to use an UPDATE query but don't want
your timestamp columns to be automagically
updated as well just be sure to set them to
themselves in the SQL statement like so ...

UPDATE `table` SET `views` = `views`+1 ,
`timestamp` = `timestamp` WHERE `row_id`
= "242"

that way the the updates will take place but the
timestamps will not update themselves.
Hope it helps someone.
-=PJK=-

I would prefer if the value for this types of fields (date, datetime,timestamp,etc) is invalid, do not insert it at all, prompt the ERROR instead, like any other type. Because it at least will prompt user that there is an error occured. If not, user will assuming the entry is valid. I've a occasion (since I'm new in this MySQL) related to this where I'm assuming the entry is exactly valid. Friends of mine also who start to working with MySQL facing the same problem.

I realy wish timezone would be stored with the time values!

Be careful when adding indexes to TIMESTAMP columns. The UNIX_TIMESTAMP(timestamp) must be at least 0 to make queries work. This example is for Finland (UTC+2)

mysql> CREATE TABLE test (ts TIMESTAMP);

mysql> INSERT INTO test SET ts=NOW();
mysql> INSERT INTO test SET ts=NOW();

mysql> SELECT * FROM test WHERE ts>=19700101000000;
+----------------+
| ts |
+----------------+
| 20030317135755 |
| 20030317135755 |
+----------------+
2 rows in set (0.01 sec)

mysql> ALTER TABLE test ADD INDEX ts_index (ts);

mysql> SELECT * FROM test WHERE ts>=19700101000000;
Empty set (0.00 sec)

mysql> SELECT * FROM test WHERE ts>=19700101020000;
+----------------+
| ts |
+----------------+
| 20030317135755 |
| 20030317135755 |
+----------------+
2 rows in set (0.01 sec)

This is because:

mysql> SELECT UNIX_TIMESTAMP(19700101000000),FROM_UNIXTIME(0)+0;
+--------------------------------+--------------------+
| UNIX_TIMESTAMP(19700101000000) | FROM_UNIXTIME(0)+0 |
+--------------------------------+--------------------+
| -7200 | 19700101020000 |
+--------------------------------+--------------------+
1 row in set (0.00 sec)

However, with or without index this will work:

mysql> SELECT * FROM test WHERE ts>=FROM_UNIXTIME(0);
+----------------+
| ts |
+----------------+
| 20030317135755 |
| 20030317135755 |
+----------------+
2 rows in set (0.01 sec)

A "Datetime" Solution for Perl:

use POSIX qw(strftime);
$datetime = strftime "%Y-%m-%d %H:%M:%S", localtime;

Hmm I'm just after readin that this is nor the place to post questions. So I shall convert my query to the form of a suggestion...

I would suggest that, to convert local server time to your timezone, that you add the relevant number of hours to the query. For example, my server is located eight hours behind (although its nine during British Summer Time!). So add this difference somehow to your relevant table. It would probably be in the form of seconds.. so 8 hoursn would amount to 28,800 seconds.

Now .. if only I knew where I should put this adjustment... anyone got any ideas..? ;)

Hi.
I have a variable wich calls an atomic clock to get the precise date and time. I am able to "separate" the values and create 3 variables (vbscript asp), one for the year, one for the month and one for the day. I want to put it all together in a way that I can use it in a sql string and compare it with a Date field in my db.

First problem: Joining this 3 variables in a way that mysql string will recognize it as a date so I can make correct queries.

Second problem: If I can solve first problem, adding a integer number to the field "Date" with a value 2003-05-05 will turn it into 2003-05-10? Like (2003-05-05)+5=2003-05-10?

Third problem: If there's no problem with Second Problem, if I add 5 days (using date field+5) to day 2003-05-28, assuming that the month has 31 days, will I get 2003-06-02?

I know this is not the place to ask questions, but I wasn´t able to find an answer yet. I have a urgent situation. I'm making a online newspaper, and it's very hard to find documentation. I did find it at msdn, but it didn't cover pratical ways to use and get dates and that's of crucial importance to have a fully automated online newspaper (timings for articles and pub, expiration dates, etc). Sorry again, but i'm kind of desperate.
Thank you.

RE: to Joao Avelino

1) I don't know the exact VBScript syntax, but couldn't you say something to the effect of:

DIM date = year + "-" + month + "-" + day
DIM sql = "SELECT date FROM database WHERE date = \'" + date + "\';"

Then run the query with Connection.executeQuery(sql);

2) MySql supports date calculations (See section 3.3.4.5)

UPDATE db SET
date = DATE_ADD(date, INTERVAL 5 DAY)
WHERE your conditions;

3) MySql wraps the end of the month as well as the end of the year and Leap years correctly.

Hope this helps in time.

A quick tip: if you have two timestamps or datetimes, you can calculate the interval in seconds like this

select (unix_timestamp(updated) - unix_timetamp(created) as diff

Hello,
I'm trying to query fields that have been added within the last given amount of days. I have the query part all set, I just need to create a date that's the given number of days before the current one. For example, today is 2003-05-15, and I need to create or calculate the date 20 days ago as being 2003-04-25. Any help is appreciated.
-Adam

To Adam Laiacano:

You can try

select date_sub('2003-05-15', interval 20 day)

Hope this help!

jane

Add your own comment.