Timestamps: iso8601 vs unix timestamp

Question

I know this is a pretty common question, but I don't feel like the answers I found really solve the problem. I will outline my specific use case and present summaries to the information from other SO answers and around the web.

For the service I'm writing database entries are created and stored both on mobile devices and our website and need to be synced both ways. We are currently targeting Android and iOS, which both use sqlite as the relational database. The server side is implemented in Python using Django with MySQL, but other solutions might replace that in the future.

Syncing will be implemented following the ideas outlined in this SO answer: https://stackoverflow.com/a/5052208/2076094. For syncing we will use a timestamp that is only set by the server and synced to the clients, the last_synced_date and timestamps for object creation and updating. Since the objects refer to something the user did at certain times they also have timestamp information.

My question only concerns the internal represenation of time used for these timestamps. The user representation in the UI is a localized and formatted version of the internal representation. There are quite a number of different ways both in the implementation languages and in the different databases used to represent timestamps. After quite a bit of research there seem to be only to valid solutions left:

• Unix-time
• ISO8601

This article which was on Hacker News (twice) suggests to use UNIX time and presents quite a good argument. The discussion on HN, as is to be expected, diverges quite a bit around the two points outlined above and then some.

My conlusion so far is, that Unix-time timestamps are easier to handle, but don't seem to be the common way to go in Django. Almost every code example I found, from the Django tutorial to lots of other sites, uses a DateTimeField in the models.py which gets mapped to some kind of date field in SQL, the exact terms depending on the database used.

The downside to using ISO8601 dates for transmitting and storage is that they need to be parsed to create the respective implementation language's Date type. That is not hard, but kind of annoying. For every single language we're using you either need a (small) library or at the very least more code than you would hope for. It's not very pretty, creates dependencies and is probably slightly slower. Unix-time timestamps don't have that problem in any lanuage I know.

The other thing is that you can easily get into trouble using "smart" Date or Timestamp fields in the database (and during parsing). There are lots of questions on SO regarding time magic that screws things up. My link limit is reached, so I can't post any, but you'll easily find some ;)

We could use a simplified format that does not include time zone information and just always use UTC. We would only use UTC anyways, but it seems if you use ISO8601, you might as well use a format that is universally understood and unambiguous. Unix-time is always in UTC, so you never have to worry about that.

Of course ISO8601 has the advantage of being human-readable when you look at the raw database and I won't have to rewrite a couple of lines of code shortly before 2038, but that does not seem to make up for the downsides.

It seems that by writing things out I actually have my answer already ;) Anyways, I would love to know what others think and what you do in your own projects. Please do give a short outline of your use case so others can classify your input better.

Thanks!

Answer 1

It's really hard to find any information about time datatypes or time standard formats, whatever it's known. I have myself been struggling for the past few days to choose the right time format. As you are, I am coding in both mobile devices and in web server too (not to mention that there will also be a desktop application).

The main question I was asking myself was "how can I have a seamless experience between the mobile device, the web server (API, or whatever you wanna call that) and the desktop application? How can you be consistent on when an event occurred and how to make sure that the representation of this instant is the same in the different levels of this software constellation?

As you might know coding with mobile devices implies you are using SQLite databases and web applications mostly implies MySQL. I got deeply disappointed to know that one of the biggest headaches I could ever imagine was to go from one database to another with time data. What to choose? DateTime? Timestamp? Oh gosh, SQLite doesn't have a built in time data type… Unix time? Ok, no problem, of course MySQL doesn't use UNIX time as a standard… Would be too simple…

What I learnt is… If you want to place the data on a timeline and say this is the point on the timeline that this event happened, best use timestamps (be it UNIX or MySQL style aka ISO8601).

Human readable is just a detail for me. No humans should read database tables, computers do, and you do tell the computer to handle the data in the order that humans can understand.

But then the question of "what is the time zone?" popped out… Well… it sucks… But hey, will dig the web for answers.

Myself I am surprised that MySQL doesn't use UNIX time, I think this is the most standard and consistent time format one can have.

I really think the documentation and standards around these choices are limited… I am now considering to write something about how to handle time with MySQL and SQLite. I wasted indeed half of my week on this matter, trying to understand how to make it clean and simple and the conclusion is, with the available documentation you just can't…

I am probably wrong…

Anyways, take a look at this video showing the struggle of dealing with timestamp data in MySQL:

http://www.youtube.com/watch?v=fp-etlirjbo