X

A blog about JSON in the Oracle database Technology

  • December 10, 2015

JSON and date/timestamp values

Beda Hammerschmidt
Consulting (Coding) Member of Technical Staff


We have been asked repeatedly by customers to share our ideas and recommendations on how to present dates in JSON. JSON's type system has no build in data types for dates. So what are our options?

 0) Ideally one would want JSON to support dates natively by the use of some new syntax in JSON - something like {"birthdate" : #date 2015/12/05 }

Currently this example is syntactically invalid JSON and it looks like JSON won't change - requests from our side got us a "JSON will never change..." response. Such change would also require every JSON parser to be changed and accept this new syntax. Not gonna happen. 

This leaves us to use existing data types to express date and timestamp values.

1) Numbers. MongoDB uses long integers as well as a markup schema to indicate that a value is a date.:

{"birthday": {"$.date": 1449273600000}}

The annotation scheme ($.date) is called eJSON (https://www.meteor.com/ejson) and seems to be a good idea since it also supports other data types that are not native to JSON. Traction by now is unclear though. A JSON parser would have to understand the annotation, i.e. that "$date" is followed by something that represents the date.

But using numbers to express a date (more precisely the seconds since 1970) does not seem like a very good idea:

  •  values are not human readable
  •  every date is a time stamp, there is no coarser granularity (and no finer, you cannot express microseconds)
  •  some dates require a negative number, if you're born before 1970 your birthday is a negative number


3) This brings us to strings. Obviously every date value can undergo some serialization process and can thus be converted to a string. Question is what format should be used. Oracle users are used to have a date (and number) format that reflects their locality (NLS settings). In Germany (all of Europe?) the day comes before the month, in the US it is the opposite. Thus 2/1/15 is the second Jan in Germany whereas it is the first Feb in the US. Confusing. Adding a second key/value pair for the format does not seem like a good idea either since the date value would not be self-contained anymore - it would be meaningless without the format value. The conclusion can only be to not support different date formats in JSON! There can only be one format in order to be understood globally by different systems.

This brings us to ISO 8601 (https://en.wikipedia.org/wiki/ISO_8601).

Using the ISO 8601 has many benefits

  •  no ambiguities by different formats
  •  string representation can be read and understood by humans
  •  different granularities are possible (day, day and time, timestamp, etc)
  •  Date and time values are organized from the most to the least significant - this allows lexicographic sorting. Your 'order by' on a JSON_VALUE VARCHAR2 column works out of the box.
  •  Oracle database users can perform a 'to_date' conversion to get an Oracle date and use NLS to print it with the right locality settings (if needed)


Thus, our recommendation is: use only ISO 8601 to represent dates in JSON.



comments welcome.



Join the discussion

Comments ( 2 )
  • Stew Ashton Sunday, October 7, 2018
    Hello Beda,

    It appears (at least in 18c) that JSON_TABLE and JSON_VALUE returning DATE will truncate the time portion of the input. Is this expected behavior? I don't see any mention of this in the documentation. Demonstration:

    alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
    with data as (
    select json_array(sysdate) j from dual
    )
    select * from data,
    json_table(
    j, '$[0]' columns(
    d date path '$',
    t timestamp path '$'
    )
    ) r;

    J D T
    ["2018-10-07T12:05:12"] 2018-10-07 00:00:00 2018-10-07 12:05:12,000000000
  • Beda Wednesday, February 27, 2019
    Hi Stew,
    that is correct. The JSON functions JSON_VALUE and JSON_TABLE return an ANSI date as per the SQL standard. Oracle's deviates from the standard in it's DATE type by also allowing a time portion. Since the JSON function returns a date as per ANSI standard the time portion is lost.
    If you want to reserve the time portion then you need to return the value as a TIMSTAMP instead of a DATE.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.