Menu
I need to store dates (with time) in a MySQL database. I want to be able to format these how I like. Then what is the best way to store dates in a MySQL database? The DATETIME type, the TIMESTAMP type or simply a unix timestamp in a numeric data type? I will be retrieving the dates using PHP.
449
The DATETIME type is used for values that contain both date and time parts. 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 is used for values that contain both date and time parts.
Timestamps in MySQL are 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.
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'.
What is the difference between UNIX TIMESTAMPS and MySQL TIMESTAMPS? In MySQL, UNIX TIMESTAMPS are stored as 32-bit integers. On the other hand MySQL TIMESTAMPS are also stored in similar manner but represented in readable YYYY-MM-DD HH:MM:SS format.
Usually it does not matter whether you use TIMESTAMP or DATETIME datatype.
TIMESTAMP was 4 bytes and DATETIME was 8.DATETIME as a picture of a clock; think of TIMESTAMP as an instant in time, worldwide. That is, if you connect to the same database, but from a different timezone, a DATETIME will look the the same, but a TIMESTAMP will be adjusted for timezone.NOW(), SELECTing into PHP, etc, are compatible with both.TIMESTAMP is stored as a 32-bit integer (but you don't see that), it is limited to ~1970-2038.DATETIME is clock time, there will be a missing/extra hour twice a year if you switch to/from daylight savings time.Yes, you could use UNIX_TIMESTAMP() and have an INT UNSIGNED, but wouldn't it be better to see '2015-...'? (That would be 4 bytes.)
102
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
