DATETIME vs TIMESTAMP vs DATE & TIME
- 3 minsI’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 need 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 asDD + MM×32 + YYYY×16×32TIME: A three-byte integer packed asDD×24×3600 + HH×3600 + MM×60 + SSDATETIME: Eight bytes:- A four-byte integer packed as
YYYY×10000 + MM×100 + DD - A four-byte integer packed as
HH×10000 + MM×100 + SS
- A four-byte integer packed as
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.
