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.
1
-
int(11) > stackoverflow.com/a/4126015/2065594Cyril Jacquart– Cyril Jacquart2019-09-17 12:05:20 +00:00Commented Sep 17, 2019 at 12:05
Add a comment
|
3 Answers
Usually it does not matter whether you use TIMESTAMP or DATETIME datatype.
- In older versions,
TIMESTAMPwas 4 bytes andDATETIMEwas 8. - Think of
DATETIMEas a picture of a clock; think ofTIMESTAMPas an instant in time, worldwide. That is, if you connect to the same database, but from a different timezone, aDATETIMEwill look the the same, but aTIMESTAMPwill be adjusted for timezone. NOW(),SELECTinginto PHP, etc, are compatible with both.- Both are externally seen as a string, such as '2015-04-25 17:09:01'.
- Since
TIMESTAMPis stored as a 32-bit integer (but you don't see that), it is limited to ~1970-2038. - Since
DATETIMEis 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.)
4 Comments
cvbattum
Wouldn't unix timestamps be more useful if I wanted to format the time myself (instead of letting SQL do it, which is preformatted)?
Rick James
DATE_FORMAT() gives you lots of options. It works with TIMESTAMP, DATE, and DATETIME datatypes, without the need to use FROM_UNIXTIME().cvbattum
But is there a reason to nót use a unix time? Is it preferred to format inside SQL (with DATE_FORMAT()) rather than inside PHP (or any other language)? I'd say a unix timestamp is more generalized, as basically every language knows how to deal with those.
Rick James
Sure, use unix time -- but do you prefer to store unix time in a
TIMESTAMP field? Or an INT UNSIGNED field (as a number)? (I prefer TIMESTAMP.)You can use DateTime type to store timestamp & insert current timestamp using now() in mysql or get dates/timestamps via any methods you find suitable.
Comments
I prefer storing it as long which is always from epoch and represent UTC. This helps when we have to cater to different localization and timezone aspect -other wise we would end up storing values in either DB server default or JVM default locale.
