DATETIME vs TIMESTAMP vs DATE & TIME

I'm starting off this project and wanted to study some data retrieval optimization values. DATE & TIME are the two most deciding factors for processing the information in my app. The aggregation, classification, sorting & grouping of data is based on DATE & TIME.

• Daily reports
• Weekly reports
• Every day at 00:00 hours.
• Every year on this date

So there is a huge amount of chronlogical processing. We might require to process the data just date, just time, or both date & time. So was born the question. "What is the most optimum way of storing information DATE & TIME, DATETIME or TIMESTAMP?" The initial study helped me find this.

From the MySQL manual...

Storage Requirements for Date and Time Types

Data Type	Storage Required
DATE	3 bytes
TIME	3 bytes
DATETIME	8 bytes
TIMESTAMP	4 bytes
YEAR	1 byte

The storage requirements shown in the table arise from the way that MySQL represents temporal values:

• DATE: A three-byte integer packed as DD + MM×32 + YYYY×16×32

• TIME: A three-byte integer packed as DD×24×3600 + HH×3600 + MM×60 + SS

• DATETIME: Eight bytes:

  • A four-byte integer packed as YYYY×10000 + MM×100 + DD

  • A four-byte integer packed as HH×10000 + MM×100 + SS

• TIMESTAMP: A four-byte integer representing seconds UTC since the epoch ('1970-01-01 00:00:00' UTC)

• YEAR: A one-byte integer

So in terms of data storage, DATETIME is 8 bytes, TIMESTAMP 4 bytes, DATE & TIME 6 bytes (3 each). Ideally TIMESTAMP is good enough, if it fits my needs.

8 bytes > 6 bytes > 4 bytes

Memory is getting cheaper by the day, so let's ignore this for the time being, we'll revisit the storage factor a bit later.

Since I have to fetch information and process it, I decided to run some test in MySQL. Below is the schema of the database.

CREATE DATABASE datetest;

USE datetest;

DROP TABLE IF EXISTS dateandtime;

DROP TABLE IF EXISTS datetime;

DROP TABLE IF EXISTS timestamps;

CREATE TABLE dateandtime (
 timeonly TIME,
 dateonly DATE,
 counter    INTEGER,
 salary DECIMAL(10,2),
PRIMARY KEY (timeonly, dateonly));

CREATE TABLE datetime (
 dateandtime DATETIME,
 counter    INTEGER,
 salary DECIMAL(10,2),
PRIMARY KEY (dateandtime));

CREATE TABLE timestamps (
 timestamps TIMESTAMP,
 counter    INTEGER,
 salary DECIMAL(10,2),
PRIMARY KEY (timestamps));

I added approximately 100,000,000 records to each table, and then ran further test on it. As of now I'm yet to write the test cases, after I'm done I'll put the files on.

Found another interesting post, you might want to touch base on.
http://www.scribd.com/doc/2565263/The-top-20-design-tips-for-MySQL-Enterprise-data-architects

Bibliography

• http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html