but this usually doesn’t gain you anything.

 

The following rules describe the possibilities for defining the first TIMESTAMP column in a table with the current timestamp for both the default and auto-update values, for one but not the other, or for neither:

• With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the column has the current timestamp for its default value and is automatically updated to the current timestamp.

  CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  );
  

• With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

  CREATE TABLE t1 (
    ts TIMESTAMP
  );
  

• With a DEFAULT clause but no ON UPDATE CURRENT_TIMESTAMP clause, the column has the given default value and is not automatically updated to the current timestamp.

  The default depends on whether the DEFAULT clause specifies CURRENT_TIMESTAMP or a constant value. With CURRENT_TIMESTAMP, the default is the current timestamp.

  CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  

  With a constant, the default is the given value. In this case, the column has no automatic properties at all.

  CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT 0
  );
  

• With an ON UPDATE CURRENT_TIMESTAMP clause and a constant DEFAULT clause, the column is automatically updated to the current timestamp and has the given constant default value.

  CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
  );
  

• With an ON UPDATE CURRENT_TIMESTAMP clause but no DEFAULT clause, the column is automatically updated to the current timestamp. The default is 0 unless the column is defined with the NULL attribute, in which case the default is NULL.

  CREATE TABLE t1 (
    ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP      -- default 0
  );
  CREATE TABLE t2 (
    ts TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
  );
  

It need not be the first TIMESTAMP column in a table that is automatically initialized or updated to the current timestamp. However, to specify automatic initialization or updating for a different TIMESTAMP column, you must suppress the automatic properties for the first one. Then, for the other TIMESTAMP column, the rules for the DEFAULT and ON UPDATE clauses are the same as for the firstTIMESTAMP column, except that if you omit both clauses, no automatic initialization or updating occurs.

To suppress automatic properties for the first TIMESTAMP column, do either of the following:

• Define the column with a DEFAULT clause that specifies a constant default value.

• Specify the NULL attribute. This also causes the column to permit NULL values, which means that you cannot assign the current timestamp by setting the column to NULL. Assigning NULL sets the column to NULL.

Consider these table definitions:

CREATE TABLE t1 (
  ts1 TIMESTAMP DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
  ts1 TIMESTAMP NULL,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
  ts1 TIMESTAMP NULL DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP);

The tables have these properties:

• In each table definition, the first TIMESTAMP column has no automatic initialization or updating.

• The tables differ in how the ts1 column handles NULL values. For t1, ts1 is NOT NULL and assigning it a value of NULL sets it to the current timestamp. For t2 and t3, ts1 permits NULL and assigning it a value of NULL sets it to NULL.

• t2 and t3 differ in the default value for ts1. For t2, ts1 is defined to permit NULL, so the default is also NULL in the absence of an explicit DEFAULT clause. For t3, ts1 permits NULL but has an explicit default of 0.

 

High Performance My SQL, Third Edition

Date and Time Types

My SQL has many types for various kinds of date and time values, such as YEAR and
DATE. The finest granularity of time My SQL can store is one second. (Maria DB has
microsecond-granularity temporal types.) However, it can do temporal computations
with microsecond granularity, and we’ll show you how to work around the storage
limitations.

 

Most of the temporal types have no alternatives, so there is no question of which one
is the best choice. The only question is what to do when you need to store both the
date and the time. My SQL offers two very similar data types for this purpose: DATETIME

and TIMESTAMP. For many applications, either will work, but in some cases, one

works better than the other. Let’s take a look:

DATETIME

This type can hold a large range of values, from the year 1001 to the year 9999,
with a precision of one second. It stores the date and time packed into an integer
in YYYYMMDDHHMMSS format, independent of time zone. This uses eight bytes
of storage space.

By default, My SQL displays DATETIME values in a sortable,unambiguous format,

such as 2008-01-16 22:37:08. This is the ANSI standard way to represent dates
and times.

TIMESTAMP

As its name implies, the TIMESTAMP type stores the number of seconds elapsed since
midnight, January 1, 1970, Greenwich Mean Time (GMT)—the same as a Unix
timestamp. TIMESTAMP uses only four bytes of storage, so it has a much smaller
range than DATETIME: from the year 1970 to partway through the year 2038. My SQL
provides the FROM_UNIXTIME() and  UNIX_TIMESTAMP() functions to convert a Unix
timestamp to a date, and vice versa.

My SQL 4.1 and newer versions format TIMESTAMP values just like  DATETIME values,

but My SQL 4.0 and older versions display them without any punctuation between

the parts. This is only a display formatting difference; the TIMESTAMP storage format is the same in all My SQL versions.

The value a TIMESTAMP displays also depends on the time zone. The My SQL server,
operating system, and client connections all have time zone settings.

 

Thus, a TIMESTAMP that stores the value 

0 actually displays it as 1969-12-31 19:00:00
in Eastern Standard Time (EST), which has a five-hour offset from GMT. It’s worth
emphasizing this difference: if you store or access data from multiple time zones,
the behavior of TIMESTAMP and  DATETIME will be very different. The former preserves
values relative to the time zone in use, while the latter preserves the textual repre-
sentation of the date and time.
TIMESTAMP also has special properties that
DATETIME doesn’t have. By default,
My SQL will set the first TIMESTAMP column to the current time when you insert a
row without specifying a value for the column.

 

My SQL also updates the first
TIMESTAMP column’s value by default when you update the row, unless you assign
a value explicitly in the UPDATE statement. You can configure the insertion and
update behaviors for any TIMESTAMP column. Finally, TIMESTAMP columns are
NOT NULL by default, which is different from every other data type.

 

Special behavior aside, in general if you can use TIMESTAMP you should, because it is
more space-efficient than DATETIME. Sometimes people store Unix timestamps as integer
values, but this usually doesn’t gain you anything. The integer format is often less
convenient to deal with, so we do not recommend doing this.
What if you need to store a date and time value with subsecond resolution? My SQL
currently does not have an appropriate data type for this, but you can use your own
storage format: you can use the BIGINT data type and store the value as a timestamp in
microseconds, or you can use a DOUBLE and store the fractional part of the second after
the decimal point. Both approaches will work well. Or you can use Maria DB instead
of My SQL.