MySQL has DATETIME and TIMESTAMP fields to store date & time in MySQL database. There are some points that should be keep into mind while choosing data type to store date and time. DATETIME or TIMESTAMP? Let’s compare.
An important difference is that DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time.
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.
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’.
On the other hand the TIMESTAMP data type has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC.
In MySQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. Also It has varying properties, depending on the MySQL version and the SQL mode the server is running in.
Below is an example to let you understand the difference between DATETIME and TIMESTAMP:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> show variables like '%time_zone%'; Variable_name | Value system_time_zone | India Standard Time time_zone | Asia/Calcutta mysql> create table datedemo( mydatetime datetime, mytimestamp timestamp ); mysql> insert into datedemo values ((now()),(now())); mysql> select * from datedemo; mydatetime | mytimestamp 2014-06-14 14:11:09 | 2014-06-14 14:11:09 mysql> set time_zone="america/new_york"; mysql> select * from datedemo; mydatetime | mytimestamp 2014-06-14 14:11:09 | 2014-06-14 04:41:09 |
We noticed that the DATETIME is unchanged while TIMESTAMP date type changed the values after changing the time zone.
Memory size is 4 bytes for TIMESTAMP vs 8 bytes for DATETIME.
Queries with native DATETIME will not be cached where queries with TIMESTAMP will be cached.
TIMESTAMPS are indexed faster and lighter on the database as compare to DATETIME.
The default behavior of TIMESTAMP is to update when row updated.
DATETIME or TIMESTAMP? What and when?
For values that represent system time like payment transactions, table modifications, you can use TIMESTAMP whereas if you need to store date/time information against a row, and not have that date/time change, use DateTime.
I personally prefer to use native MySQL DATETIME field because I 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.
Now you have a great understanding between both data types and hope this we help you to better decide DATETIME or TIMESTAMP depending upon your application requirements.