Hello Readers,

This blog tell the difference between the Datetime vs Timestamp in MySQL and PHP.

1> Datetime and Timestamp are used to store datetime and then retrieve the datetime back.

2> Basically, In this we have a 2 factor which help you to decide which one we use into the database design.

The first factor is Range for datetime

Range for DATETIME:-

The supported range for DATETIME type is 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

The other is range for Timestamp

Range for TIMESTAMP:-

The supported range for TIMESTAMP type is 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.

That means if you want to store date which is before the year 1970 or after the year 2038 you will need to use DATETIME.

3> If you want higher range, use DATETIME and if your application is timezone independent, you should use DATETIME.

The above scenario means, If your application want time with respect to GMT then use TIMESTAMP, Otherwise we use DATETIME.

Below is the small example where generally we use :

For example, if I am using forum or blog type documents, I will use TIMESTAMP as I want to capture the time when user have left comments, but if I am using an application where I have to deliver goods as per local time I will use DATETIME.

5> Lastly, Below is the example of mysql:

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone |  IST
| time_zone        |  SYSTEM 
+------------------+---------------------+

IST = India Standard Time SYSTEM = Asia/Calcutta

mysql>create table datedemo(
    -> usedatetime datetime,
    -> usetimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| usedatetime          | usetimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql>set time_zone="Africa/Accra";

mysql> select * from datedemo;
+---------------------+---------------------+
| usedatetime          | usetimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

The above examples shows that how TIMESTAMP date type changed the values after changing the time-zone to 'Africa/Accra' where DATETIME is unchanged.