How many bytes are used to store date and time?

I have several date formats to choose from in MySQL (DATETIME, DATE, TIMESTAMP, TIME, and YEAR) but I’d like to know how many bytes each of these types use on disk? I couldn’t find this in the manual.

I’m guessing here, but each character stored is one byte, so date should be 8 bytes, time should be 6 bytes, you would expect timestamp (which usually incorporates date and time) to be 14 bytes, year will be 4 bytes and datetime is whatever you decide to put in. It will take a date, a time, a timestamp, etc…


nice try, greg, i can see where you’re coming from

actually, i would’ve thought they’d be stored as strings too, since mysql allows you to define stuff like 1958-00-00 as a datetime

but what do i know, eh

[b]Storage Requirements for Date and Time Types[/b]
Column type  Storage required
DATE         3 bytes
DATETIME     8 bytes
TIMESTAMP    4 bytes
TIME         3 bytes
YEAR         1 byte


D’oh!! I still get points for effort though, no? :wink:

yes, of course


Thanks. But I’ve read also:
“The maximum size of a row in a MyISAM table is 65534 bytes.”

What does that mean?? Can’t I use LONGTEXT and LONGBLOB then?

sure you can

they don’t count in the row total, because they’re stored separately anyway

So this means I have maximum 65534 bytes for all columns except MEDIUM-, LONG-, TEXT & BLOB columns, which don’t count here? Do I get it right?

from the same page i quoted before:

"The maximum size of a row in a MyISAM table is 65534 bytes. Each BLOB and TEXT column accounts for only 5-9 bytes toward this size. "

this is the last time i read the manual for you :wink:

Don’t be so harsh on me, I’m mysql beginner :slight_smile: If you hadn’t read the manual for me I still wouldn’t understand it, but now it’s all clear to me!!! :smiley: