MySQL Timestamp vs Datetime What & When To Use
Howdy!
In 99.99% of the cases, developers deal with dates in their web applications.
In MySQL, there are two types of datetime, there is timestamp and there is datetime, so this will pose the question. What to use and when to use each one of them ?
Timestamp
According to MySQL documentation, Timestamp data type is used to store date and time parts in it. But the range is from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. Why that date you ask ? Well it's because the UNIX system store the datetime in a signed 32-bit integer. So when that date will come. It will cause an overflow. You can read more about it here
Datetime
Now Datetime is also used to store date and time parts together. But the key difference is the range. The range of Datetime data type is from 1000-01-01 00:00:00 to 9999-12-31 23:59:59 . Which is a long interval if you think about it.
When to use what ?
Now comes the question, when to use what ?
The way I see it, if you have to deal with some data withing the future, you must use Datetime data type. For example, if you have a subscription model in your application, one edge case can be a customer can buy 20 years of subscription. If you use the default timestamp data type, that will produce an error. So your solution is to use Datetime data type.
What about the use of Timestamp then you ask ? Well, it can be used to store events datetimes, as they occur momentarily.
All depends on your needs and your application needs after all.
Using Datetime & Timestamp In Laravel
Both of data types are supported in Laravel. You can follow the following example of migration to use each one.
$table->timestamp('timestamp');
$table->dateTime('datetime');