Working With Dates in JavaScript, JSON, and Oracle Database

When I wrote a series of posts on creating JSON from relational data, I mentioned that dates could be tricky. That was a bit of an understatement. Dates and the time zone conversions that often go along with them can be outright daunting!

In this series, I hope to shed some light on the subject of dates in the context of JavaScript, JSON, and Oracle Database. As in the previous series, I'll base things on a RESTful API scenario. The focus here will be on tracking a date from the browser to the database and then back from the database to the browser.

This first post will provide an introduction to dates in Oracle Database and then briefly touch on dates in JavaScript and JSON. In the end, I'll go over parsing and stringifying dates between JavaScript and JSON (numbers 1 and 4 in the image above). In later posts, I'll revisit each of the solutions in the Relational to JSON series to show how each works with dates (numbers 2 and 3 in the image above). Here's a list of those solutions (will become links as new posts are published):

• Node.js driver for Oracle Database (node-oracledb)
• ORDS
• APEX_JSON
• PL/JSON

The date I will use for most examples in this series is 01-jan-2016 00:00:00.123456 America/New_York. The time zone offset of this date is -05:00. If converted to GMT, the date would be displayed as 01-jan-2016 05:00:00.123456 GMT.

In the section on Oracle Database, the term "client" refers to a piece of software that connects to the database. This includes drivers (node-oracledb, cx_Oracle, etc.) and some higher-level products which use drivers (ORDS, SQL Developer, etc.). In the sections on JavaScript and JSON, the term "client" refers to the environment where the JavaScript VM is running. This could be in a browser, Node.js, Electron, etc.

Dates in Oracle Database

Support for temporal values in Oracle Database is robust. There are several data types to choose from (including intervals, which I will not discuss) along with many easy-to-use functions for arithmetic, conversions, and formatting.

Database and Session Time Zones

Oracle Database has a database time zone that is set when the database is created. The database time zone can be changed using ALTER DATABASE, but changing the time zone of the database is rare as it's generally only relevant with the TIMESTAMP WITH LOCAL TIME ZONE data type.

Oracle recommends setting the database time zone to UTC (0:00) to avoid conversions and improve performance when working with distributed systems, replication, importing and exporting, and the like. One can view the database time zone with the following query:

select dbtimezone
from dual;

In addition to the database time zone, there is a session time zone that clients set when they connect to the database. The session time zone is used when fetching TIMESTAMP WITH LOCAL TIME ZONE data or converting TIMESTAMP values to TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE data types. Some clients may even use the session time zone to perform time zone conversions when storing or fetching values (examples can be seen with node-oracledb and ORDS).

The session time zone can be set via the environment variable ORA_SDTZ or via an ALTER SESSION command. See Setting the Session Time Zone for more details. One can view the session time zone with the following query:

select sessiontimezone
from dual;

An important question to consider when building applications on Oracle Database is: What value should the session time zone be set to? One could set the value to the end-user's time zone. That would require first obtaining the time zone, whether done automatically or via some user configurable setting in the app. Another option is to set the session time zone to a fixed value for all end-users, such as GMT or the server's local time zone.

The "correct" answer to the question above will depend on a number of variables, such as which features of the database you want to use, the type of app you're creating, etc. To make this decision correctly, or to change it later, it is important to understand how the session time zone is being set and used when working with different clients! We'll explore this in more detail in the subsequent posts which focus on specific clients and tools.

Datetime Data Types

Here are the datetime data types available in Oracle Database:

• DATE 
• TIMESTAMP
  
• TIMESTAMP WITH TIME ZONE
  
• TIMESTAMP WITH LOCAL TIME ZONE
  

Datetime data types are made up of fields which are used to determine their value. The DATE data type stores the year, month, day, hour, minute, and second components. Although still commonly used today, the DATE data type is missing some important pieces of information: fractional seconds and time zone components.

All the TIMESTAMP data types add fractional seconds to what can be stored. The TIMESTAMP WITH TIME ZONE  data type adds time zone related components.  TIMESTAMP WITH LOCAL TIME ZONE is unique in that it normalizes the time zone of the datetime value to the database time zone for storage (the original time zone is not stored) and then automatically converts the time zone to the session time zone upon retrieval.

When given a choice, I recommend folks use either TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE data types as these are the only data types where the time zone of the date is known. This allows time zones to be converted to other time zones without making assumptions.

When using DATE or TIMESTAMP data types, clients are more likely to behave unexpectedly. This situation results from incompatible data types across languages. For example, the Node.js driver for Oracle Database converts between JavaScript and Oracle data types. If you have a DATE  in Oracle (which doesn't have a time zone) that needs to be converted to a JavaScript date (which always uses the local time zone), how should the date be converted? In these situations, clients need a frame of reference and may use the session time zone for this, which may or may not be correct.

Parsing and "Stringifying" Datetime Data Types

This is one of those things that I took for granted in Oracle Database until I learned how difficult it was in other systems and languages. To parse datetime data types from strings we have the following functions:

• TO_DATE (returns DATE)
• TO_TIMESTAMP (returns TIMESTAMP)
• TO_TIMESTAMP_TZ (returns TIMESTAMP WITH TIME ZONE)

To go from a datetime data type to a string, we have:

• TO_CHAR (returns VARCHAR2)

All of these functions accept a format model (aka format mask) that is very flexible. Defaults for the format mask can be provided by setting the NLS session parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT.

Here's an overview of the PL/SQL block above (the functions work the same in the SQL engine):

declare

  l_d      date := to_date('01-jan-2016 00:00:00', 'dd-mon-yyyy hh24:mi:ss');
  l_ts     timestamp := to_timestamp('01-jan-2016 00:00:00.123456', 'dd-mon-yyyy hh24:mi:ss.ff');
  l_tswtz  timestamp with time zone := to_timestamp_tz('01-jan-2016 00:00:00.123456 America/New_York', 'dd-mon-yyyy hh24:mi:ss.ff tzr');
  l_tswltz timestamp with local time zone := to_timestamp_tz('01-jan-2016 00:00:00.123456 America/New_York', 'dd-mon-yyyy hh24:mi:ss.ff tzr');

begin

  dbms_output.put_line(to_char(l_d, 'dd-mon-yyyy hh24:mi:ss')); -- 01-jan-2016 00:00:00
  dbms_output.put_line(to_char(l_ts, 'dd-mon-yyyy hh24:mi:ss.ff')); -- 01-jan-2016 00:00:00.123456000
  dbms_output.put_line(to_char(l_tswtz, 'dd-mon-yyyy hh24:mi:ss.ff tzr')); -- 01-jan-2016 00:00:00.123456000 America/New_York
  dbms_output.put_line(to_char(l_tswltz, 'dd-mon-yyyy hh24:mi:ss.ff tzr')); -- 01-jan-2016 00:00:00.123456000 America/New_York

  execute immediate 'alter session set time_zone = ''US/Pacific''';

  dbms_output.put_line(to_char(l_tswtz, 'dd-mon-yyyy hh24:mi:ss.ff tzr')); -- 01-jan-2016 00:00:00.123456000 America/New_York
  dbms_output.put_line(to_char(l_tswltz, 'dd-mon-yyyy hh24:mi:ss.ff tzr')); -- 31-dec-2015 21:00:00.123456000 US/Pacific

end;

• Lines 3-6: One variable for each of the datetime data types is declared and initialized using the corresponding function to parse a string into that data type. Keep in mind that on line 6, which uses TIMESTAMP WITH LOCAL TIME ZONE, the original time zone information will have been lost (converted to the database time zone) and subsequent access will reflect the session time zone.
• Lines 10-13: The variables are converted to character values using TO_CHAR and those values are added to the output buffer (similar to console.log() in a browser). The output on line 13 indicates that the session time zone was set to 'America/New_York'.
• Line 15: The session time zone was changed to 'US/Pacific'.
• Lines 17-18: The values of the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE variables were logged after the session time zone was changed. Notice how the value of the TIMESTAMP WITH LOCAL TIME ZONE reflects the updated session time zone.

The most commonly used functions to get the current datetime in Oracle Database are probably:

• SYSDATE (returns DATE)
• SYSTIMESTAMP (returns TIMESTAMP WITH TIME ZONE)

SYSDATE and SYSTIMESTAMP do not take the database or session time zones into account. Instead, the values they return are based on the operating system's time zone settings that were in effect when the database was started.

If you'd like to get the current datetime value in the session time zone then you can use either:

• CURRENT_DATE (returns DATE)
• CURRENT_TIMESTAMP (returns TIMESTAMP WITH TIME ZONE)

declare

  l_sys_d     date;
  l_sys_tswtz timestamp with time zone;
  l_cur_d     date;
  l_cur_tswtz timestamp with time zone;

begin

  l_sys_d     := sysdate;
  l_sys_tswtz := systimestamp;
  l_cur_d     := current_date;
  l_cur_tswtz := current_timestamp;

  dbms_output.put_line(to_char(l_sys_d, 'dd-mon-yyyy hh24:mi:ss')); -- 18-aug-2016 23:31:11
  dbms_output.put_line(to_char(l_sys_tswtz, 'dd-mon-yyyy hh24:mi:ss.ff tzr')); -- 18-aug-2016 23:31:11.480367000 +00:00
  dbms_output.put_line(to_char(l_cur_d, 'dd-mon-yyyy hh24:mi:ss')); -- 18-aug-2016 19:31:11
  dbms_output.put_line(to_char(l_cur_tswtz, 'dd-mon-yyyy hh24:mi:ss.ff tzr')); -- 18-aug-2016 19:31:11.480390000 America/New_York

  execute immediate 'alter session set time_zone = ''US/Pacific''';

  l_sys_d     := sysdate;
  l_sys_tswtz := systimestamp;
  l_cur_d     := current_date;
  l_cur_tswtz := current_timestamp;

  dbms_output.put_line(to_char(l_sys_d, 'dd-mon-yyyy hh24:mi:ss')); -- 18-aug-2016 23:31:11
  dbms_output.put_line(to_char(l_sys_tswtz, 'dd-mon-yyyy hh24:mi:ss.ff tzr')); -- 18-aug-2016 23:31:11.480871000 +00:00
  dbms_output.put_line(to_char(l_cur_d, 'dd-mon-yyyy hh24:mi:ss')); -- 18-aug-2016 16:31:11
  dbms_output.put_line(to_char(l_cur_tswtz, 'dd-mon-yyyy hh24:mi:ss.ff tzr')); -- 18-aug-2016 16:31:11.480882000 US/Pacific

end;

Notice how changing the session time zone (line 20) only affected the subsequent calls to CURRENT_DATE and CURRENT_TIMESTAMP .

Converting Time Zones and Casting Data Types

Sometimes, you can't choose the datetime data type you want. Other times you'll be asked to convert values from one time zone to another. Luckily, converting data types and time zones in Oracle Database isn't all that difficult once you get the basics.

Let's create a table with all the different datetime data types and insert a datetime value into it using a client like SQL Developer:

create table date_test(
  d      date,
  ts     timestamp,
  tswtz  timestamp with time zone,
  tswltz timestamp with local time zone
);

insert into date_test (
  d,
  ts,
  tswtz,
  tswltz
) values (
  to_date('01-jan-2016 00:00:00', 'dd-mon-yyyy hh24:mi:ss'),
  to_timestamp('01-jan-2016 00:00:00.123456', 'dd-mon-yyyy hh24:mi:ss.ff'),
  to_timestamp_tz('01-jan-2016 00:00:00.123456 America/New_York', 'dd-mon-yyyy hh24:mi:ss.ff tzr'),
  to_timestamp_tz('01-jan-2016 00:00:00.123456 America/New_York', 'dd-mon-yyyy hh24:mi:ss.ff tzr')
);

commit;

We can fetch the values back out with:

select d,
  ts,
  tswtz,
  tswltz,
  sessiontimezone
from date_test;

Here are the results:

Now, imagine someone asks us to fetch the data out in GMT. My preference for these types of conversions is to use the AT TIME ZONE clause. The AT TIME ZONE clause throws an error with DATE and, in my opinion, shouldn't be used with TIMESTAMP directly because there's not enough info to convert from.

In the case of the DATE column, we first need to use CAST to convert the data type to a TIMESTAMP and then use FROM_TZ to convert the TIMESTAMP to a TIMESTAMP WITH TIME ZONE using the time zone we know to be correct. The TIMESTAMP column doesn't need to be cast to another type, but should to be converted to a TIMESTAMP WITH TIME ZONE. Only theTIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE  columns can use AT TIME ZONE straight away.

select from_tz(cast (d as timestamp), 'America/New_York') at time zone 'GMT' as d,
  from_tz(ts, 'America/New_York') at time zone 'GMT' as ts,
  tswtz at time zone 'GMT' as tswtz,
  tswltz at time zone 'GMT' as tswltz,
  sessiontimezone
from date_test;

Here are the results:

See? Converting time zones can be quite simple with Oracle Database!

Dates in JavaScript

Support for datetime data types in JavaScript is currently lacking in some areas. There is a native Date class, but the time zone of instances is always localized to the client's time zone (derived from the OS), which makes working with dates in other time zones difficult. The class does provide APIs for getting and setting fields in a date instance (even some that work with UTC), but they're fairly low level and tedious to use. Arithmetic and formatting are also quite difficult using the native methods.

I will not cover the native Date methods because most folks will probably opt to use a library or their framework's built-in support for datetime values. Here are a few examples:

• Moment.js: A fantastic library that can be used both in Node.js and browsers for parsing, formatting, and more.
• Moment Timezone: This library extends Moment.js so it can be used to format and convert dates in different time zones.
• Oracle JET: Oracle JET includes much of the functionality from Moment.js and version 2.1 added support for time zones.
• Angular 2.x: Angular 2 includes some date formatting capabilities.
• Angular 1: Angular 1.x included some date formatting capabilities.
• jQuery UI: jQuery UI has long included support for parsing and formatting dates.

Dates in JSON

The first rule about dates in JSON is that there are no dates in JSON! Perhaps this will change someday, but for the foreseeable future, we have to use String or Number types to represent dates in JSON. If using a number, most folks will use epoch time, which is the number of seconds that have passed since 12 AM on January 1, 1970 UTC. If using a string, most folks will use a format that conforms to ISO 8601.

I recommend using ISO 8601 because it's human readable and, more importantly, it's the default format used in JSON.stringify(). The key thing to understand is what happens when that method or JSON.parse()  is used to convert between JavaScript and JSON—- that's what we'll have a look at next.

Parsing and Stringifying Dates in JavaScript and JSON

In the early days of JSON, developers had to include a third-party JSON library to work with JSON in JavaScript. These days JavaScript clients include a native a JSON object with just two methods: parse and stringify.

Let's explore what happens as a date goes from JavaScript to JSON, typically before being transferred out over a RESTful API.

var todo = {};

todo.name = 'Get milk';
todo.due = new Date(2016, 00, 01, 00, 00, 00, 123);

console.log(todo.due); // Fri Jan 01 2016 00:00:00 GMT-0500 (EST)

console.log(JSON.stringify(todo)); // {"name":"Get milk","due":"2016-01-01T05:00:00.123Z"}

As you can see, JSON.stringify() converts the date to an ISO 8601 string that includes a four-digit year, two-digit month, and two-digit day, all separated by dashes. The time portion, which is preceded by a T, includes a two-digit hour (24-hour based), a two-digit minute, and a two-digit second, all separated by colons. Next, we see a .  followed by fractional seconds with three digits of precision. Finally, we see a Z which is an abbreviation for Zulu, meaning that the datetime value has been converted to UTC.

Now, imagine we POST or PUT the serialized (stringified) todo from above to some RESTful API and later issue a GET request to bring it back out. When the JSON arrives at the browser, the date will be a string again, hopefully in the same format as before. Let's try doing the reverse, going from JSON to a JavaScript object.

var todo = JSON.parse('{"name":"Get milk","due":"2016-01-01T05:00:00.123Z"}');

console.log(todo.due); // "2016-01-01T05:00:00.123Z"

The due property was not parsed into a native JavaScript date, it was left as a string (it's easy to spot as it's still wrapped in double quotes). To get the date string parsed into a native date object, we need to make use of the optional reviver parameter of the parse method:

var dateTimeRegExp = /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(\.\d{3})?Z$/;
var reviver = function(key, value) {
  if (typeof value === 'string' && dateTimeRegExp.test(value)) {
    return new Date(value);
  } else {
    return value;
  }
};
var todo = JSON.parse('{"name":"Get milk","due":"2016-01-01T05:00:00.123Z"}', reviver);

console.log(todo.due); // Fri Jan 01 2016 00:00:00 GMT-0500 (EST)

In the example above, a reviver function is declared and passed into JSON.parse() as the second parameter. The console.log() output shows that we now have a JavaScript date instance. Notice that the date's time zone has been converted to my local time zone, which was ultimately derived from my operating system.

Hopefully, you now have a better idea of how to work with dates in Oracle Database and how to parse and serialize dates to and from JSON. Stay tuned for the follow-up posts on specific clients and packages.

Here, again, are the links to the subsequent posts (will become links as the posts are published):

• Node.js driver for Oracle Database (node-oracledb)
• ORDS
• APEX_JSON
• PL/JSON