Menu
There is no date format in JSON, there's only strings a de-/serializer decides to map to date values. However, JavaScript built-in JSON object and ISO8601 contains all the information to be understand by human and computer and does not relies on the beginning of the computer era (1970-1-1).
So, 1900-01-01 00:00:00 is stored as 0.0 and September 20th, 2016 9:34:00 is stored as 42631.39861. 42631 is the number of days since 1900-01-01. . 39861 is the portion of time elapsed since midnight. Don't use a decimal type directly to do this, use an explicit date/time type; my point here is just an illustration.
You can get the value of the date field as String by calling the getText() method of JsonParser class and then you can simply convert it into a Date object by using the parse() method of SimpleDateFormat, as you normally parse Date in Java.
JSON does not directly support the date format and it stores it as String. However, as you have learned by now that mongo shell is a JavaScript interpreter and so in order to represent dates in JavaScript, JSON uses a specific string format ISODate to encode dates as string.
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.
the right format depends on...
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.
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.
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 asJanuary 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.
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.
USA11/25/20 7:00PM Europe/Berlin
Germany25.11.20 19:00 Europe/Berlin
UK25/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).
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With