What is the best way to store a date/time in JSON?

Question

I recommend to use ISO 8601 dates. Especially this format

2014-03-12T13:37:27+00:00

is portable across many programming languages.

Edit:

JSON only knows these types:

string
number
object
array
true
false
null

Dates and datetimes are best stored as strings in a format that is widely used.

I suppose the best answer depends on the context in which that date/time will be used.

TL/DR;

the right format depends on...

• the context of how the data will be used (see below for examples)
• whether people or code are consuming the json and whether your programming language or library easily supports specific formats.

I suggest using json numbers (unix timestamps) and then combining those with either user timezones or a single separately stored "render timezone" (json string) depending on the use-case. This allows the most relevant date/time rendering to be presented to the user and allows you to easily use different date/time formats depending on the user's location/locale.

Alternatively if json-readability for humans is important to you, you can use the ISO string format, but still do the parsing and time-zone conversions in code for maximum flexibility in rendering user-friendly date/times.

Warning If you are writing these timestamps by hand, don't mess them up. The offsets for different timezones change at different dates in different regions throughout the year and if you put the wrong offset for a given region/date then your timestamp will not be representing the correct moment in time...and when you parse it and format it you will likely run into problems with the wrong time being displayed.

If you want the JSON date to be easily human-readable

ie if you want a human being to read the json directly and have the date/time have meaning.

In this case @Ribtoks answer is probably the best. Although if the date/time is stored in a different timezone than the user the user may be confused and/or need to do timezone conversions in their head in order to properly interpret the date/time.

Higher temptation for manual editing by humans and format mistakes that will lead to parsing errors. Also incorrect UTC offsets (wrong offset/time for a given region/date) will result in wrong date/times being displayed to users after formatting.

If the json is just data storage and the date/time will be rendered by code

User-Local Date/Time Formatting

i.e. you want to display a specific moment in time (date/time) to a user in their local timezone.

In this case, in addition to the date/time data representing the specific moment, you need a way to identify what is the "right" timezone for a given user (different topic).

Your code will need to parse/convert the JSON date/time data so that it can be combined with the user's timezone data (e.g. America/Denver or Europe/Berlin) for printing formatting in a user-friendly way. Take a look at moment-timezone library for this.

For instance the moment in time:
December 31, 2020 8:00 PM America/Denver
is the same as
January 1, 2021 4:00 AM Europe/Berlin

This type of behaviour is often desirable with things like social media or blog posts or message boards where a user wants to know when something was published in their own local time and doesn't care about the timezone of the author.

In this case since I have to parse/format the date/time I usually store date/time in unix timestamp (integer...json number).

Taking a number and doing math on it is computationally simpler than parsing a string timestamp including the built-in timezone in order to understand the underlying specific moment in time in order to then format a new date/time string for a user in a different timezone.

Event/Location-Local Date/Time Formatting

i.e. you want to display a specific moment in time (date/time) to a user in specific timezone.

Example: date/time of an (in-person) music concert in Berlin. If you have concert-goers buying tickets in London, it would be confusing to show them what time the concert starts in London time since they will be attending the event in Berlin.

This is the same case as the "User-Local" case above with the difference that instead of formatting/rendering the specific moment in time in different timezones for each user, you would render the date/time in a single specific "event location timezone".

So in addition to storing the specific date/time, you should also store the timezone you want to render that date/time in.

While you could just pre-format the date/time as a string, this gives you less options to programmatically (ie with code) change the date format. For instance you may want to use the event-local timezone but use different date/time formats for users in different countries.

e.g. for concert at 7PM on November 25, 2020 in Berlin. Same moment in time, same timezone, but different formats.

USA
11/25/20 7:00PM Europe/Berlin
Germany
25.11.20 19:00 Europe/Berlin
UK
25/11/20 19:00 Europe/Berlin

So in this case I would also store the date/time as a json number (unix timestamp) and then also store the event timezone as a string alongside that timestamp. This against keeps the parsing/timezone-conversion computation simpler. Then you still need to figure out a user's specific date/time format preference (browser locale, user profile, etc...out of scope of this question).

Anecdote about getting timezone/offsets wrong

I had a colleague who had the wrong timezone (resulting in wrong UTC offset for his location) set on his computer.

To fix this, he disabled network time and manually adjusted his computer clock to correct for the wrong timezone.

When he would send out meeting invites for a specific time, the invites include a time and the timezone of that time...which is important of course when you have a call across timezone boundaries so everyone sees the meeting in their local time and dials-in at the same time. In my colleagues case, of course these meetings were sent out for a different timezone.

He was quite grumpy that everyone was missing his meetings by an hour and showed us how he had scheduled it for 3PM but we all received an invite for 4PM.

Morale of the story...hacks generally eventually backfire and so it is very important to make sure you have stored the correct/intended moment in time. You can always change the timezone/format that specific moment in time is displayed in later but only if you have stored the correct moment in time to begin with.

Getting the underlying moment in time correct mostly comes into play when dealing with form input (e.g. date picker giving YYYY-MM-DD and time picker giving HH:MM). You need to make sure these date/time input strings from a user are interpreted with a time-zone context depending on the use case.

Of course you should save String in a JSON. But lets say you want to filter dates. You can still store a "number" as a String like "125"(Binary Codified Decimal) and it will be useful when you convert it back to 125. That said, If you want to customize your date number for filtering for example: Use long type and perform bitwise operations on it. https://docs.oracle.com/javase/tutorial/java/nutsandbolts/op3.html

• Reserve 12 bits for YYYY until year 4096-1.
• Reserve 4 bits for MM because (0-11)is enough.
• Reserve 5 bits for DD because day of the month is (1-31).

You can continue reserving bits for hours, minutes until 64bit. In this case DD/MM/YYYY is 21 bit. By using this aproach, you agree to @Clay Ferguson and you are being able to filter a KeySet in a Map in terms of "<" and ">" by year, month, day very easy and probably very fast.