Differences between timestamp and datetime in MySQL

When it is good to use datetime or timestamp in MySQL? 
10 August

Differences between DATETIME or a TIMESTAMP can be hard to understand for a beginner MySQL programmer. Let's try to figure out, what command and where should be used.

The  DATETIME, and TIMESTAMP types are related. MySQL recognizes DATETIME, and TIMESTAMP values in several formats.

The DATETIME type is used for values that contain both date and time parts. 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'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'UTC

DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD HH:MM:SS[.fraction]', the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.

The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time. 

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable.

Invalid DATEDATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

TIMESTAMP in MySQL generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a DATETIME field.

If need to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

What should we wait in MySQL 8.0?

Overview of upcoming "general" release of one of the most popular database
29 September

MySQL, one of the most popular databases in the world, has recently released the MySQL 8.0 RC.

What's new in MySQL 8.0? 

So, main improvements and new features are:

  • First-class support for Unicode 9.0, straight out of the box.
    MySQL 8.0 no longer uses latin1 as the default encoding, to discourage new users from choosing a troublesome legacy option. The recommended default character set for MySQL 8.0 is now utf8mb4, which is intended to be faster than the now-deprecated utf8mb3 character set and also to support more flexible collations and case sensitivity.
  • Window functions and recursive SQL syntax, for queries that previously weren’t possible or would have been difficult to write.
    MySQL 8.0 adds window functions via the standard OVER SQL keyword, in much the same way it is implemented in competing products like PostgreSQL.
  • Expanded support for native JSON data and document-store functionality.
    MySQL 8.0 expands JSON support with better performance, functions to allow extracting ranges from a JSON query (such as a “top N”-type request), and new aggregation functions that let MySQL-native structured data and semistructured JSON data be merged in a query.

It's worth noting that MySQL is jumping several versions in its numbering (from 5.5), due to the fact that 6.0 being nixed and 7.0 being reserved for the clustering version of MySQL.

MySQL 8.0 release date

As we know, MySQL’s release policy is “a new [general] release every 18-24 months.” Last major release (MySQL 5.7) was at October 21, 2015, so we should wait for 8.0 version around October 2017.

Other features in MySQL 8.0

Other interesting changes planned for MySQL 8.0 are:

  • More options for how to handle locked rows, via the SKIP LOCKED and NOWAIT keywords. SKIP LOCKED allows locked rows to be skipped during an operation; NOWAIT throws an error immediately on encountering a locked row.
  • MySQL can automatically scale to the total amount of memory available, to make the best possible use of virtual machine deployments.
  • Indexes can be manually excluded from the query optimizer via the “invisible index” feature. Indexes marked as invisible are kept up to date with changes to tables, but aren’t used to optimize queries. One suggested use for this is to nondestructively determine if a particular index needs to be kept or no