Which is most suitable datatype for storing time and date

Question

I have two choices of storing date and time in my database.

1. Generate the time & date from time function in php and then storing in database into int datatype which is of 4 bytes.

2. Generate the time & date during insertion in database into datetime datatype which is of 8 bytes.

My question is which type will make my SQL queries faster if I use date&time column for sorting.

Answer 1

You are better off using the native format of the database to store date times.

I can see almost no occasion where you would want to use another binary format for this purposes. That would make that component of the database essentially inoperable for other access methods.

As for human readability, you can solve that issue by having views access the tables (in other databases you can define a table with computed columns) that provide the dates in human readable format. Also, tools that know the database should produce the output in human readable format.

Unless you have a very large database or are in a very tightly constrained environment, then don't worry about storage. You are probably not thinking about the extra bits that are stored when you allow NULLs for a column, or the extra padding that might go between fields when they are no aligned on hardware word boundaries, or the empty space on data pages because the records don't align on page boundaries.

If space is such an important consideration, then you might want to develop your own date/time format to see if you can get it down to 2 or 3 bytes.

Answer 2

Use a TIMESTAMP datatype. It's stored as a number, but returned formatted. So it's faster for sorting, and more human-readable.

Answer 3

I always hate it building queries on a DB that contains human unreadable date and time values in int format.

Maybe the query will be a nano second faster if you use int but is it really worth it? I say no!