Timestamps: iso8601 vs unix timestamp
iso8601, timestamp, unix-timestamp
Solution
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".
Problem
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!