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.