CODEWITHSUNDEEP
mysqldatabase-designtimestamp
cvbattum
cvbattum

Reputation: 799

Store date as unix timestamp or TIMESTAMP data type in MySQL?

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.

Upvotes: 8

Views: 6053

Answers (3)

cpandey05
cpandey05

Reputation: 1301

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.

Upvotes: 0

Rick James
Rick James

Reputation: 142208

Usually it does not matter whether you use TIMESTAMP or DATETIME datatype.

  • In older versions, TIMESTAMP was 4 bytes and DATETIME was 8.
  • Think of 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.
  • Both are externally seen as a string, such as '2015-04-25 17:09:01'.
  • Since TIMESTAMP is stored as a 32-bit integer (but you don't see that), it is limited to ~1970-2038.
  • Since 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.)

Upvotes: 2

Durlabh Sharma
Durlabh Sharma

Reputation: 48

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.

Upvotes: 0

Related Questions