but this usually doesn’t gain you anything.

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.

巴特西

but this usually doesn’t gain you anything.

最新文章

热门文章