Storing date as integer

Question

1. I came across a database schema (instant messaging, http://www.9lessons.info/2013/05/message-conversation-database-design.html) where message's date + time is stored not as timestamp, but with an integer, like 123984347439.

   What's the point of this?

2. I found a couple of resources which store dates as integers, like 20151009. What are pros and cons of this approach in comparison native date + time specific formats of databases?

Answer 1

When stored as integer, the timestamp is not reliant on any time zone settings of the server. When you send a date to MySQL server, it will try to convert it to UTC for storage, if the column type is timestamp. It will perform the same conversion when it pulls the date out.

You can read about it in the manual, 5th paragraph.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.

With integer saved, this doesn't happen.

Pros:

• you are not reliant on the server's time zone

Cons:

• you can't use date functions easily, without performing conversions first using FROM_UNIXTIME()
• when reading the data manually, numbers don't tell you what date is in question. The timestamp column formats it so you can understand the date without problems

Update: I don't know what's the benefit of storing an integer that isn't unix timestamp. Storing the date such as 20151009 corresponds to 10.09.2015 - I don't see any kind of use for this without further information so my personal opinion boils down to that the person who designed such a system either didn't know much about dates and handling them or there's some kind of awkward business logic in the app itself that requires dates formatted like that in order to work. Bottom line is that I personally wouldn't use it. I'd stick to proven standards that work for everyone.

Answer 2

From the post that you linked it appears that the row is storing a Unix timestamp.

This allows you store the value as an integer while also allowing for method calls to retrieve as human readable date using from_unixtime() So if need be you can call SELECT from_unixtime(time) ASdateFROM conversation where user_id_fk = '3' and retrieve a value like 2007-11-30 10:30:19 from the result.

To answer your question about pros and cons, the biggest pro for storing as an integer is that comparing integers is a lot faster than datetimes. However, when stored as a Unix timestamp, there are some caveats(cons). Integers are factually faster when comparing unix_time = '1106475000' vs date_field = '2005-01-23 10:10:00; However, the problem arises when you need to compare an integer(Unix timestamp) to a human readable timestamp. Because you will need to convert the value to a Unix timestamp ahead of time in code or in the query, it will take some extra resources, or in case of in query it is much slower than native date comparison. So now this int comparison unix_time = UNIX_TIMESTAMP('2005-01-23 10:10:00') is a lot slower than date_field = '2005-01-23 10:10:00.

So it really depends on how your server side code is done to either leverage the speed of storing as an integer. As well it is up to the developer to decide if this speed is worth the extra abstraction between the sql server and the application.

Here is some more information on date/integer comparisons in innodb.

Here is some more information on date/integer comparisons in myisam.

Answer 3

1. That is most likely a timestamp expressed in seconds from a chosen epoch (starting date and time). There are a few standard timestamps out there, such as Unix timestamps

2. Storing dates as integers may speed up date calculations / comparisons in certain simple cases, since neither mysql, nor the processing application need to convert the underlying data to a date format. It also saves some space on your HD. The drawback is that you can't use the built-in date management functions. So, if you want to perform complex calculations, then either you write your own functions to do those or you need to convert the integer back to a date format.

Answer 4

The benefit is when you dont actually need the date or time - such as when you are storing peoples birthdates and are just using them as a matching value - not actually using them as dates and you dont want to store strings.