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://mcmap.net/q/99579/-how-to-sync-iphone-core-data-with-web-server-and-then-push-to-other-devices-closed. 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!

Bonneau

Answer 1

When using SQL, you should basically always use timestamptz (TIMESTAMP WITH TIME ZONE) rather than timestamp (TIMESTAMP WITHOUT TIME ZONE ). In your backend, use the default type (datetime in Py, Date in JS). As long as you do that, you're safe. In APIs, ISO8601 or Unix timestamp are both viable options.

timestamp and timestamptz store the exact same information in your tables, a Unix timestamp. The difference is that timestamptz includes the time zone when converting to/from ISO strings, which your DB driver uses. timestamp doesn't convert to UTC time as you might expect, rather it keeps it in whatever time zone your DB locale is set to and omits the offset from the result, creating ambiguity. Your client doesn't know which time zone to use and might assume something incorrect, often UTC.

Try this in Postgres as an example:

-- Some systems default to UTC, some default to a local time zone. 
-- Let's see what happens in the latter case.
# SET timezone TO 'US/Pacific';

# SELECT now();  -- returns timestamptz
2023-09-29 09:26:40.277-07

# SELECT now()::timestamp;  -- Don't do this!
2023-09-29 09:26:43.927

# SET timezone TO 'UTC';

# SELECT now();
2023-09-29 16:27:10.32+00

And btw, UTC is a time zone too. Like any other time zone, ISO 1601 states that you have to add the offset ("+00") to specify it, otherwise it's ambiguous which time zone you mean. Also, "Z" is the same as "+00".

Sanies

Answer 2

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

Helenehelenka

Always use UTC for time zone and convert to the local time whenever you need a local time. – Latency

@AnthonyHunt ....Why? Unix time is a number. * its value is ALWAYS GMT * it's a lot shorter (unix timestamp requires a 32 bit integer while timestamps require 200 bits of ASCII) * it's platform/language agnostic and handled by all programming languages * 64 bit operating systems use a 64 bit t_time integer so it'll last the heat death of the universe So I'm really curious as to why you would choose to sacrifice mobility, size and maliability for string parsing? – Sather

A couple of reasons to choose ISO-8601 over UNIX timestamp: 1) correctness: unix time notoriously does not account for leap seconds, that occur ever so often. ISO-8601 timestamps can correctly represent these as "23:60:00" time when a leap second occurs. 2) reversibility: an ISO-8601 timestamp can contain the timezone/offset data, while unix timestamp does not have this. This is important if there is a use case that needs to know when the original event happened in their local time. With unix timestamps this is impossible, since it doesn't contain timezone or offset. – Engedi

@EsaLindqvist The point about leap seconds is interesting. However, MySql and Postgres don't support leap seconds. They use Unix time internally. Technically, this implies that they don't use UTC! – Sanies