SQL - Datetime vs Timestamp and Varchar vs Text

Question

In SQL, I was wondering if there were any practical differences between Datetime and Timestamp and when to use one over the other. Same goes for Varchar and Text.

Thanks in advance.

Answer 1

Timestamps 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.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You 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 if you want to operate on it with PHP.

TEXT

- fixed max size of 65535 characters (you cannot limit the max size)
- takes 2 + c bytes of disk space, where c is the length of the stored string.
- cannot be part of an index

VARCHAR(M)

- variable max size of M characters
- M needs to be between 1 and 65535
- takes 1 + c bytes (for M ≤ 255) or 2 + c (for 256 ≤ M ≤ 65535) bytes of disk space where c is the length of the stored string
- can be part of an index

Answer 2

As mentioned in the MySQL documentation:
https://dev.mysql.com/doc/refman/5.7/en/datetime.html

The DATETIME type is used when you need values that contain both date and time information. 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'.

...

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

...

Use VARCHAR if you want to store data such as name, email, title, subtitle..

Use TEXT if you want to store long text less than or equal 65535 characters

Answer 3

interesting