Storing date/time for sorting

Databases
1

I have to do frequent sorting of records based on the timestap.
I decided to use integer field for date/time instead of mysql’s default DATETIME data type.

Also, I want to protect readability of the date/time data, if somebody scans through it.

Am I doing it rightly? Or, I should drop this idea?

Please check for added_on field.
The DATETIME requires at least 19 characters.
But integer is even less than that. Being the numbers instead of mixed data, I believe integer field is good.


added_on > DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
added_on > INT(10) UNSIGNED NOT NULL DEFAULT 0

Further, the number and datetime are convertible.


SELECT CURRENT_TIMESTAMP(); # 2011-08-03 10:01:03 
SELECT UNIX_TIMESTAMP(); # 1312344976

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(CURRENT_TIMESTAMP())); # 2011-08-03 10:01:27

What can be the performance impact of using numeric field to store datetime data?

2

Why?

Also, I want to protect readability of the date/time data, if somebody scans through it.

Somebody? Who?
Protection of the database has nothing to do with choosing a date type or integer type column. If they can access your database, you’ve got other things to worry about.

Am I doing it rightly? Or, I should drop this idea?

No. Drop the idea :slight_smile:
The datetime data types exist for a reason. Take advantage of them.

3

not sure where you might have picked up this information, but it is completely wrong