A question which would come sometimes to mind when starting with MySQL is whether I should use DATETIME or TIMESTAMP data type since both appear to store same date and time component.


Similarities between datetime and timestamp:
1. Values contain both date and time parts.
2. Format of retrieval and display is "YYYY-MM-DD HH:MM:SS".
3. Can include a trailing fractional seconds part in up to microseconds (6 digits) precision.
4. With the fractional part included, the format for these values is "YYYY-MM-DD HH:MM:SS[.fraction]".
5. Both the TIMESTAMP and (as of MySQL 5.6.5) DATETIME offer automatic initialization and updating to the current …

In this post, we’ll discuss how MySQL 5.7 handles the old temporal types during an upgrade.

MySQL changed the temporal types in MySQL 5.6.4, and it introduced a new feature: microseconds resolution in the TIME, TIMESTAMP and DATETIME types. Now these parameters can be set down to microsecond granularity. Obviously, this means format changes, but why is this important?

Are they converted automatically to the new format?

If we had tables in MySQL 5.5 that used TIME, TIMESTAMP or DATETIME are these fields are going to be converted to the new format when upgrading to 5.6? The answer is “NO.” Even if we …

Currently you cannot specify the current time as a default value for a DATETIME column. It works for TIMESTAMP but not for DATETIME,and even with TIMESTAMP this only work for the one timestamp column in the table, if you have more than 1 TIMESTAMP column you have to specify a default for all but the first.

This is fixed in MySQL 5.6 and MariaDB 10 which allows you to use a construct like this:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());

So what can you do if you are on MariaDB? Well, you can use a trigger to achieve a similar result:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());
delimiter //
mysql> CREATE TRIGGER td1_ins BEFORE INSERT ON td1 FOR EACH ROW
BEGIN
  IF new.c2 IS NULL THEN
    …

MySQL 5.6 introduces a new feature – microseconds resolution in some temporal types. As of 5.6.4 TIME, TIMESTAMP and DATETIME can have a fractional part. To create a field with subseconds you can specify precision in brackets: TIME(3), DATETIME(6) etc.

Obviously, the new feature requires the format change. All three types may now have a tail with a fractional part. The tail may be one, two or three bytes long and can store up to six digits after the point.

The non-fractional part has changed too. Thus, DATETIME uses only five bytes comparing to eight in previous versions.

As of revision 79 the …

Since the release of the 5.6 Community MySQL Server there is support for the milliseconds value in Time, TimeStamp and DateTime types.  You can find more information on this new feature at (http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html).  Starting with version 6.5, Connector/Net fully supports milliseconds. This support does not affect compatibility with older versions of MySQL and it gives you the ability to use fractional seconds when combining Connector/Net 6.5 with MySQL Server 5.6 or later.  Let's see how we should use it.

Creating a DateTime column that include Milliseconds value.

You can do this either using Connector/Net or using any MySQL client with a valid connection to your database.

For this case we're going to use MySqlCommand class within a console application in VS 2010.

…

Since the release of the 5.6 Community MySQL Server there is support for the milliseconds value in Time, TimeStamp and DateTime types.  You can find more information on this new feature at (http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html).  Starting with version 6.5, Connector/Net fully supports milliseconds. This support does not affect compatibility with older versions of MySQL and it gives you the ability to use fractional seconds when combining Connector/Net 6.5 with MySQL Server 5.6 or later.  Let's see how we should use it.

Creating a DateTime column that include Milliseconds value.

You can do this either using Connector/Net or using any MySQL client with a valid connection to your database.

For this case we're going to use MySqlCommand class within a console application in VS 2010.

…

Background Knowledge

I will explain how to convert a DATETIME (data type) value in Microsoft SQL Server to Unix timestamp and how to convert Unix timestamp to DATETIME. A Unix timestamp is a integer value of seconds since January 1, 1970 at midnight. For further explanation of Unix timestamps refer to Wikiepedia, UnixTimestamp.com or http://unixtimesta.mp/.

Note: This solution only work on dates prior to 2038-01-19 at 3:14:08 AM, where the delta in seconds exceeds the limit of the INT data type (integer is used as the result of DATEDIFF). See source for further details as I have not verified a solution to this problem.

Solutions

Convert Datetime Value to Unix Timestamp (today)

Are you doing some datetime manipulation or maybe you are migrating from some database technology to MySQL or possibly using milliseconds?
Here is an example on how to go about it:

Say you have the following date: MAR 16 2008 09:12:51:893AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893AM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 2008031691251.893000


What if its PM rather than AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 20080316211251.893000


Ok so this is just simple string manipulation where:
%M is the month name
%d is day number
%Y is the year
%h is the hour
%i is the minute
%s is the second
%f is the microsecond
%p is the period: ante or post meridiem
In the DATE_FORMAT part we se a %k which is …

I’ve been thinking a lot about invalid and zero temporal values and how to detect them with MySQL date and time functions because mk-table-checksum has to handle “everything” correctly and efficiently. The requirements are complex because we have to take into account what MySQL allows to be stored verses what it allows to be used in certain operations and functions, how it sorts a mix of real and invalid temporal values for MIN() and MAX(), how to detect a temporal value as equivalent to zero, and how different MySQL versions might affect any of the aforementioned.

At base, the four guiding requirements are:

1. Detect and discard invalid time, date, and datetime values
2. Detect zero-equivalent temporal values
3. Do #1 and #2 using only MySQL functions
4. Work in MySQL 4.0 …

Once upon a time`stamp`, in a `data`base far far away, someone filed a bug named: `Microseconds precision is not retained by TIME, DATETIME, and TIMESTAMP field types.` – Bug Number 8523. This was the beginning of 2005, yet now that we are approaching the end of 2009, after 4.5 years, many (including myself) are still asking for this.

In fairness sake, MySQL have indeed supplied a way to retain milli and micro seconds in a decimal field `DECIMAL(17,3)`, and it is also queryable as if it were a timestamp BUT why isn’t it possible to store in a `DATETIME` or `TIMESTAMP` field? Why can’t we run a ’select now()’ or ’select curtime()’ etc and get a full timestamp including milli / micro seconds?

I have counted 37 different usernames asking for this feature, spanning from 15th Feb 2005 to recently. (list found below)

Some have suggested UDFs, others suggested using log4j while others, pretty annoyed, allegedly went …