MySQL Forums
Forum List  »  Newbie

Re: DATETIME vs. INT(11)?
Posted by: Rick James
Date: August 15, 2009 03:26PM

DATETIME takes 8 bytes of storage.
INT takes 4
DATE takes 3

DATETIME and TIMESTAMP have lots of useful functions.

TIMESTAMP is stored as GMT and TZ-converted on input/output.
DATETIME -- think of it as a snapshot of the clock on the wall (no timezone, daylight savings time, etc)

The diff of two TIMESTAMPs is a true elapsed time.
The diff of two DATETIMEs can be off by an hour due to DST, or more if data comes from multiple timezones.
The diff of two INTs depends on where the INT comes from (localtime, gmttime, etc).

5 = 4 + 1 -- The extra 1 is because it is NULLable, not NOT NULL. This is usually inappropriate for a time field.

"Using index" -- this is because you specified only the fields in a single index. This only rarely happens in real queries.

If you have a real query, let's continue this discussion with it in mind.

Back to your question:
DATETIME -- Obvious what the value represents.
INT -- smaller
TIMESTAMP -- has both advantages, so I lean toward it.

Options: ReplyQuote

Written By
August 14, 2009 10:00AM
Re: DATETIME vs. INT(11)?
August 15, 2009 03:26PM
August 17, 2009 10:31AM
August 17, 2009 08:04PM
August 26, 2009 09:23AM
August 26, 2009 11:30AM
August 26, 2009 11:55AM
August 26, 2009 11:34PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.